How to pass null value to report parameter on ASP.NET?

[Former Member]

Background

Crystal Reports XI

ASP.NET 2.0

I plan to convert ASP application to ASP.NET.

In report parameter input screen, there are optional fields where user need not input value.

If user doesn't input value to optional field, ASP application doesn't set value to corresponding parameter field.

session("oRpt").MorePrintEngineErrorMessages = False
session("oRpt").EnableParameterPrompting = False
session("oRpt").DiscardSavedData
...
'If screen input value is empty, parameter isn't set.
If optionalInputValue <> "" Then
  Session("oRpt").ParameterFields.GetItemByName("optionalField").AddCurrentValue(optionalInputValue)
End If

In report, I use IsNull statement to check whether user input value to optional field or not.

If IsNull({?optionalField}) = True Then
		 ...
Else
    ...

ASP application works well.

But ASP.NET application doesn't work.

In ASP.NET application, I use CrystalReportViewer component, and the following error occurs.

Missing parameter values. CrystalReportViewer - CrystalReportViewer1 
Error: Object reference not set to an instance of an object. 

Of course, I set CrystalReportViewer.EnableParameterPrompt property to 'False'.

And I use ParameterFieldDefinition.ApplyCurrentValues to set parameter value.

Question

How can I change applications to work well on ASP.NET?

(i.e. How to pass null value to report without error?)

Is it impossible to pass null value to report on CrystalReportViewer?

[Former Member · Accepted solution]

Hello, Yuki;

Your code is correct.

I did some testing here and in my report Record Selection Formula I have:

If ({?Country} = "") then
   True
Else
   {Customer.Country} = {?Country}

That works for an empty prompt and for a value.

From my code I have the same as you and instead of Nothing as in the Stored procedure example I actually pass an empty string:

myParameterDiscreteValue.Value = ""

That did work.

There is another issue that caused my selection formula to be ignored. Make sure, in the code behind Form1.designer.vb, you do not have:

Me.myCrystalReportViewer.SelectionFormula = ""

If you do, delete that line or comment it out. That issue is documented [here|https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.km.cm.docs/oss_notes_boj/sdn_oss_boj_erq/sap(bD1lbiZjPTAwMQ==)/bc/bsp/spn/scn_bosap/notes.do].

Elaine

[Former Member]

Hello Elaine, David.

Thanks to all of you for your kindness and support!

I really appreciate it.

As a result, I will try to change report to handle optional parameter.

Thanks once again, for your very helpful answer.

[Former Member]

Hello, Yuki;

I tested a number in the report itself and it will not allow a null value in the prompt for a number. The prompt box will not close until a value is entered.

It does appear that regular parameters remain unchanged and will not accept a null value as David suggested.

There is an article that gives a workaround but it involves changing the report in the designer.

1213102 - Create report so that you are not required to enter a value for all parameters

Symptom

A report contains two parameter fields used for record selection. One parameter field pertains to a number field and the other parameter field pertains to a date field. Once a value is entered for either of the parameter fields then the other parameter field can be ignored.

How do you create a report so that you are not required to enter a value for all parameters?

Resolution

The parameter fields must both be created as string value types and then converted into a date field and a number field in the record selection formula. This will allow the two string parameter fields to evaluate an empty string, (""), in the record selection formula. The empty string will allow you to not enter a value for either or both of the parameters.

Upon completing the following steps you will be:

· Creating the parameter as a string type value

· Adding the parameters to the record selection

CREATING A PARAMETER AS A STRING TYPE VALUE

To create a parameter to be a string type value, complete the following steps:

1. From 'Insert', select 'Parameter Field'.

2. Click 'New', type {?Number}, and type text into the 'Prompting Text' box.

3. Select 'String' for 'Value Type'.

NOTE: =====

Repeat steps 1 through 3 to create a parameter for {?Date}. The string date parameter must be entered using the following format:

yyyy/mm/dd.

This format allows Crystal Reports to easily convert the date field from string to date in the record selection formula.

==========

ADDING THE PARAMETER TO THE RECORD SELECTION

To add the parameters to the record selection, complete the following steps:

