How to assign NULL Value to Crystal Parameters

[Former Member]

We are using Crystal 2008 in VS2003. We need to assign NULL value to Crystal parameters: Boolean, Currency, Date, DateTime, Time, Number, String.

When the NULL value is assigned, the exception thrown

"The types of the parameter field and parameter field current values are not compatible."

ParameterFieldDefinitions crParameterFieldDefinitions;
ParameterFieldDefinition  crParameterFieldDefinition;
ParameterValues crParameterValues;
ParameterDiscreteValue crParameterDiscreteValue;

crParameterFieldDefinitions = crReportDocument.DataDefinition.ParameterFields;
crParameterFieldDefinitions.Reset(); 
crParameterFieldDefinition = crParameterFieldDefinitions[paramName]; // parameter Name

crParameterValues = crParameterFieldDefinition.CurrentValues;
crParameterValues.Clear();
crParameterDiscreteValue = new ParameterDiscreteValue();
    
if (paramValue == System.DBNull.Value)
        crParameterDiscreteValue.Value = null;
else
        crParameterDiscreteValue.Value = paramValue; // this is object wit the parameter value entered by the user

crParameterValues.Add(crParameterDiscreteValue);
crParameterFieldDefinition.ApplyCurrentValues(crParameterValues);

We have a propetarian OLEDB that talks to Crystal. When NULL value is assigned, all possible values of the parameters are used in the query -that is there is no filtering based on the parameter value. On previous Crystal .NET versions and in the COM components you could set the parameter value to NULL/Nothing, now in Crystal 2008 it seems that this is not possible, however we still need this functionality.

How do we set the value to NULL in Crystal 2008?

Thanks,

[Former Member · Accepted solution]

Hi,

I have checked that this feature is not available from the designer also. As a result getting it from the sdks might not be possible. Both Viewer and ReportDocument Object model returns the same result.

Lets see if someone else figures out a way for it.

Thanks.

[Former Member]

Opened an issue with Crystal, but there was no solution on setting the parameter to NULL when the report is using a command. Our limitations is that we need to use the command, can not change to a store procedure. As a work around we change the parameter values in the command itself (how to implement this is described in other posts)

[former_member183750]

Iris, thank you very much for updating this thread. It may not be the greatest solution, but at least there is some "work-around". I'll see if I can dig up more info on NULLs and parameters / stored procs etc. E.g.; why is this so difficult and post what ever I find.

Ludek

[Former Member]

Just to add on my previous post, we have thousands of existing reports. Modifying those reports to use a store procedures instead is not an option

Many Thanks

[former_member183750]

Suggest you obtain a single case support contract from here and discuss over the phone with a support technician. Single cases can be obtained here:

http://store.businessobjects.com/store/bobjamer/DisplayProductByTypePage&parentCategoryID=&categoryI...

Ludek

[Former Member]

Unfortunately setting an alternative value to NULL is not available when you are dealing with booleans.

I do see functionality on setting a parameter to NULL.For example: you have a report with a number parameter let's say an id. When you run the report and pass a value, it returns data filtered for specific user. When you run the report and pass NULL, it returns data for all the users. Now that the NULL value has been removed, the solution is to create an almost identical copy of the original report, except that it does not include the parameter?

It is not clear why NULL value was eliminated and more importantly how to work around this issue.

By the way, this is only in .NET, I tried in VB and I can set the parameter value to Empty.

Thanks,

[Former Member]

I've seen the post mentioned above also, but it does not resolve our situation

1) The code in the suggested solution throws the exception mentioned in the original post

CrystalDecisions.Shared.ParameterDiscreteValue crParameterDiscreteValue;
 
crParameterDiscreteValue = new CrystalDecisions.Shared.ParameterDiscreteValue();
 
crParameterDiscreteValue.Value = null;

2) We can not use an alternative NULL value like empty string because: we have hundreds of existing reports that have the parameters already defined. Also there is no alternative NULL value for boolean, and empty string is a valid parameter value for strings.

Thanks for the suggestion,

Still the question: Is there any way of setting the parameter values to NULL?

[former_member183750]

Well, I think the thread I quoted and the answer from AG answer that question(?). You have options, but perhaps not what you were hoping for...

Ludek

[Former Member]

Hi,

Just an update:

The only reason we have a parameter is to filter data and if we are not passing any parameter or passing null to it, then having a parameter in the report will not having any point (from report point of view..). Hence the CR does not allow this.

You can try passing some value like a -1 or 0 to indicate that there are no valid parameters and act accordingly. Another way to pass null would be to use a stored procedure.

Hope that helps.

Thanks.

AG.

[former_member183750]

See if the suggestions in this thread will be of more help:

https://forums.sdn.sap.com/click.jspa?searchID=23337607&messageID=6948935

Ludek