Crystal Reports/SQL 2000 - Reporting from a Stored Procedure with multiple selects (for a sub-report)
Scott,
To use a stored procedure as source of data, in data explorer, press
"options" on the window check "Stored procedure" and then collaps and
expand the data source again, it will show the stored procedures.
correct me anyone if I an wrong, but I don't think it's possible to
make
crystal's main report and a subreport use the result sets of the same
stored procedure call, unless the subreport makes its own call each
time it gets called, but then I am not sure it can pick up the second
result set. That's why crystal requires to make sure to turn off
update count, because any update count that comes before the result
set is treated as result set and obviously doesn't work.
About temp tables, you didn't specify what kind of database you are
using, but for oracle, sybase and sql server temp tables used by
different sessions do not share the table's contents. If you are using
sybase or sql server, use #temptablename temp tables. so will be
fine. In sybase you may need to create temp tables differently when
creating stored procedure than with oracle and sql server, but data
interference wont occur.
As with main report and subreport you really need to have 2 stored
procedures (or one with an argument specifying who's calling - by
which it would decide what content to return, say skip first result
set if it's called by the subreport) and crystal will use only 1st
result set.
Anatoly
Quote:
> I'm having a few problems, that I'm hoping someone can help out with (I
> apologize for the cross-posting, but I wasn't exactly sure which newgroup
> this should go into).
> I've got a VB 6 application to which I'm adding a new report, for which I'm
> going to use Crystal 8.
> The report itself is actually a report with a sub-report. To generate the
> data, I created a stored procedure (that takes a single optional parameter)
> that would tabulate the data as needed, and then would return two separate
> SELECT statements, the first for the main report, the second for the
> sub-report.
> So my problem is on several fronts:
> 1) How do I set up the VB code and/or the Crystal RPT file to use a Stored
> Procedure as the source, including the logic for either not passing a
> parameter (as a default), or for passing data that the user had selected
> (coming from another form in VB)?
> 2) How do I set up the RPT file to differentiate between the two SELECT
> statements coming from the Stored Proc?
> Note: Originally my plan was to have a stored procedure (called by VB) that
> would create a table of the data, and then have two views that the RPT would
> reference. This way I could create the data based on whether (or not) the
> user needed to have it send a parameter. Unfortunately, the reason I had to
> abandon this logic, is if two users tried to access the same report at the
> same time (especially if the two users passed different parameters to the
> stored procedure), there would be a conflict, since the table is a regular
> table. I couldn't use a temp (#) table, as I don't believe it'd be able to
> find the table going between the stored procedure that created/populated it,
> and the VIEW that Crystal would call.
> Any ideas?
> Thanks!
> -Scott