Combine two datasets in one Crystal Report

Question

I want to create a type of Summary report for an account. The report should have a chart of account data from one data source AND a grid of different account data from another data source.

I want to use all of the data from table one where account_ID = X AND I want to use all of the data from table two where account_ID = x.

Crystal Reports will let me add two different datasources, but It wants to link them to create a sub report in a Master / Details kind of way. I need to report on ALL of the data in table one, and all of the data in table two.

I'm a bit confused on how to do that.

Note: When I'm doing this for one report only, I just create a datatable from a view filtered by the account_ID and then set that as the datasource for the report. That way the heavy lifting is done before Crystal Reports gets the data. Again, not sure how to do that for two different datasources.

Does your current call rely on a Sql Select Command if so sounds like you need to alter and or create a view that's based on a JOIN statement unless I am misunderstanding what it is you are requesting — MethodMan
My first instinct would be to suggest a UNION, however OP's "a grid of different account data from another data source" suggests that the two datasets don't look the same. I'm thinking that the correct solution is two reports. So... @pStan -- do the two datasources contain the same fields? — Bob Kaufman
Thanks guys. No, the fields are different... apples and oranges... or in this case Daily account values (for the graph) and rates of return to be placed in a grid. Also, I will have MANY daily account value records, and very few rate of return records — pStan

EvilBob22 EvilBob22 · Accepted Answer · 2013-04-03T22:05:23

If the datasets are different enough that a JOIN doesn't make sense, you'll want to use a sub-report. First, create the report as if you were only using one of the data sets. Then, insert a sub-report where you want the second grid to show up (go to Insert --> Subreport); usually putting it in a new section. Build the sub-report using only the second dataset -- it may help to think of it as a full report in itself, as if the main report didn't exist.

Right-click the sub-report "field" in the main report and select Change Subreport Links. Add the account_ID field and you'll automatically get a parameter, Pm-Table.account-ID, and basic select expert in the sub-report itself.

Double-click it, and you'll get a new tab for just the sub-report. In the long run, you'll want most of the sections suppressed, so the output is just the grid.

Once all that is done, running the main report will display both sets of results.

Combine two datasets in one Crystal Report

1 Answers