Crystal 11, SQL Server, Oracle - Subreport String to Main Report

[hmax]

Crystal 11
SQL Server 5
Oracle 10g

I need to share the result of a string field in a subreport on the main report.

Goal: create a cross-tab on main report and include results of subreport in cross-tab counts and totals.

Reason: subreport SQL Server results are not captured in Oracle database as this time.

Main report query is reporting against Oracle database tables.
Subreport query is reporting against SQL Server database tables.

Docket ## exist in both dbs and relate, hence I create the link based on the docket #. Retrieving associated results in subreport.

Now, I need to transfer the subreport results, where they exist (only for certain results in the main report, which is correct).

I need to take the results, which are "organizations" (9 types), place the results in the main report so that I can include in the cross-tab.

Then, within the cross-tab, I will need to include the results transferred to the main report and create sub-totals/totals.


Is this possible, and is this the best approach?

Thank you.
hmax

 

[hilfy]

You're probably not going to be able to use the Crystal Cross-tab component to do this - I can't think of any way to get a shared variable from a subreport into a cross-tab where the value will be different on a row-by-row basis (but I may be wrong here...) So, you're going to have to format the data yourself and do formulas, running totals, or summaries for all of the numbers.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[RTag]

Would it be easier if you get all the data from one place ?

You can link the Oracle server in SQLServer or use OPENROWSET:

http://www.sql-questions.com/micros...-for-connecting-to-oracle-via-openrowset.aspx

Create a command, join the data in one table and use crosstab on it

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.

 

[hmax]

Thanks, hilfy and RTag, for your feedback.

hilfy, yes, I thought about that, too. I was thinking if I could just get the result of the shared variable from the subreport into the main report under a group, then I could take the results in the main report and incorporate those into the cross-tab.

So, for that, how do I transfer the text result in the subreport into the main report with a shared variable?

Example:

Subreport

Text Field result A
Text Field result B
Text Field result C

Main report

Shared variable to show each instance of Text Field result as appropriate in main report group (below subreport placement).

====

RTag:

Yes, looking into that, too. Thanks for suggesting. Talked with dba, and we could also do it in SQL Server as

select * from openquery(rims4d,'select * from sample_table')

Definitely a good solution when we reach that point. The sql in each environment has some complexity, however, so this might take some work.

 

[RTag]

In order to use openquery you need to link the Oracle server to SQLServer first. Openrowset will allow you to retrieve the data without linking the servers , but you will need to expose the connection info.

http://www.R-Tag.com

Viewer, scheduler and report manager for Crystal reports and SSRS.
Send your report everywhere.