Subreport Location

[kthacher]

I have a simple report comparing actuals to budget and calculating the variance between the two. The budget is in an Access database which I could not get to link to the main database (Lawson Software). I kept getting an error: Invalid File Link-Not all fields in same index expression. This occurred when I tried to create a second link between the tables.

So I created a subreport containing the budget data. But I could not create the variance formula in the main report because the subreport hadn't run yet so there was no budget to compare to.

So I passed the actuals down to the subreport and had it calculate the variance. So on the main report, the account number and actuals are from Lawson and the budget and variance are from the subreport.

The problem occurs when there is a record for an actual amount for an account but there was no budget for that account. When the main report sends the parameters to the subreport, it returns no records and blank spaces appear in the main report. Since no records were retrieved, no report was created and the variance was not calculated. Even though there were no records in the subreport, I still need the variance to calculate.

I'm guessing the solution lies in where the subreport is placed on the main report. It is currently in GF2a, the account group. The manual running totals are in GF2b and are suppressed so they don't show.

Or else I need to find a way to calculate the variance in the main report (see my second paragraph).

Any help is appreciated. Is there a way to link my Access database to Lawson? Or is there a way to calculate the variance in the main report?

I'm not too technical and only know enough to get myself in trouble <grin>.

Kathy

 

[lbass]

I would try placing the subreport in GH#2 instead of in the footer, and then use a shared variable to pass the value back to the main report in GF#2b. You would need to have a reset formula in the main report for the shared variable setting it to 0 so that it doesn't assume the previous value when null. In the formula for calculating the variance in the main report, I would allow for nulls (where the reset value of 0 will appear) by writing something like this:

whileprintingrecords;
shared numbervar budget;
if budget <> 0 then
{#yourrunningtotal} - budget

Then you can suppress the formula, if you wish, when budget = 0.

-LB

 

[lyanch]

The easiest way to get the info into the main report (easier to handle there) is to place the subreport into a section before the values are needed, then set that section to "overlay following sections" so it will *look* like only one section when displayed. Then the values will be availabe when you need to calculate the variance. One note, you should set your shared variable back to 0 before the next subreport runs. That way it will be 0 if the subreport doesn't return anything.


In designer would look sorta like:
Actual Budget Variance
---------------------------------------------------------
[subreport]
---------------------------------------------------------
[field] [@Variance]

In preview it would look like:

Actual Budget Variance
---------------------------------------------------------
[field] [subreport] [@Variance]
---------------------------------------------------------

Lisa

 

[kthacher]

Thank you LB and Lisa. This makes sense. I didn't know I could put the subreport in the group header. I thought it had to be next to the main report fields which are in the group footer.

Kathy

 

[scottostanek]

Just to add something outside the box, I did something very much like this where the budget data was in an Excel file (really just a special moron case of Access anyway) and the actuals are in a Progress database. Instead of trying to link the two, I built an excel query to pull the actuals into another tab of the original excel file, and used a little vba to setup a button that merges the two tabs into a 'data' tab with a column designating it as "budget" or "actual". This way my actual crystal report runs against a single datasource table and comes up a lot faster than fighting my way through any subreport nonsense.


If your original data is in Access, could you not build an access table that fetches your actuals through an odbc data source, and then have a sql view (? Access view that is) that merges the two? That way, they are definitely the same column format and there is no question of where to put the formulas...

Scotto the Unwise