calculated column for group - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Analysis Server and Reporting Services (2005)

calculated column for group

Author

Topic

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-05 : 07:30:04

I have a matrix which is in following format:

........... Morning|.Afternoon
.... 9 |.10|.11|12.|.1.|.2.|.3.|.4
...|x|y|x|y|x|y|x|y|x|y|x|y|x|y|x|y|
Mon
Tues
etc

What I need to do is insert an average for each column (x,y,z) for Morning, Afternoon and the whole day.
The Morning / Afternoon is a Group using the expression:
=IIF(Fields!oTime.Value="a" OR Fields!oTime.Value="b" OR Fields!oTime.Value="c" OR Fields!oTime.Value="d","Morning",IIF(Fields!oTime.Value="e" OR Fields!oTime.Value="f" OR Fields!oTime.Value="g" OR Fields!oTime.Value="h","Afternoon",""))

I inserted a new column with the following expression:
=RunningValue(Fields!x.Value, Avg, "matrix1_day")
This gives me a running average after each hour.
I tried hiding averages for hours I didn't want to show but white space remained. So calculating for the Group seems better solution.

How can I put a similar calculated column after the Morning / Afternoon group rather than after each hour.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 09:07:56

what are grouping you're applying in matrix?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-05 : 09:21:10

From Matrix properties:
Row Groups:
Matrix1_day: Expression: =Fields!day.Value,
and three other sub groups.
Column Groups:
Matrix1_Morn (Morning or Afternoon): Expression: =IIF(Fields!oTime.Value="a" OR Fields!oTime.Value="b" OR Fields!oTime.Value="c" OR Fields!oTime.Value="d","Morning",IIF(Fields!oTime.Value="e" OR Fields!oTime.Value="f" OR Fields!oTime.Value="g" OR Fields!oTime.Value="h","Afternoon","")),
Matrix1_Time (Hour) : Expression: =Fields!oTime.Value,
there is also [Static Group] - but I'm not sure what this is - I can't edit it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 09:29:54

i think you should be putting a subtotal for Matrix1_Time column group and put the below expression for it

=Avg(Fields!x.Value,"Matrix1_Time")

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-05 : 09:49:02

I added a subtotal to the Matrix1_Time and it has created a column in correct place, but I'm not sure where to add "=Avg(Fields!x.Value,"Matrix1_Time")".
Can I change subtotal calculation in 2005?
Also the subtotal calculated is not correct but is giving the first value from the group, e.g. Morn Total = 9am value.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:00:02

in detail cell for group give this

=IIF(Inscope("Matrix1_Time"),Fields!x.Value,Avg(Fields!x.Value,"Matrix1_Time"))

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-05 : 10:33:01

Thanks it seems to be nearly there.
The average is a bit off maybe you have some ideas?
Values:
Morn: 48 + 91 + 72 + 86 =297 /4= 74.25 but Generated Avg=85.85
Afternoon: 58 + 101 + 92 + 67=318 /4= 79.5 but Generated Avg=66.5

The format property is blank so I don't think it is down to decimal places

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-05 : 10:57:58

Sorry I just noticed that the calculated average is same for each row for 5 days so it seems to be getting average for the field for the week.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 11:02:42

quote:
Originally posted by darkdusky

Sorry I just noticed that the calculated average is same for each row for 5 days so it seems to be getting average for the field for the week.

the expression i gave gives you average for Matrix1_Time grouping. is this representing grouping for week then?

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-05 : 11:50:38

The Matrix1_day group is first row group of matrix (=Fields!day.Value) and Matrix1_Morn (Morn / Afternoon) is first column group.

The average result calculated is should be:
Mon 74.25
Tue 96.5
Wed 94.75
Thur 89
Fri 74.75

It is giving the average of these (85.85) in each day

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 11:55:11

quote:
Originally posted by darkdusky

The Matrix1_day group is first row group of matrix (=Fields!day.Value) and Matrix1_Morn (Morn / Afternoon) is first column group.

The average result calculated is should be:
Mon 74.25
Tue 96.5
Wed 94.75
Thur 89
Fri 74.75
=IIF(Inscope("Matrix1_Time"),Fields!x.Value,Avg(Fields!x.Value,"Matrix1_Time"))
It is giving the average of these (85.85) in each day

ok then try this
=IIF(Inscope("matrix1_day"),Fields!x.Value,Avg(Fields!x.Value,"matrix1_day"))

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-05 : 12:09:04

No that is giving the first value as the average eg. 9am value is same as morning average.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:35:21

try this too
=IIF(Not(Inscope("Matrix1_Time")) AND Inscope("Matrix1_day"),Avg(Fields!x.Value,"Matrix1_Time"),Fields!x.Value)

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-06 : 04:54:24

visakh - thanks for all your help.
The combination which finally worked.
=IIF(Inscope("matrix1_Time"),Fields!x.Value,Avg(Fields!x.Value))

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-01-06 : 05:54:09

visakh - I have another relate problem (should I open a new thread?)
One of my columns contains an aggregate:
=Fields!x.Value / Fields!y.Value) ) * AVG(Fields!z.Value)
I tried placing the existing working calculation into the "Fields!x.Value" i.e.
=IIF(Inscope("matrix1_Time"),CalculationHere,Avg(CalculationHere))

But I get a "aggregates cannot be nested" error.

Any suggestions?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources