Very Computer

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

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

Simply define a key that would uniquely identify each individual user


remember that process ids are reused. Thus, you may want to delete any old

populating your table(s) with new table. Of course, this approach could
still be problematic if your user attempts to run this report in multiple
instances simultanenously with different parameters. However, I think
chances are, this is an unlikely scenario (and there are ways to get around
it as well). Your main report and subreport can simply reference these
generated table(s) as required.

HTH

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