Linking to a Stored Procedure with Parameters
I recently saw two very similar questions about stored procedures posted to the book’s online forum. I thought they were excellent questions and that many people would benefit from all the details.
Both questions said that they had a main report and a subreport that were linked to a SQL database. The data sources were stored procedures and each had an input parameter associated with it. When they ran the report, they were prompted with two parameters instead of one. They wanted to only enter one parameter and have it passed to the stored procedure for both the main report and the subreport. However, they couldn’t figure out how to get rid of the second parameter prompt for the subreport.
The key to fixing this problem is to understand what the previous section said about how to pass data to a subreport parameter. In the previous section, the parameter was created by the report designer (presumably you) and we learned how to pass a data field from the main report to it. This told Crystal Reports not to prompt the user for the subreport’s parameter. The questions on the forum are very similar, but with a couple of differences.
The first difference being that in this case Crystal Reports created the subreport parameter automatically when you selected the stored procedure as the data source. The second difference is that instead of eliminating the parameter prompt altogether, you want to keep one parameter prompt and have its value shared between both reports.
You first have to understand that whenever you create a report whose data source is a stored procedure with an input parameter, Crystal Reports automatically creates a parameter with a similar name and the same data type. When you run the report, you are prompted to enter a value for the parameter and that value gets passed to the stored procedure. In the previous section, we saw how to link a data field from the main report to the subreport parameter and this keeps the user from getting prompted to enter the parameter value. The same applies here except that we want be prompted for the first parameter only, and have its value passed to the subreport.
The key to making Crystal Reports pass the same data to both the main report and subreport is to link the two parameters together on the Subreport Links dialog box. Instead of double-clicking a data field in the Available Fields list, you want to double-click the parameter field from the main report’s stored procedure. After that, at the bottom of the dialog box, select the parameter field for the subreport’s stored procedure. For example, Figure 9-7 shows a Subreport Links dialog box which links the two reports based on the @Customer ID parameter from each report.
Figure 9-7. Linking two stored procedure parameters together.
When you preview the report, you are prompted to enter a value for the main report’s parameter. Crystal Reports takes this value and passes it to the stored procedure for the main report. Crystal Reports also sees that the main report’s parameter is the linking field for the subreport and passes the same value to the subreport’s parameter field. Since this value gets passed to stored procedure for the subreport and there is no need to prompt you a second time.
