Help with Hasvalue Function in 2008

[Former Member]

Dear all,

I have a question regarding "Hasvalue" function under Crystal Reports 2008. I have created a quite extensive report based on quite alot of parameters. These parameters need to be Optional i.e. if user does not enter a value then all the records according to date parameter should be chosen, however if any one parameter value has been entered then report should filter according to that parameter value as well as date parameter values.

The report prints out results fine however it takes quite a lot of time to load. Example would be, yesterday when I ran the report Database query took 87238ms and reading records took 30691ms hence report formatting a first page took 981428ms.

Upon research and help from Brian in this forum I found out that while using "Hasvalue" the even when I use parameter value in one of the parameters, only date parameters are getting passed through the SQL query to the database, rest are not passed meaning that it reads all the records and then sort them in the end.

I have only one parameter which is not optional and that is date parameter, rest all are optional.

My question is, how can I pass the parameter values when chosen through the SQL query so that it can search the db upon query and not read all the records and sort at last.

Any ideas how to make it work?

Many thanks

Regards

Jehanzeb

[Former Member]

closing the question.

Regards

J

[Former Member]

are the parameters in the report or the stored procedure?

[Former Member]

On the report under Report>Record->Selection formula.

Infact here is the formula

{order_header.date_entered} >= {?Start Date} and
{order_header.date_entered} <= {?End Date} and
(not HasValue({?Account Number}) or {order_header.account_no} = {?Account Number})and
(not HasValue({?Product Group}) or {order_lines.stock_code}[1 to 5] = {?Product Group})and
(not HasValue({?Sales Area}) or {slslsp.slr_slsperson} in {?Sales Area});

All the Hasvalue are not being passed on through SQL, as you can see below I have chosen a customer account number but it does not reflect in the query, however the results are according to customer account.

 SELECT DISTINCT order_header.order_no, order_header.order_status, order_header.date_entered,
ndmas.ndm_name, order_header.account_no, order_progress.order_status, order_lines.stock_code,
slslsp.slr_slsperson, slslsp.slr_slspname, order_progress.date_created
 FROM   maxmast.ndmas ndmas, maxmast.order_header order_header, maxmast.order_lines order_lines,
maxmast.order_progress order_progress, maxmast.slcust slcust, maxmast.slslsp slslsp
 WHERE  (order_header.order_no=order_lines.order_no) AND
((order_header.order_no=order_progress.order_no) AND (order_header.repeat_no=order_progress.repeat_no))
AND (order_header.account_no=slcust.slm_custcode) AND (ndmas.ndm_ndcode=slcust.slm_custcode) AND
(slcust.slm_slsperson=slslsp.slr_slsperson) AND (order_header.date_entered>={ts '2008-10-01 00:00:00'}
AND order_header.date_entered<={ts '2008-10-05 00:00:00'})

Regards

Jehanzeb

[Former Member]

try this to see if it speeds it up

if {order_header.date_entered} >= {?Start Date} and

{order_header.date_entered} <= {?End Date} then true

else

if {?Account Number}='ALL' then true else {order_header.account_no} = {?Account Number})

or

if ({?Product Group}) ='ALL' then true else {order_lines.stock_code}[1 to 5] = {?Product Group})

or

if ({?Sales Area}) ='ALL' then true else {slslsp.slr_slsperson} in {?Sales Area});

you can also place the parameters in the sp which sometimes helps narrow the dataset

[Former Member]

Thanks for your kind reply Sharon however with your method under implementation nothing got passed through the query not even dates.

Here is the SQL when I chose date range from 1st june to 30th june and account number 0113158

 SELECT DISTINCT order_header.order_no, order_header.order_status, order_header.date_entered,
ndmas.ndm_name, order_header.account_no, order_progress.order_status, order_lines.stock_code,
slslsp.slr_slsperson, slslsp.slr_slspname, order_progress.date_created
 FROM   maxmast.ndmas ndmas, maxmast.order_header order_header, maxmast.order_lines order_lines,
maxmast.order_progress order_progress, maxmast.slcust slcust, maxmast.slslsp slslsp
 WHERE  (order_header.order_no=order_lines.order_no) AND ((order_header.order_no=order_progress.order_no)
AND (order_header.repeat_no=order_progress.repeat_no)) AND
(order_header.account_no=slcust.slm_custcode) AND (ndmas.ndm_ndcode=slcust.slm_custcode) AND
(slcust.slm_slsperson=slslsp.slr_slsperson)

I guess I have to use Hasvalue in any case because that allows user to either enter or not a parameter value which then crystal decides to pass or not to pass the query through SQL to the db. Additionally I think maybe Hasvalue function does not work as what it has been mentioned in the description of it, because if you look at the Help file (for hasvalue) it states that Crystal decides after entering the optional parameter value wether to pass the query or not through SQL.

Regards

Jehanzeb

[Former Member]

i left out a part sorry.

in the parameter you need to add ALL as a default value.

generally the first.

that may help you

i tried hasvalue in version 8.5 and it doesnt work for me, i cant even use it.

[Former Member]

Yes because "Hasvalue" is a new function in CR2008 and it cannot be used even in XIR2.

I am not sure about adding "All" in there because I didn't create the db tables.

Regards

Jehanzeb