Using a Crystal Reports parameter in a Stored Procedure call

Question

Using a Crystal Reports parameter in a Stored Procedure call

John Connell

SSC Veteran

Points: 256
More actions
February 14, 2006 at 5:02 pm

#170597

I have built a Crystal report that is invoked via a web browser that passes two parameters
(?DATE and ?AorB).
In turn, CR must pass these two parms to my stored procedure to extract the data from the SQL server 2000 db. I cannot find any way to assign the values of ?DATE and ?AorB to @Date and @AorB which are the SP input parms. When I get this running, I expect that I will be prompted for the two parm values, but I'd really like them assigned programmatically.
Any ideas would really be appreciated. My boss wants this done pronto!
SirLansa

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Peter Kryszak SSCarpal Tunnel Points: 4368 More actions · Answer 1

What API are you using to generate the report?

I've done some extensive work with Crystal 9 in ASP.NET against Report Application Services (RAS) using the crystal RAS client document. In that scenario, you have to create an array of parameters in the right order and that is passed into the crystal method that generates the file. These parameters are just strings, not typed objects.

I suggest that you create some helper objects that can provide a better API to the Crystal objects. That's what we did and now generating reports and changing db connections takes only a few methods.

--peter

Steven Fastabend SSC Veteran Points: 275 More actions · Answer 2

When you call the query you need to set the parameters. It should look someting like this.

declare @dateparm datetime

declare @otherparm char(1)

select @datepart = {?DATE}, @otherparm = {?AorB}

exec your_stored_procedure @dateparm, @other

John Connell SSC Veteran Points: 256 More actions · Answer 3

Thanks for your replies. In the short term we have come up with another method, but it required creation of a temporary table, which now gives me additional housekeeping duties.

I'd still like to be able to use the SP and I expected to be able to do just what Steven is suggesting. HOWEVER, neither I nor my advisors here can tell me where I can put code like that.

We are using CR XI and the database query appears to be done as an integral part of the database connection, established when you create the report. If this can be overridden GREAT! Please help me understand where in Crystal to put this code!

Thanks,

John (sirlansa)