ACK! Crystal 8: Subquery or Stored Procedure

[Interwizard]

I would appreciate anyone's help with this: I'm sure it's just me being stupid.

I have a subreport that needs to do a subquery.

Select * from psl.dbo.ipoxmitpobreakdown where bponum = (select distinct bponum from psl.dbo.ipoxmitPObreakdown where bponum=@ponum or ponum=@ponum)


i haven't figured a way to do the subquery with crystal (is there?) So, I decided to use a good ol' stored procedure (the sql statement above)

I was the stored procedure of the subreport to be passed the the po parameter of the parent report. How do I do that? Conincidentally, when I create report to use the stored procedure and try to save it ( i was going to pass the parameter through code) I get an application error. (Memory could not be "read", cpw32.exe, blah blah, blah.)

What should I do? If I could just pass the parameter to a subquery in the selection formula for the subreport, that'd be great. I looked at the SQL statement it generates, but I wasn't too sure if it would accept the paramters required.

Thanks!
Bill

 

[synapsevampire]

A stored procedure sounds appropriate, and if you've generated a stored procedure with a parameter inside of it, your report will automatically show the parameter (since you didn't seem to see a parameter, I'm guessing you omitted to provide a parameter in the SP), there's nothing left to do except pass the parameter.

There was a typo in you post that I couldn't quite figure out:

"I was the stored procedure of the subreport to be passed the the po parameter of the parent report. How do I do that? Conincidentally, "

You can pass variables between reports, I gather that's what you're after.

Check out the technical article on Seagates support site:

Article ID: c2007600

-k
-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[Interwizard]

Thanks for your reply! I do have a parameter in my stored procedure, and the stored procedure works great in Query Analyzer, from VB or ASP. The parameter does show up when I add the stored procedure to my crystal. When I edit the selection query, I set the Parameter from the SP to equal the PO Number parameter from the parent report in the selection formula. When I save the report. GPF/Application error occurs. Maybe that's an installed program problem with my computer. I just thought it was something I was doing wrong. I did get the thing to work, but I had to pass the SQL Query from the app at runtime w/ VB b/c when I chose "Show SQL Query" and tried to do the subquery in SQL language built into the crystal, there wasn't a way for that to accept parameters.

 

[synapsevampire]

Stored Procedures can be problematic, but they do work.

It would of helped to know which database and version, and Crystal version you're using, but if it's SQL Server or Oracle, it definitely works.

GPF's in Crystal are a poorly documented bug detection feature, I use them often!

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com