Difference subreport calculating out to zero (1 Viewer)

[April15Hater]

I have a report that contains 3 subreports:
-1 Current Period
(Control Name: [subReporta], Access name: [subReport 19a - Exception Items Trend Analysis CP]),
-1 Prior Period
(Control Name: [subReportb], Access name: [subReport 19b - Exception Items Trend Analysis PP]), and
-1 Variance (Current Period - Prior Period)
(Control Name: [subReportc], Access name: [subReport 19c - Exception Items Trend Analysis Difference])

I have Current and Prior working correctly, but when I go to subtract Prior from Current in the variance subreport, it shows zeros for the entire subreport. There should definetely be differences between current and prior.

=[Reports]![Report 19 - Exception Items Trend Analysis]![subReporta].[Report]![Text13].[Value]-[Reports]![Report 19 - Exception Items Trend Analysis]![subReportb].[Report]![Text13].[Value]

 

[pbaldy]

Oops, brain cramp. Just noticed the different subreport names. Are those textboxes in the footer or detail sections?

 

[April15Hater]

detail section

 

[April15Hater]

FWIW, this is my first time trying my hand with subreports, so I had a feeling I was going to have trouble, but the fact I didn't get a long line of #NAME errors kinda makes me hopeful.

 

[pbaldy]

If those controls are in the detail section, it will only work reliably if there's only one record in each. Otherwise, you're probably getting the first or last.

 

[April15Hater]

Should I not use a 3rd subreport?

How would one go about getting the variances?

 

[pbaldy]

I'm not clear on what the data looks like, but if each of the subreports is displaying various accounts, customers, etc, then there's no real way for the third one to associate the values. I might have 2 queries, one each for current and prior. Then in a third query, join those together (or left join both to a base table of all accounts or whatever). That gets the current and prior values for each account in the same record, enabling you to have a calculated column for the difference.

 

[April15Hater]

Sorry, I forgot I only 'splained the reports.

Data is all coming from one archive table which records the date the record was archived. So essentially I have 2 subreports pulling from one table.

Table is all transactions which roll up into account numbers on the report (which is why I wanted to just take one control and subtract from the other, but I now see the issue there...). Problem is that I'm also recording each transaction's aging window (i.e. 0-30 days, 31-60 days, etc) and columnizing a crosstab query based on that aging window (again, subtracting one control from the other would have been great here).

The table is far from normalized, but there is a P. Key. It is an import that we get daily, so the maintenance in something normalized would be outrageous.

I'm sorta at a loss.

Here's my sql, maybe that will help explain:
SubReportA

TRANSFORM Sum(IIf([DRCR]='CR',[Amount]*-1,[Amount])) AS DRCRNetAmount
SELECT qryDIV_ExceptionArchive.Company, qryDIV_ExceptionArchive.GEAC, qryDIV_ExceptionArchive.CostCenter, qryDIV_ExceptionArchive.TRECSAccount,  Count(IIf([ExceptionType]="OldImm" Or [ExceptionType]="OldMat",1)) AS AgedCount, Format(nz(Sum(IIf([ExceptionType]="OldImm" Or [ExceptionType]="OldMat",IIf([DRCR]='CR',[Amount]*-1,[Amount]))),0),"Currency") AS AgedSum, Count(qryDIV_ExceptionArchive.ImportAccount) AS TotalOutstandingCount, Count(IIf([ExceptionType]="NewMat",1)) AS NotAgedMaterialCount, Format(nz(Sum(IIf([ExceptionType]="NewMat",IIf([DRCR]='CR',[Amount]*-1,[Amount]))),0),"Currency") AS NotAgedMaterialSum, DCount("Amount","qryDIV_ExceptionArchive","ImportAge = 0") AS TotalOutstanding, Sum(IIf([DRCR]='CR',[Amount]*-1,[Amount])) AS TotalOS
FROM qryDIV_ExceptionArchive
WHERE (((qryDIV_ExceptionArchive.ImportAge)=0))
GROUP BY qryDIV_ExceptionArchive.Company, qryDIV_ExceptionArchive.GEAC, qryDIV_ExceptionArchive.CostCenter, qryDIV_ExceptionArchive.TRECSAccount, 
PIVOT qryDIV_ExceptionArchive.AgeCat In ("Age0","Age31","Age61","Age91","Age180");

SubReportB

TRANSFORM Sum(IIf([DRCR]='CR',[Amount]*-1,[Amount])) AS DRCRNetAmount
SELECT qryDIV_ExceptionArchive.Company, qryDIV_ExceptionArchive.GEAC, qryDIV_ExceptionArchive.CostCenter, qryDIV_ExceptionArchive.TRECSAccount,  Count(IIf([ExceptionType]="OldImm" Or [ExceptionType]="OldMat",1)) AS AgedCount, Format(nz(Sum(IIf([ExceptionType]="OldImm" Or [ExceptionType]="OldMat",IIf([DRCR]='CR',[Amount]*-1,[Amount]))),0),"Currency") AS AgedSum, Count(qryDIV_ExceptionArchive.ImportAccount) AS TotalOutstandingCount, Count(IIf([ExceptionType]="NewMat",1)) AS NotAgedMaterialCount, Format(nz(Sum(IIf([ExceptionType]="NewMat",IIf([DRCR]='CR',[Amount]*-1,[Amount]))),0),"Currency") AS NotAgedMaterialSum, DCount("Amount","qryDIV_ExceptionArchive","ImportAge = 0") AS TotalOutstanding, Sum(IIf([DRCR]='CR',[Amount]*-1,[Amount])) AS TotalOS
FROM qryDIV_ExceptionArchive
WHERE (((qryDIV_ExceptionArchive.ImportDate)=DateSerial(Year(Date()),Month(Date()),1)-1))
GROUP BY qryDIV_ExceptionArchive.Company, qryDIV_ExceptionArchive.GEAC, qryDIV_ExceptionArchive.CostCenter, qryDIV_ExceptionArchive.TRECSAccount, 
PIVOT qryDIV_ExceptionArchive.AgeCat In ("Age0","Age31","Age61","Age91","Age180");

 

[vbaInet]

Like pbaldy mentioned, you can't refer to two subreport's records simultaneously because the entire subreport of one loads before the other loads.

So re-iterating what pbaldy explained, and since you're using fixed Column Headings, you need to join both queries via an ID and calculate the difference between the fields in there. Then use this query as the record source of a subreport.

 

[April15Hater]

SOLVED: Difference subreport calculating out to zero

Ahhh, makes perfect sense... I programmed one field and got it to work. Now I just have to

BTW, thanks for bailing me out on the post from yesterday regarding the day and night employees. I tried to help that guy but had no idea where he was going with that. I figured I get so much help that I'd give back to the community, but it backfired

Thanks,

Joe

 

[vbaInet]

Glad we could help.

That's what we're here for, to help each other out so don't be afraid to give advice when you feel you can