Passing parameters to Stored procedures in Formula Editor

[LiamE]

I am trying to create a crystal report (Using Crystal Reports for Visual Studio.NET) that uses a formula to decide how to lay out text for a Group on the report.

I have a stored procedure called sp_GetTestSetLevel that takes the ID of a TestSet record, and recursively works out what level it is in the data structure. This level can then be used to indent the name of the group by an appropriate amount.

I have set up my Stored procedure as a data field for the report in the Visual Linking expert.

In the formula editor, I can access the procedure and get it inserted into the formula. It gets inserted as {sp_GetTestSetLevel;1.Expr1000}

However, I have NO idea how to pass the parameter (the ID of the test set, which is also available to me in the formula editor) to the stored procedure. This ID will be different for every Group in the report.

What I want to do in the formula editor is something like this:

numberVar level := sp_GetTestSetLevel( {TestSet.testset_id} );

Where TestSet.testset_id is the id of the record, that I want to use as a parameter to the stored procedure.

(If I Browse Data for the stored procedure, it allows me to enter a value for the parameter, but it only lets me enter a constant number for this. I need to get the value from the current record for each group in the report).

Can anyone tell me how to do this (or even IF I can do it...?)

Thanks,

Liam

 

[Reebo99]

YOu can do this via a subreport.

Create a group on {TestSet.testset_id}.
Create a new subreport based on the SP, then place this in the group header.
Reight click the subreport, change subreport links, then select {TestSet.testset_id} from the top left box and in the bottom left drop down list select the SP parameter.

Hope this helps.

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[LiamE]

Hi Reebo,

Thanks for your reply.

I have tried this out but now I get asked for the parameter to the stored procedure when I run the report (it appears to ask me several times, perhaps once for each header?)

If I can get round this, does this mean that any formulas I write within this new "Group Header" subreport can use the Expr1000 value from the stored procedure, and get the right numerical result based on the record that the group header has been created for?

Thanks again,

Liam

 

[Reebo99]

1) Have you created the sub-report link as specified in my initial post?
2) Yes, you will have to pass back the value from the Sub-Report via Shared Variables. (there are countless threads detailing how to do this)

Reebo
UK

Please Note - Due to current economic forcast and budget constraints, the light at the end of the tunnel has been switched off. Thank you for your cooperation.

 

[LiamE]

1) Yes.
Maybe I need to somehow reset the report, as I have deleted other things from the report that USED to try and use this SP? More double-checking needed that they're all gone...
2) Thanks - I shall look them up.

This has got me started again anyway - Cheers!

Liam