Alternative to "Hasvalue"

[jehanzebn]

Dear all,

Could you please let me know what command I can use other than "Hasvalue".

I have designed my report under Crystal Reports 2008 and I have used a command "Hasvalue" however our server which is Crystal Reports XIR2 does not recognize this command and I have to use an alternative under my report to make it work on our server.

Any ideas?

Regards

Jehanzeb

 

[jehanzebn]

How do you declare a record selection formula:

If parameter has no value then select all records else use the parameter = {field}.

I know one way is to use "hasvalue" which I used however my server doesn't recognise it. I need to use an alternative.

I have tried "isnull" but it is not the same and didn't work.
I have tried "" however that is not the same either and didn't work.

Any ideas what should be used here?

Regards

Jehanzeb

 

[GJParker]

Try

If IsNull({?MyParam}) or {?MyParam} = '' Then
    True
Else
    {MyTable.Field} = {?MyParam}

HTH

Gary Parker
MIS Data Analyst
Manchester, England

 

[dgillz]

please explain the function of hasvalue. Is this a null test or does it test a string to see if it contains a substring?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[jehanzebn]

Hasvalue checks the optional parameter and see if the parameter holds a value. If it does then the report is filtered according to the value it contains and if it doesn't then it selects all the records from that field.

Here is more appropraite definition:

Arguments
Parameter field

Returns
Boolean Value

Action
Evaluates the parameter value passed as the argument and returns TRUE if it has a value, FALSE if it has no value.

Typical uses
HasValue() is typically used to check that an optional parameter has a value before it is evaluated by the report engine. A runtime error is generated when a formula references an optional parameter that does not have a value.
It is applicable to both discrete and range values.

In my case I am using 3 optional parameters which needs to be filtered accordingly. If it has value filter records.

I can use this feature quite easily on CR08 but our server is XIR2SP3 hence does not take the Hasvalue command.

Regards

Jehanzeb

 

[jehanzebn]

Hello GJParker,

When I try your method the report says "Does not have a value" on Account number.

Here is my code

{order_header.date_entered}>={?Start Date} and
{order_header.date_entered}<={?End Date} and

If IsNull({?Account Number}) or {?Account Number} = '' Then
    True
Else
    ({order_header.account_no} = {?Account Number}) and

If IsNull({?Product Group}) or {?Product Group} = '' Then
    True
Else
    ({order_lines.stock_code}[1 to 5] = {?Product Group}) and

If IsNull({?Sales Area}) or {?Sales Area} = '' Then
    True
Else
    ({slslsp.slr_slsperson} = {?Sales Area});

Even if I take Sales area and Product group out the error message is the same.

Any ideas what else I can use.

Many thanks

Regards

Jehanzeb

 

[bdreed35]

The HasValue function was introduced in CR 2008 to allow for the testing of a parameter to see if it contained a value or not. This function didn't exist in XI R2 or below, and Crystal Reports XI R2 doesn't allow you leave a parameter null, so I am not sure why you would even need the equivalent to HasValue.

~Brian

 

[dgillz]

Isnull() will perform the same way on a database field.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[GJParker]

I suspect this is because your command will contain a where clause which is expecting an account number

so you could try

If IsNull({?Account Number}) or {?Account Number} = '' Then
{order_header.account_no} = {order_header.account_no}
Else
({order_header.account_no} = {?Account Number})

If not then please post the SQl for your command

HTH


Gary Parker
MIS Data Analyst
Manchester, England

 

[jehanzebn]

Morning GJParker,

Many thanks for your informative suggestion. I have tried it however I have the same error message appearing all the time.

Parameter cannot be null

As suggested by dgillz, does Isnull perform the same job on parameter fields? I know it works on Database fields.

Oh and here is the SQL pulled from the report when I ran it with IsNull.

 SELECT order_header.date_entered, order_header.order_no, order_header.order_status, order_progress.order_status, order_progress.date_created, ndmas.ndm_name, slslsp.slr_slspname, order_header.account_no, order_lines.stock_code, slslsp.slr_slsperson
 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-06-01 00:00:00'} AND order_header.date_entered<={ts '2008-06-30 00:00:00'})

And here is the code when I ran the same report using Hasvalue

 SELECT order_header.date_entered, order_header.order_no, order_header.order_status, order_progress.order_status, order_progress.date_created, ndmas.ndm_name, slslsp.slr_slspname, order_header.account_no, order_lines.stock_code, slslsp.slr_slsperson
 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-06-01 00:00:00'} AND order_header.date_entered<={ts '2008-06-30 00:00:00'})

Both queries are the same. The only difference is the Record Selection code.

One is using ISNULL and other is using HASVALUE.

Regards

Jehanzeb

 

[jehanzebn]

Right I found an alternative which is half working.

Why half?

well what I did is, I set up a default value for static parameters as "n/a" which means when a report is run, the parameter value is not null and set up to "n/a".

The problem is with Dynamic parameter, because they are setup as range values I cannot make the report ignore it, nor I can set default value to "n/a".

Any ideas what can I set it up to so that it ignores it too?

Regards

Jehanzeb