% Variance Section in Report

[kestefon]

Hi,



I'm trying to create a report that has three sections. Section 1 = Planned Cost; Section 2 = Actual Cost; and Section 3 = % Variance. I was able to build the first two sections easily with my query, but I'm having some trouble with the % variance part. Would I have to build that into the query itself, or is there an easier way to do it within the Access report fields?

Below I've included a mock-up (drawn in Excel) of what I'd like the Access report to look like.



Also, for reference, this is what the query used for my report looks like. Note: The original raw data looks a bit different, but I used a crosstab query in order to get the months of the year to appear horizontally as fields. (That's how I need my final report to look.)

[June7]

What have you done so far in constructing report? How are you creating sections in report? Are you using subreports? Or Grouping & Sorting? I am not sure which will be easiest.

Building variance calcs into query would probably involve a UNION query.

[kestefon]

I'm using groups within a single report, based on a single query (see second screenshot in original post for a sample of how my query is structured). The report is grouped by: DataType, Product.

I was thinking of doing a Union as well, but wanted to confirm whether that was the easiest approach. What would the query look like for calculating % variance?

Thanks!

[June7]

An aggregate query based on raw data (assuming Planned and Billed records are in same dataset) to calculate variance would use expression like:

(Sum(IIf([DataType]="Planned",[data],0) - Sum(IIf([DataType]="Billed",[data],0)) / Sum(IIf([DataType]="Billed",[data],0) * 100 As Variance

Apply group criteria for Brand, Market, YearMo.

[kestefon]

Hmm, it doesn't seem to be working for me. The result is just -100...

SELECT DataType, Market, Brand, Period, ((sum(IIf([DataType] = "Planned", [Data],0)) / sum(IIf([DataType]  = "Billed", [Data],0)))-1)*100 AS Variance
FROM RawData
GROUP BY DataType, Market, Brand, Period;

[June7]

Is your field actually named Data? Are your data values actually "Planned" and "Billed"?

[kestefon]

My table names are slightly different, but I adjusted them accordingly. Here's a sample of how the raw data table is laid out.

[June7]

Does it work with the correct field name? You should have seen popup input prompt because of incorrect name.

Do not select DataType field and do not include DataType in the GROUP BY clause.

[kestefon]

Ah, that's it! I removed DataType and now it works. Thanks!

[kestefon]

One more quick question...how would I go about removing the Div/0 records? I tried doing "WHERE Variance <> "#Div/0", but that didn't work. Is there some sort of error function I can use in the WHERE clause?

[June7]

This will not return the error but also probably not a desired result.

Sum(IIf([DataType] = "Billed", [Actual],1))

I am not sure how to handle situation where there is no Billed data.

Try:

IIf(Sum(IIf([DataType]="Billed", [Actual], 0))=0, Null, Sum(IIf([DataType] = "Billed", [Actual],0)))

If that won't work, might need to do separate aggregate queries for Planned and Billed then join those queries. Then do IIf calc in that query.

IIf(IsNull(SumOfBilled), 0, SumOfPlanned / Nz(SumOfBilled,1) -1) * 100