×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Alternative to "Hasvalue"

Alternative to "Hasvalue"

Alternative to "Hasvalue"

(OP)
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

RE: Alternative to "Hasvalue"

(OP)
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

RE: Alternative to "Hasvalue"

Try

CODE

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

HTH

Gary Parker
MIS Data Analyst
Manchester, England

RE: Alternative to "Hasvalue"

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
www.gainfocus.biz

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

RE: Alternative to "Hasvalue"

(OP)
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

RE: Alternative to "Hasvalue"

(OP)
Hello GJParker,

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

Here is my code

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

RE: Alternative to "Hasvalue"

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

RE: Alternative to "Hasvalue"

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

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports
www.gainfocus.biz

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

RE: Alternative to "Hasvalue"

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

RE: Alternative to "Hasvalue"

(OP)
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.

CODE

 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

CODE

 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

RE: Alternative to "Hasvalue"

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close