[ggs]

If I am basing a crystal report on a stored procedure, is there any way to pass an output paramater calculated from a stored procedure into a crystal report, along with a recordset?


Ideal World Example: (Used to just show the theory, there is no relevance for this query)

Alter Procedure SampleDatasetWithParamater (@OutputToCrystal int OUTPUT)

Set @OutputToCrystal = (SELECT COUNT(DISTINCT FIRST_NAME) AS Firstnames
FROM CONSTITUENT);

Select * From RECORDS ;


What would I have to do to be able to have Crystal receive a recordset and also one (or a few) calculated values to use elsewhere in the report?

Say the report header would contain:

"There are {@OutputToCrystal} distinct first names in the system"

Then the rest of the report would be related to the RECORDS recordset that the SP brought in.

Is this possible or is there a creative way to do this? Thanks in advance.

Gerhard

[DrewAllen]

From what I understand, Crystal will only use the last recordset returned by the stored procedure. Can you return the output parameter as a static column in the recordset?

Drew

[ggs]

Yes, it looks like returning it as a static column will work, great idea. I also was able to use a second SP to get the variables and use both procedures in the report by assuring the two result sets were not linked in the Link manager.

Do you see either method being better than the other?

Option 1, static columns created in stored procedure with dataset and basing report on this single procedure.
Option 2, using 2 separate SPs with no links in CR, one with dataset, the other with variables as a single row dataset.

Both methods appear to work, but i havent really tested option 2 thuroughly yet.

The Data for the report in Option 2 would be based on:

Procedure 1: Select * From Records
--and--
Procedure 2: Select Col1 = "Title1", Col2 = "AnotherTitle"

Having no links between the two, I do get a warning message in Crystal about multiple starting points but i am not trying to group on data from each, only the one result set from the first procedure. The SP 2 will only be returning one row with my variable values as the data.

Thoughts? Any forseeable problems?

Thanks for the help.