Report Calculation based on Detail Criteria

[bstboy]

The report displays monthly flow (as columns) of inventory for 5 fiscal years (as rows) by product.

I'd like to add a calculation with each break in product that would show the difference only between 2011 and 2010 but still display the detail behind the other years. The fiscal years are generated via a query field called [Fiscal Year] out of a crosstab query.

 

[vbaInet]

I'd like to add a calculation with each break in product...

Are you able to do the bolded bit at the moment?

 

[bstboy]

Are you able to do the bolded bit at the moment?

I just have product set up as a grouping, that's all I mean there.

 

[vbaInet]

So is your crosstab query embedded in the report or is the report feeding from the crosstab query?

 

[bstboy]

So is your crosstab query embedded in the report or is the report feeding from the crosstab query?

Report is based off of the query

 

[Alansidman]

Please read this:

http://www.excelguru.ca/node/7

 

[vbaInet]

Ok. Is your report currently Grouped By ProductID > Sorted By Fiscal Year?

What exactly do you mean by the difference? Perhaps you can show us a screenshot of your report and where you would like this calculation.

 

[bstboy]

Hopefully this helps

 

[vbaInet]

Did you acknowledge Alansidman's post?

 

[bstboy]

Did you acknowledge Alansidman's post?

I read the page, yes.

 

[vbaInet]

Also, this question was unanswered

Ok. Is your report currently Grouped By ProductID > Sorted By Fiscal Year?

Please note that every question we ask helps us in determining what the next steps would be.

If you are sorting by Fiscal Year, you need to change that to a Group By.

 

[bstboy]

Also, this question was unansweredPlease note that every question we ask helps us in determining what the next steps would be.

If you are sorting by Fiscal Year, you need to change that to a Group By.

It's grouped by Product and then Sorted by Fiscal Year.

So I've changed it to group by Fiscal Year.

 

[vbaInet]

Cool. So you want the difference for each month right?

What would the result be if, for example, Jul 2010 is "empty" and Jul 2011 is $28? Would it be $28? And what if Jul 2010 is $28 and Jul 2011 is "empty". Would it be -$28?

And I'm guessing the you want the difference to be 2011 - 2010?

 

[bstboy]

Cool. So you want the difference for each month right?

What would the result be if, for example, Jul 2010 is "empty" and Jul 2011 is $28? Would it be $28? And what if Jul 2010 is $28 and Jul 2011 is "empty". Would it be -$28?

And I'm guessing the you want the difference to be 2011 - 2010?

Correct on your assumptions above....yes 2011-2010

 

[vbaInet]

Ok, so here's what you do.

Taking May as an example:

1. Create two textboxes in the FY group footer, one for 2010 called txtMay2010 and the other for 2011, i.e. txtMay2011
2. In the control source for txtMay2010, put

[B]=Sum(IIF([FY] = 2010, [MayField], Null))[/B]

...do the same for May 2011 by changing 2010 to 2011.
3. Drop a textbox in the Product footer section and enter

=Nz([txtMay2011],0) - Nz([txtMay2010],0)

You can set the FY footer section's Visible property to False so it's invisble.

Also, I'm sure you're aware that you will need to change the names every year.

 

[bstboy]

I got the FY text boxes to show the correct values however the difference calculation is netting to zero across the board, as if it's not recognizing the values and doing 0 - 0. I actually think it sees txtMay2010 and txtMay2011 as Null values even though they are showing values in the FY footer.

(Nevermind, I just combined both IIF into one text box and took the FY text boxes out of the equation)

Also, allow me to take this a step further.

What if I were to filter the report (via multi-select listbox in a form) and allow the user to choose any two fiscal years, so basically the fiscal years are dynamic and not static. How would that work for the formulas that you provided earlier?

 

[vbaInet]

What if I were to filter the report (via multi-select listbox in a form) and allow the user to choose any two fiscal years, so basically the fiscal years are dynamic and not static. How would that work for the formulas that you provided earlier?

It won't. It will require coding.

 

[bstboy]

It won't. It will require coding.

Difficult enough coding to not bother with it? I don't want to make this overly complicated however it would be a nice feature. Like I said before, I know zip about coding. Anybody willing to help?

 

[bstboy]

quick bump on this for the coding of calculating the difference in a dynamic (filtered) report.