Optional Parameters

[BDRichardson]

Hi,

I am generating a report with version XI report designer, which contains two sub-reports, and need to allow some of the parameters to be optional, but am unable to find a solution.

Some of the parameters are passed as a comma demilited value, so may not be set to any default other than a zero length string.

Each of the parameters for the sub-reports are linked to the parameters of the parent report.

The sub-reports each retrieve their data from a Stored Procedure.

The parent report has resultsets linked to Stored Procedures, which are used for populating dynamic parameter data sources. Therefore the parameters are configured as Dynamic types which use an existing Data Source.

Please can anyone offer any ideas as to how I may make the parameters optional in this scenario?

 

[synapsevampire]

the parameters are built into the Stored Procedures, so they are NOT optional.

If you mean take a default action based on the parameters not being set, then you would do so within the Stored Procedure, and this is common when designing Stored Procedures to do so.

So in code the SP checks for the value of the parameters returned or sets a default value for them, depending upon the dtaabase language, we don't know what database you're using so we can't really help with that, but I suggest that you speak with the database programmer anyway as this is standard fare.

-k

 

[BDRichardson]

I agree, parameters are not optional in Stored Procedures, and I have programmed the Stored Procedure to act on a default value, i.e. NULL or zero length string.

I am using SQL Server 2000.

However, my real issue is that if I dont select at least one value for the parameter, then it appears that the report does not attempt to execute the stored procedure.

 

[synapsevampire]

If you just hit OK it should still execute, are you certain that it isn't running?

You can set default values for the parameters by right clicking them and select edit and select set default values.

Then you can code for the default within the SP.

-k

 

[BDRichardson]

I'm afraid it doesn't execute, you simply get an icon next to the parameters which are not set, stating that the value is not valid.

When using Dynamic parameters in version XI, it will not allow you to specify a default.

The only best solution I have found, is to add a dummy record which has a zero length string value, and a description of 'ALL'. The user can then pick that option, in effect to determine that the parameter is set to the default value.

I have now realised that when picking multiple values, Crystal doesnt actually pass more than the first value to SQL.

To be honest, I find that Crystal always lets me down in at the last stage, and it really hasnt improved very much from the days when I first started using it in version 6.

Hopefully SQL Reporting Services will offer better achievements and satisfaction.