Setting params for subreport (based on Oracle stored procedure)

[crystalvictim]

Hi,

I'd like to know if there's a solution for the following problem I'm dealing with since quite some time without success:
I have an ordinary report that's retrieving data from a table.
I've written an oracle stored procedure / function with two parameters that inserts the values of its parameters into a certain table.
Now I'd like the stored procedure to be executed with parameters based on the main report's table fields (most likely by creating a subreport based on the stored procedure).
The problem is, that CR expects me to enter the subreports' parameters (i.e. the stored procedure's parameters) when running the main report. Yet, I do not want to specify them manually, as the subreport is meant to get them from the main report's table fields.

Has anyone succeeded in accomplishing this with CR8.5/10 or XI?

Thanks in advance for your comments.

Frank

 

[synapsevampire]

A parameter is just that, something which prompts for values.

In BOE/CRS/CE you can turn off the prompting, and you can ignore it within Crystal.

-k

 

[crystalvictim]

synapsevampire,

I want the subreport to be linked to the main report.
Thus I'd have to link a table field of the main report to the subreport's parameter (which originates from the stored procedure). Yet I have to specify it's value when running the main report. If I enter a value, it is correctly being passed down to the subreport. If I ignore it or switch it to null, the subreport no longer works.
There are two parameters in the subreport. On that originates from the stored procedure and the other one is being created when I link the main report to the subreport and is being used in the record selection clause.
What I need is the main report to be used to provide the value used to call the stored procedures.

 

[synapsevampire]

The whole notion is kludgy anyway.

Why not have the SP run the same process as the main report and update the table itself?

Then if need be, you could have a main report run and execute the SP without a parameter...

-k

 

[crystalvictim]

The reason for this kludgy construction is this:
A report (based on SAP or Oracle data) regularly runs and returns data, let's say on a daily basis.
Now we'd like to store some of this data (e.g. group summaries and key figures) in our oracle database each time the report runs.
The SP is meant to write a tuple of data into the oracle database and thus would be called with the name and value of a keyfigure (which is being retrieved from e.g. the report's group header).
So the SP has to have parameters and it has to be linked to the main report. Yet its parameters do not seem possible to be filled from the main report but only from a user-entry.

I suppose, we'll have to take a look at the possibility of creating an UFL that does the trick and can be included in a formula field (although we'd have preferred using a subreport as mentioned above as an easier way).

 

[synapsevampire]

Yeah, SP's always have limitations in CR, and my SAP->Crystal is rusty.

Still, I'd sidestep the Crystal aspect, schedule the SP to run in Oracle and do whatever the report is doing, it'll be faster and more readily maintained.

-k

 

[crystalvictim]

That's right; yet as the original data does not necessarily comes from an Oracle database, but might as well be SAP, we aren't able to rebuild the crystal report in oracle. In fact, an advantage of the SP-solution in the report would have been that this way we would have been able to transfer condensed data from the system the report's based on to oracle.

In the meantime, I ran a first test using a UFL and it seems to work.

Nevertheless, thank you very much for your suggestions!

 

[synapsevampire]

Ahhh, got it.

Annoying that you have to build yet another layer...

-k