Skip to Content
author's profile photo Former Member
Former Member

NULL Value in Crystal Reports Parameter

I have trouble getting a parameter work in Crystal Reports. I have defined a Parameter variable in the report. The parameter should restrict the data if a value is entered or it has to bring back all the data if the Value is NULL. In the SELECT EXPERT I have a condition that says IF ISNULL (Parameter_FIELD) then a Default Value.

When I run the report I get an error saying that the Parameter has NULL Value. Even If I default it with some Value the report is not working the way we want to. Is there a way I can do what I am trying to achieve. Please advice.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 22, 2010 at 03:06 PM

    You could pick a value to represent a null. 'Null' would work if it is a text parameter.

    Now create a formula {@null}

    If parameter ="null" then 1 else 0

    In your select expert use

    ({@null}=1 or field=parameter)

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member

      you could also try

      if isnull(parameter) or (parameter)="" then true else

      =

      the null might not be working because your database blank spaces arent defined as null but as blank ""

      go to file

      opition

      make sure you check convert null values check boxes.

  • author's profile photo Former Member
    Former Member
    Posted on Oct 22, 2010 at 05:31 PM

    I'm not sure how this is even an issue... The user shouldn't be able to leave a parameter empty. CR won't close the parameter screen until all parameters have a value.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Here is what I found. Any numeric cannot be blank in Crystal Reports. We have to create a Non Numeric Parameter.

      In the following example ARG_SRC_ID is a non numeric parameter. By default it will prompt the user with % which means that if the user enter % sign it will fetch all the data.

      Since we are defining the ARG variable as Numeric I have to change the non numeric to Numeric format using TONUMBER function. Hope this helps.

      IF {?ARG_SRC_ID} = "%" then

      {SRC_AUD.SRC_ID} = {SRC_AUD.SRC_ID} else {SRC_AUD.SRC_ID} = tonumber ({?ARG_SRC_ID})

      Crystal reports should come up with better way to handle this. This should not be very tricky.

  • author's profile photo Former Member
    Former Member
    Posted on Aug 23, 2016 at 11:29 AM

    Dear,

    Use this formula

    (not HasValue({?My Parameter}) OR {TABLE.FIELD} = {?My Parameter})


    paramerters.png (9.4 kB)
    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.