Double Prompting for Parameters from SQL Server SP

[lynchg]

I am using Crystal XI sp2 with a SQL Server 2000 database. I have a report that uses date parameters (start date and end date), from a stored procedure (the SP being the data source for the report). These are input parameters of the SP that are being used to set a time frame within which the report data is retrieved. These are set up as static parameters in Crystal with a Date datatype, this causes the prompt to invoke a calendar control for selecting the start and end dates, this works fine.

I also have dynamic cascaading report parameters that are based on fields in the data, and I am prompted with drop-down lists to choose those values, that also is working properly.

The problem that I am having is that I am getting double-prompted for the date parameters. When I preview the report in the report designer I first get a prompt window for the start and end dates, I hit OK. Then I get a second prompt window that has the start and end date prompts as well as the other cascading prompts. So I am forced to select the date parameters twice.

Another interesting twist is that the first set of date parameters each has the label for the first of the cascading prompts at the upper right corner of the prompt window, but when they appear again on the second prompt window with the other parameters, they all have the correct labels.

 

[DataDivaJill]

redo your links of the subreport. I have this problem all the time. after you link review the report filter for the subreport to fix the duplication of the link.

 

[lynchg]

This happens with reports that have no subreports.

 

[lynchg]

I figured out the problem. I had originally used a view as the datasource for the report and I created parameters on the report for the start and end dates. I then switched the datasource to a stored procedure that has it's own start and end date parameters. The call to the SP as the report opened triggered the first set of prompts, then the report invoked it's own parameters, so I was getting double-prompted.

The only problem I have now is that I can't delete the report parameters that I created. It seems to lock in on the fact that there are SP parameters out there and it doesn't offer Delete as an option when I right-click on the parameter objects in the Field Explorer window.

There are two ways to fix it, either re-create the report without the parameters or temporarily remove the parameters from the SP, run Verify Database in Crystal (this removes the parameters on the report), then reinsert the SP parameters, and the rerun Verify Database on the report in Crystal and the parameters will be restored with only one set of prompts.