Month by month report - P&L

[Matt Larkin]

All

I get the impression from the results I am having at the moment that I
am doing something wrong, so I thought I'd turn to the experts.....

We have previously produced P&Ls for our company on a current month
and current YTD basis. My new boss now wants to see each month side
by side on a report, and the aggregated for the whole year. He also
wants the current month's budget showing as a separate column.

I have a grouped query which returns the following from the raw data:-
B_Or_A Heading Period SumofAmount
Budget Income 2003001 £100
Actual Income 2003001 £80
Budget Income 2003002 £105
Actual Income 2003002 £90
etc.....
Budget Expenses 2003001 £100
Actual Expenses 2003001 £85
Budget Expenses 2003002 £95
Actual Expenses 2003002 £90
etc.....

I need this reporting as
Heading Budget_Current_Period 2003001 2003002 etc.. 2003012
Year_To_Date
Income £105 £80 £90 -
£170
Expenses £95 £100 £90 -
£190
(we don't make much profit!)

I need this to be in a report.

I have tried the following:-
Add a parameter to the report called "CurrentPeriod", which user (me)
enters as (say) 2003002.
Create a text box on the report with something like
"iif([Period]=[CurrentPeriod] And [B_or_A] = "Budget",
sum([SumofAmount]))" for the Budget_Current_period column and then
"iif([Period] = (left([Period],4) & "001") and [B_or_A] = "Actual",
sum([SumofAmount]))
This doesn't seem to work though - I get inconsistent responses to
this.

I've tried a crosstab, where I specify the column headings so that I
always get the 12 periods for this year, but this leaves me high and
dry with working out what the current period actually is!

There must be a better way!

Help gratefully received! TIA!

Matt

 

[Matt Larkin]

I have tried the following:-

Add a parameter to the report called "CurrentPeriod", which user (me)
enters as (say) 2003002.
Create a text box on the report with something like
"iif([Period]=[CurrentPeriod] And [B_or_A] = "Budget",
sum([SumofAmount]))" for the Budget_Current_period column and then
"iif([Period] = (left([Period],4) & "001") and [B_or_A] = "Actual",
sum([SumofAmount]))
This doesn't seem to work though - I get inconsistent responses to
this.

Thanks for the overwhelming response to this (!). I worked it out for
myself, and was being dumb - my "iif"'s should have been contained
within a sum (e.g. sum(iif(test=testresult,Value,0)) ) so this was
where I was going wrong.

Matt