1. From 'File', select 'Report Option', click 'Convert Null Field Value to Default'.

2. Create a record selection formula similar to the following:

(if {?Number} <> "" then {field.number} = tonumber({?Number}) or {?Number} = "")

or

(if {?Date} <> "" then {field.date} = date({?Date}) or {?Date} = "")

This record selection will allow you to enter a value for one or both parameters. You can assign values to both parameters.

When a value is given to one parameter, then it is not required to give a value to the other parameter. The record selection formula will return values only based on one or both of the parameters.

I hope that will help you.

Elaine

[Former Member]

Hello Elaine, David.

Thank you for your reply.

Elaine:

I tried to your code, and I could pass empty string "" to parameter successfully.

But I have a parameter which type is numeric or date, so I couldn't pass "".

If you have other ideas to pass null value to parameter which type is date or numeric, please tell me.

David:

Thank you for your useful answer!

But I'd like not to modify .rpt file, because there are many reports.

Do you have any ideas without changing .rpt file?

If it is specification of .NET application, I will adopt your solution.

Regards,

Yuki

[former_member208657]

Another option is to move to Crystal Reports 2008 where we added a new feature called optional parameters. These parameters can be optionally supplied when viewing, exporting, or printing a report. Check out the demo of Crystal Reports 2008 if you want to see it for yourself.

http://www.sap.com/solutions/sapbusinessobjects/sme/reporting/crystalreports/index.epx

[Former Member]

Hello, Yuki;

If the parameter can handle a NULL value, the following should work for you. This example happened to be a Stored Procedure:

To pass a NULL value to a stored procedure parameter, set the 'Value' property of the ParameterDiscreteValue class to NULL/Nothing. For example:

VB.NET sample code

Dim crParameterDiscreteValue As New CrystalDecisions.Shared.ParameterDiscreteValue()

crParameterDiscreteValue.Value = Nothing

C# sample code

CrystalDecisions.Shared.ParameterDiscreteValue crParameterDiscreteValue;

crParameterDiscreteValue = new CrystalDecisions.Shared.ParameterDiscreteValue();

crParameterDiscreteValue.Value = null;

Elaine

Edited by: Elaine Dove on Jan 29, 2009 8:32 AM

[Former Member]

Hello, Elaine.

Thank you very much for your answer!

I tried to use the following code, but COMException such as "parameter field type and value type

is not compatible" occurs when ApplyCurrentValues() is called.

And the parameter which I use in rpt is normal report parameter, not stored procedure parameter.

Of course, it can handle a NULL value, because we check value with IsNull statement in rpt file.

(If parameter value is null, all records are retrieved from DB.If parameter has a value, records are selected based on it.)

Dim crReportDocument As New CrystalDecisions.CrystalReports.Engine.ReportDocument
crReportDocument.Load(reportPath & reportFile)
        
'Logon to report
 ...
        
'Set null parameter
Dim currentParameterValues As CrystalDecisions.Shared.ParameterValues = New CrystalDecisions.Shared.ParameterValues
Dim myParameterDiscreteValue As CrystalDecisions.Shared.ParameterDiscreteValue = New CrystalDecisions.Shared.ParameterDiscreteValue()
myParameterDiscreteValue.Value = Nothing       
currentParameterValues.Add(myParameterDiscreteValue)
        
Dim myParameterFieldDefinitions As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinitions = crReportDocument.DataDefinition.ParameterFields
Dim myParameterFieldDefinition As CrystalDecisions.CrystalReports.Engine.ParameterFieldDefinition = myParameterFieldDefinitions("optionalField")
myParameterFieldDefinition.ApplyCurrentValues(currentParameterValues)
        
CrystalReportViewer1.ReportSource = crReportDocument
CrystalReportViewer1.EnableParameterPrompt = False

Is my code correct?

And do you have any ideas to set Nothing?

Best regards

[former_member208657]

As I recall you can only pass a NULL if the parameter is used in a stored procedure. If you have a normal parameter in a Crystal Report you must pass something if that param is used in the report. You may want to choose a value that will indicate the value is null and handle it in your app.