Crystal Reports/SQL 2000 - Reporting from a Stored Procedure with multiple selects (for a sub-report) 
Author Message
 Crystal Reports/SQL 2000 - Reporting from a Stored Procedure with multiple selects (for a sub-report)

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



Mon, 06 Dec 2004 03:49:25 GMT  
 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



Mon, 06 Dec 2004 11:32:30 GMT  
 Crystal Reports/SQL 2000 - Reporting from a Stored Procedure with multiple selects (for a sub-report)
Your original plan can work very well.

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



Thu, 09 Dec 2004 23:39:02 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. Crystal Reports/SQL 2000 - Reporting from a Stored Procedure with multiple selects (for a sub-report)

2. VB.NET,SQL SERVER 2000 STORED PROCEDURE PARAMETERS, CRYSTAL REPORTS

3. Crystal Reports 8.5, SQL Server 2000, and Stored Procedures

4. Call SQL stored procedure in VB5.0 and print report to Crystal report

5. Linking Crystal Report with a stored Procedure -- crystal report for visual studio.net

6. sub report changes its source stored procedure to main report's stored procedure

7. report based on a stored procedure , crystal reports and vb6

8. Crystal Report based on SQL Server Stored Procedure with Parameters

9. Assign Stored Procedure of SQL Server with Crystal report

10. Pass parameter to a MS SQL stored procedure from Crystal Report 6.0

11. Calling Crystal Report from VB, repo is based on Stored Procedure in Sql Server

12. doing updating from grid to sql server with stored procedure sql 2000

 

 
Powered by phpBB® Forum Software