Dynamic Parameters prompt twice on SQL Server Stored Procedures

[Former Member]

Post Author: Rich

CA Forum: Crystal Reports

New to Crystal Reports XI - we've just upgraded to enable the dynamic prompt ability, something I was looking forward to but have run into untold problems following all the instructions and information I can find.

We currently have many reports running on version 9 using stored procedures on SQL Server 2000. I have created a simplified stored proc for testing CR XI out, figuring start with simplest then move up. I have a stored procedure which takes a staff id as parameter - this is an optional parameter so they should be able to leave it blank (ie NULL).

I've created the report linked to the stored proc (no subreport, just simple table, grouped on person_id), my person_id parameter field shows up as I would expect (so far so good). I go to Edit and set up the parameter as dynamic, set the value to person_id, the description to full_name and parameter to @person_id as per all instructions. It is set to discrete values with multiples not allowed, prompt text is 'Select Name'.

PROBLEM: When I refresh and choose prompt for new, I get a free-text box prompting for @person_id (not 'Select Name'). I have to enter something or select NULL to progress, only then am I presented with my drop down box with populated values.

Why is the report coming up with this first prompt? I have tried opening up existing version 9 reports in the XI editor and have found the same problem, always prompting twice for parameters. Would love some help with this (the first of several issues I seem to be stuck on!).

As a note, I am not using Business Objects Enterprise or Repository, just Crystal Reports XI. I have also tried filling the parameter list from a simple table added to the report with no joy.

Many thanks in advance for all advice.

[Former Member]

I had the same issue. What I found in my case was this. Under database expert in Crystal Designer I specified the stored procedure that I wanted to use (which had the parameters in it), along with the table or view I wanted to use to populate the choices available for the parameter. The problem was that the SP and Table were linked in Crystal Reports. Thus everytime the program went to the lookup view or table it would generate the parameter prompts for the stored procedure. To remedy this I simply unlinked them in database expert. I then edited my parameter fields in the report to look at the lookup table or view. After that everything worked fine, only one prompt.

[Former Member]

Post Author: Rich

CA Forum: Crystal Reports

The only solutions to this problem I have found on KB articles and other forums is to:

Deselect Verify Database on First Refresh in Database connection properties

Deselect Verify Database on First Refresh in Report Options

Deselect Verify Stored Proceduire on First Refresh in Report Options

Upgrade to SP3

Already tried all of these.

The problem only occurs with dynamic parameters - if I set it to static and fill the list from the stored proc the prompt only occurs once. Static completely fails to meet our requirements which is why we upgraded to XI in the first place.

This should be the most simple thing, I fail to see why it should be so complex which is why I was thinking I must be missing something simple ... perhaps not.

Again, if anyone can shed some light on this, I would be highly appreciative.

[Former Member]

Hi, Rich.

In case you are still seeking input, I may have some helpful observations. I hope I am correctly understanding your issue.

As to your question about why Crystal XI prompts for the stored procedure parameter, I guess I don't understand why it wouldn't. The way I understand stored procedure parameters in Crystal Reports I would expect the behavior you describe.

A static parameter doesn't have to hit the database to populate the drop-down. The drop-down values are entered at the time you create the parameter in Crystal designer, either by selecting them from the values in the database (as you indicated you have done, if I understood correctly) or importing them in a flat file or entering them by hand.

Once those static parameter values entered they are part of the report. There is no need for the report to hit the database to populate the drop-down, so you are not prompted for the stored proc parameter.

When you are running the report in Crystal Designer you must enter the stored procedure parameter every time you refresh data and select "prompt for new paramters".

As you alluded to in your post, if your proc is your only data source, you need to hit the database to populate your drop-down. You indicated that you tried to populate the dynamic parameter values with a separate table added to the report, but I'm not sure that's the way to go. When I have tried it, and it didn't work.

Possible solution:

Have you tried creating the stored proc without a parameter and then creating a parameter in the report on person_id?

For testing, I did just that. The first level of the cascading parameter (person_id) had a default value of 0 (our database doesn't have a person with person_id = 0) and the second level (team_member) gives the user the opportunity to select from 1 to 50 people as team members of the person selected.

The report behaved as I thought you expected yours to behave: I was only prompted for the cascading parameters.

Please advise if I misunderstood something or if this appropriately addresses your issue.

I hope this helps.

George

[Former Member]

Rich, I'm beginning to think that the problem you are having has nothing to do with SQL Server or Stored Procedures, but rather some interesting XI Release 2 "features".

I've run into the same problem where I've modified a report in Release 2 (against an Oracle DB with no stored procedures), originally built in Release 1. When I send it to folks to look at using R1, they get this exact behavior. I've found a couple of other threads on the fora about this, but no useful answers. One involved a situation that sounded similar to mine. The solution is to upgrade everyone to Release 2. The other one involved a Crystal Viewer (XI, I imagine) embedded in VS. No upgrade path there, except, perhaps, to go to 2008 viewer. Of course, from what I've seen, you can't prompt for parameters in 2008 viewer, so I don't know if that would solve the problem.

What would be truly novel would be if the support folks supposedly monitoring the fora, now that the free support options are toast, would actually take this in hand.

[Former Member]

I think George has identified the problem correctly. In order for your report dynamic parameter values to be displayed, it needs to run the stored procedure, which has a prompt in it. Once you satisfy that, it can prompt you with the actual parameter in the main report.

Since you are using Enterprise, the solution here is to create a Business View structure and a list of value objects. You can then point your parameter to the list of values objects instead of the stored procedure. Also, build the Business View to pull the field from a look up table that the SP uses instead of the SP itself.