Parameter in SQL Command Prompts User Twice for Value 1

[MCCooper]

I am using Crystal Reports XI and MS SQL 2000.

I have a parameter in a SQL command for my report called "Report Date" which both filters records returned to the report as well as assists with calculations of a loan balance at that date using a sub-query. If I run the report with just this parameter then all goes well.

The report starts to do weird things when I create a second parameter within the report to allow the users to further filter out information. I create a dynamic parameter called Portfolios which I base on a second SQL command within the report that returns a distinct list of values.

I set the second parameter up using the Report Selection Formula so that I can utilise the "multiple values" option.

Now when I try to preview the report I am presented with multiple prompt screens. The first time the prompt screen pops up I am asked for the Report Date. When the prompt screen pops up a second time I am asked for the Report Date again and also for the Portfolios that I want in the report.

Matt

 

[pelajhia]

did you set this 2nd command into a subreport? sounds like you are passing the date to the sub and it's also calling for the portfolios.

 

[MCCooper]

No there's no subreport involved.

 

[lbass]

I recreated your issue. I think you have to either create both parameters in the main report and set them up in the record selection formula, or create each within its respective command. If you don't have too many possible values in the one where you want multiple options, you can set it up within the command as a string parameter, and then in the parameter prompt enter an array like this:

('ValueA','ValueB','ValueC')//using the parens and quotes

Do not use the single quotes around the string parameter in the command as you usually would, and instead use the following in the command:

table.`value` in {?parm}

Thanks to Turkbear for this idea in an earlier post.

If you have too many values, then you are forced to add the parameters to the record selection formula instead.

-LB

 

[MCCooper]

Thanks for the detailed response LB. I was hoping that this wouldn't be the only way around the issue though. If I had to have both parameters running against the SQL command can I create one of them as a dynamic parameter?

I did try another solution where I put both parameters in the main report and passed the date parameter to a subreport which contained 1 field for the loan balance calculation. It seemed to take for ever to preview the report though as the main report has about 1,500 records in it and the table that contains the values to calculate the loan balance has around 200,000 records.

If I was able to I would write stored procedures to return the specified data to the report but I am a contractor to the company I am developing the reports for and their DBA's are very protective/cautious about loading stored procs into the database.

Any other solutions would be much appreciated.

MC

 

[lbass]

I'm not sure I follow your question. If you are creating the parameters in the main report, you could create a command and use a field from the command to populate a dynamic parameter option list. If you are creating both parameters from within commands, you are forced to manually enter parameter options at the prompt.

It sounds like you would like advice about how to optimize the report, but I don't think we really have enough information about the report structure and what you want the report to do. If you want to spend some time providing a more detailed explanation, someone might have some helpful suggestions in that regard.

-LB

 

[BOBJProduct]

This problem is fixed in CR XI Release 2.

The limitation in CR XI is that when using DC prompts with stored procs or command objects, the engine requires parameter value values for all stored procs or commands before displaying the parameter page with the dynamic list.

So even if your stored proc/command that populates the DCP doesn't need a parameter - if you have a separate stored proc/command that populates the report the system will prompt for that parameter value before displaying the parameter page with the dynamic list.

CR XI R2 is available here as a free upgrade to CR XI.

http://www.businessobjects.com/products/reporting/crystalreports/compatibility_vs2005.asp

Be aware that if you're using Enterprise or CR Server XI, then the CR XIR2 designer cannot connect back to these servers.

Thanks, Blair
Crystal Reports Product Manager

 

[MCCooper]

For those that may be interested I did find another solution, although not the most favourable, to this issue.

I can set up my 1st parameter in the SQL command which asks the user for a date. The date is used for two things in the SQL command; in a formula to calculate a loan balance up to that date; and to return a filtered subset of data from the server.

I can then set up a 2nd parameter in the main report but this cannot be dynamic. If the 2nd parameter is dynamic then I encounter the issues described in the original post. I create a static list for the 2nd parameter, which luckily for me is only a short list, and all works fine. Both parameter prompts appear to the user only once!