Matrix - adding a column for calculating difference

I have a report showing registered hours (invoicable, not invoicable and
total) for the last 3 weeks this year and last year. It looks something like
this:
...............Invoicable hours.........Not invoicable
.........Total
..................2005|2004..............2005|2004............2005|2004
Week
1............12|...13..................5|..10...............17|...23
Week
2............10|...12..................6|..12...............16|...24
Week
3............15|...14..................7|..14...............22|...28
Total...............37|...39.................12|...36...............55|...75
Row groups: Weeks.
Column groups: [Static group], Years
So far, so good. But I'm supposed to add an extra column for each column,
called "Difference". Which is this year - last year's numbers for each of
the different sets of numbers. And I can't figure out how.
I've added a column to the right of my first column, and my current matrix
looks like this:
............Invoicable hours...Difference....Not invoicable
.........Total
...............2005|2004......2005|2004....2005|2004............2005|2004
Week
1.........12|...13.........-1|..-1.........5|..10...............17|...23
Week
2.........10|...12.........-2|..-2.........6|..12...............16|...24
Week
3.........15|...14..........1|...1.........7|..14...............22|...28
Total............37|...39..........-2|..-2.......12|...36...............55|...75
And I need to suppress one row of numbers in the difference column in some
way. Is this possible? How? Add a new column group or do some conditional
visibility check?
All help appreciated!!!
Kaisa M. LindahlHi Kaisa,
Did you manage to get the Totals working, I am facing a similiar problem &
wanted to check how did u manage to do it.
Thanks
"Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
news:uHTZOT6BGHA.1088@.tk2msftngp13.phx.gbl...
>I have a report showing registered hours (invoicable, not invoicable and
>total) for the last 3 weeks this year and last year. It looks something
>like this:
> ...............Invoicable hours.........Not invoicable
> .........Total
> ..................2005|2004..............2005|2004............2005|2004
> Week
> 1............12|...13..................5|..10...............17|...23
> Week
> 2............10|...12..................6|..12...............16|...24
> Week
> 3............15|...14..................7|..14...............22|...28
> Total...............37|...39.................12|...36...............55|...75
> Row groups: Weeks.
> Column groups: [Static group], Years
> So far, so good. But I'm supposed to add an extra column for each column,
> called "Difference". Which is this year - last year's numbers for each of
> the different sets of numbers. And I can't figure out how.
> I've added a column to the right of my first column, and my current matrix
> looks like this:
> ............Invoicable hours...Difference....Not invoicable
> .........Total
> ...............2005|2004......2005|2004....2005|2004............2005|2004
> Week
> 1.........12|...13.........-1|..-1.........5|..10...............17|...23
> Week
> 2.........10|...12.........-2|..-2.........6|..12...............16|...24
> Week
> 3.........15|...14..........1|...1.........7|..14...............22|...28
> Total............37|...39..........-2|..-2.......12|...36...............55|...75
> And I need to suppress one row of numbers in the difference column in some
> way. Is this possible? How? Add a new column group or do some conditional
> visibility check?
> All help appreciated!!!
> Kaisa M. Lindahl
>|||I got the total for the 4 weeks by right-clicking the Row group (Weeks) and
clicked Subtotals.
Then I changed the formula for the =Fields!Measures_Invoicable.Value to
=SUM(Fields!Measures_Invoicable.Value).
Then I added a Subtotal on the Year column group, and changed the column
name to Difference.
I then changed the formula again to check for "direction" and do the right
calculation based on the scope
=IIF(InScope("matrix2_DateWeek_Year"),
SUM(Fields!Measures_Billable_Hours.Value),
(First(Fields!Measures_Billable_Hours.Value, "matrix2_DateWeek_Week") -
Last(Fields!Measures_Billable_Hours.Value, "matrix2_DateWeek_Week")))
Meaning -
If I'm in the bottom (total) row for weeks, it will give me this, and if I'm
going from left to right, I get the difference between numbers for this year
and last year.
Unfortunately, it will now also try to calculate the total of the
difference. But it fails, giving me this:
Invoicable hours
2005 2004 Difference
Week 44 33 30 3
Week 43 20 7 13
Week 42 30 59 -30
Week 41 37 43 -6
Total 120 140 7
Any suggestions on what I can do to make the number in the yellow section
(7) turn out right or just hide the number? Right should be -20, not 7.
(can't figure out how this turns into 7.)
Kaisa
"Vishal" <vrajput77@.hotmail.com> wrote in message
news:O1m6rF$CGHA.1312@.TK2MSFTNGP09.phx.gbl...
> Hi Kaisa,
> Did you manage to get the Totals working, I am facing a similiar problem &
> wanted to check how did u manage to do it.
> Thanks
> "Kaisa M. Lindahl" <kaisaml@.hotmail.com> wrote in message
> news:uHTZOT6BGHA.1088@.tk2msftngp13.phx.gbl...
>>I have a report showing registered hours (invoicable, not invoicable and
>>total) for the last 3 weeks this year and last year. It looks something
>>like this:
>> ...............Invoicable hours.........Not invoicable
>> .........Total
>> ..................2005|2004..............2005|2004............2005|2004
>> Week
>> 1............12|...13..................5|..10...............17|...23
>> Week
>> 2............10|...12..................6|..12...............16|...24
>> Week
>> 3............15|...14..................7|..14...............22|...28
>> Total...............37|...39.................12|...36...............55|...75
>> Row groups: Weeks.
>> Column groups: [Static group], Years
>> So far, so good. But I'm supposed to add an extra column for each column,
>> called "Difference". Which is this year - last year's numbers for each of
>> the different sets of numbers. And I can't figure out how.
>> I've added a column to the right of my first column, and my current
>> matrix looks like this:
>> ............Invoicable hours...Difference....Not invoicable
>> .........Total
>> ...............2005|2004......2005|2004....2005|2004............2005|2004
>> Week
>> 1.........12|...13.........-1|..-1.........5|..10...............17|...23
>> Week
>> 2.........10|...12.........-2|..-2.........6|..12...............16|...24
>> Week
>> 3.........15|...14..........1|...1.........7|..14...............22|...28
>> Total............37|...39..........-2|..-2.......12|...36...............55|...75
>> And I need to suppress one row of numbers in the difference column in
>> some way. Is this possible? How? Add a new column group or do some
>> conditional visibility check?
>> All help appreciated!!!
>> Kaisa M. Lindahl
>