Subreport data to Main Report

[kthacher]

Using CR 8.5 I have created two subreports. One has Account Numbers and Actual Amounts from our Lawson software database. The other has Account Numbers and Budget Amounts from an Access database.

Now I want the main report to show Account Number, Actual, Budget,and Variance by obtaining all Account Numbers appearing in either subreport. Some Accounts have Actuals without Budgets. Some Accounts have Budgets without Actuals.

I was able to get the report to work using Actuals as the main report and Budgets as the subreport but then I did not get a row for any Accounts that did not have an Actual, even though they had a Budget.

I searched the posts and found a suggestion to use two subreports, but I don't know how to get what I want in the main report. Do I create a shared variable with the same name in each subreport to identify Account? (Not even sure I know how to do that.)

 

[synapsevampire]

Consider LINKing the Lawson tables into the Access database, and then create your query there and expose that as the data source for Crystal.

Crystal limits the join types when using disparate data sources, Access does not, plus Access will prove faster and allow for simplified maintenance and reusability.

-k

 

[kthacher]

Thank you for the suggestion but I don't actually know how to use Access; I just dumped the Budget there so Crystal could pull data from it.

I did have the problem of not being able to link the Lawson and Access data within Crystal, that's why I went to subreports.

These Crystal reports will be used frequently with changing parameters so I can't do queries in Access before running every report.

 

[lbass]

Do you have a table in one of the databases that has all of the account numbers? Then you could use that in your main report where you would group on the account field, link each subreport on the account number and place the subreports in a group section. You could then return subreport results per account number via shared variables that could be used in calculations.

-LB

 

[synapsevampire]

Access is really simple to use for these purposes.

Click the NEW icon within the tables tab, and select LINK.

Point at your ODBC datasource by changing the files of type to ODBC, then select the tables you want and remember to select SAVE PASSWORD.

Now you can use the MS Access query designer, which is easier and more extensive than Crystals to get the data you want.

-k

 

[bmarks]

I think lbass has the simplest solution to you. If neither Lawson nor Access has all of the account numbers, would it be possible for you to input the ones that are missing into one of them? (with nulls for the budget or actual, depending on which one you use).

You can then use that one for your main report and the other as the subreport.