Unanswered: Pass Stored Procedure Parameter to sub reports base on same sproc
I have a report that is based on a MS SQL stored procuedure. The report has to be grouped and displayed in three different ways. So, what I did was to create a main report that has the groupings. Then, I created a subreport of each of the groupings, These subreports are based on the same stored procedure, but are limited to just one of the three groups.
Basically my data looks like this
Report Group , Date, Rest of Data
1, Date 1, xxxxxx
2, Date 1, xxxxxx
3, Date 1, xxxxxx
Top level Group1
subreport linked to group 1
Top level Group2
subreport linked to group 2
Top level Group3
subreport linked to group 3
The report works great, except that when I change parameters for the main report, entering new parameters for the sproc, I have to enter the exact same parameters for the each of the subreports as well.
I know this report is inefficient, but it is a one time knock off for one big time customer (or so sales say.). My question is how to enter the parameters just one time (for the main report) and have the main report pass the parameters to the subreports runing the exact same stored procedure?
Oh I also should mention that, I've tried to place everything in just one report. My problem is that group 2 is different from groups 1 and 3. On group to I need to order and group the data based on price and supplypt point. On groups 1 and 3. I dont need to group by supplypt. It should look like this:
Group 1 is a fixed price product 1 based on a fixed supplypt lowest to highest.
SupplyPt1 product 1 but from lowest price at each supplypt.
price 1, etc.
Group 3 same as group 1 but the lowest price of product 2, 3, 4,&5 of all supplypts.
I can get them sorted out into the individual groups, but I can't get rid of sort without the supplypt for groups 1 and 3. that is why i went to the three subreports in the first place.
Answer Was A Little Cryptic - Need Further Explanation
Originally Posted by Srinvb
u can set a default value as null for parameter in the stored procedure
How does your solution impact how CR prompts for parameter values? Ideally we are trying to link the parameter values in the main report to the identical parameters in the subreport which feed the stored procedure.
Do I need to remove the SP from the subreport; assign default values for the parameters in the SP; and the re-import the SP into the subreport? Is that how the subreport will stop prompting for parameter values when the CR is run?
When a Report (Main or Sub) is based on a stored procedure Crystal automatically creates report parameters from the sproc parameters.
When setting up the Link to the SubReport, make sure you are not creating (default) new parameters but are passing the Main Report's parameter to the SubReport's Sproc Parameter. There should be no need to create extra parameters in the Main Report specifically for the subreport.
Unfortunately the Crystal interface is not very intuitive. In the screenshot provided the Top graphic is what happens when you select and move the parameter from the Main report to the "Field(s) To Link To" ListBox. Note the "SubReport Parameter Field to Use" dropdown displays a default parameter name. If you click okay at this point a new parameter in the SubReport will be created.
What you need to do is click the DropDown and scroll the list down until you see the appropriate existing parameter in the subreport - as per the Bottom screenshot in the graphic.