I need to bind a report to an Oracle stored procedure, with a multi-select parameter being passed to the SP as a string. I have read threads at this forum explaining how to use a main/subreport to do this.
I built my main report with the multi-select parameter and a formula that “flattens” the parameter to the appropriate value. My report is embedded in the main, and linked such that the main report’s formula value (not the parameter) is linked to the parameter of the sub report being passed to the SP. (I hope I’m making sense so far.)
When I run it, I am prompted for my multi-selections, and the formula builds the correct string (I placed it on the main report for reference) but the sub report doesn’t return any data. In fact, it doesn’t seem to be receiving the value at all. (I placed the SP parameter on the sub report as a reference, and nothing appears.)
When I run the subreport alone, manually entering the same string that is being created by the formula on the main, I get the expected results. I just can’t seem to get things to connect up.
Maybe I’m missing something obvious. Anybody have any ideas?
Is your sub-report also executing the stored procedure and returning its own set of data? Then, you use the parameter passed down to filter the data out?
Only the subreport is bound to the SP. The SP has 2 parameters - one is the in/out cursor, and the other is a string containing the text to be used as part of the select criteria for the query whose data set will be returned by the cursor. (The SP itself simply builds some dynamic SQL using the passed criteria string and opens the cursor with it.)
When I run the subreport alone (in design mode and not as part of the main report) it works. I manually pass the criteria string to the SP, and get the expected results. When I run the thing as a subreport embedded in the main, no soap. I even placed the subreport parameter on the subreport itself, just to see if it is receiving it from the main, but it doesn’t seem to be making it across.
To link the sub, I used the result of the formula that parses the multi-select array on the main side, and the parameter going to the SP on the sub side.
I think I understand the concept behind the technique, but it isn’t working for me. Maybe I’m missing a critical piece of the puzzle?
Standard parameters cannot be used to link a subreport to the main. However, if you embed them in a formula, the formula can be used to link the two reports.
Here is an exercise I wrote against the sample database used in training to help students understand the concept:
Start a Blank Report, select the Customer and Orders tables from the Xtreme sample database, set margins, and save the report to a filename of Subreport Parameter A.
Group on {Orders.Order Date} and subgroup by month; suppress Details and Group Footer.
Start a new parameter, name it Beginning Date, set the Type to Date Time, leave the List of Values as Static, and use the Value Field dropdown to select the Order Date field. Accept all remaining defaults.
Start a second parameter, name it Ending Date, and duplicate all other settings from the previous step.
Start a new formula, name it Beginning Date, and only insert the {?Beginning Date} parameter.
Start a second formula, name it Ending Date, and only insert the {?Ending Date} parameter.
Since the inserted subreport will need the same set of parameters and formulas, save this report again to its file name, and then save it to a filename of Subreport Parameter B. Leaving B open, reopen Subreport Parameter A, and suppress all sections except Group Header #1.
In the Subreport Parameter B file, go to Report>>Selection Formulas>>Record and write the following formula:
Go to Insert>>Subreport, and use the Subreport Parameter A file.
Click on the Link tab. Move the formula {@Beginning Date} from the Available Fields list to the Field(s) to link to list. In the lower section, clear the checkbox labeled Select data in subreport based on field. Leave the screen open for the next step.
Repeat Step 10 using the {@Ending Date} formula, click OK, and insert into the Report Footer.
Go to the Subreport Parameter A tab. Go to Report>>Selection Formulas>>Record and write the following formula:
Well, I may be misunderstanding how this technique can be employed in my case.
Basically, I have an Oracle stored procedure that needs a string passed to it for use in the building of the SQL for the cursor, and that string needs to be derived from the user’s selection from a multi-select parameter prompt in Crystal. Since Crystal automagically creates parameters corresponding to the SP’s requirements immediately upon selecting the SP as the data source, it won’t work to modify the parameter hooked to the SP; the user’s entries need to be modified first since Crystal won’t pass its native array to Oracle.
Thus, any value being passed to the SP via that parameter will have to come from somewhere else. I was hoping that the “somewhere else” could be a main report in which the other report is embedded as a subreport. This thread ( Passing multiple values from Crystal to Oracle Stored Proc ) seems to suggest that the main could capture and convert the user’s selections and pass them to the SP’s parameter in the sub-report. If so, I’m not getting how to set it up . . .
Well, I’ve not seen this behavior in a subreport before, but apparently my initial approach WAS working - it just required that I drill down the subreport after the report ran. I’m accustomed to my subreports just appearing in the section where they lived without the need to double click them. So what gives?
I suspect it has something to do with the sequence of events when the report opens. Maybe the result of my formula isn’t available to the subreport when it’s ready to open.
Anybody know if that’s the case, and if so, how to work around it?
(BOB member since 2008-06-05)