Averages in Matrix

I am new to Reporting Services on SQL Server 2005 and I need help. I have a report that I have create below using matrices.

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

2005

5

5

10

20

5

5

5

5

5

10

75

The columns represent each week in a quarter and the row represents the year the week is in and the details is the number of new accounts created during that particular week of the year. I have couple of questions.

How do I get and average for the number of account created per week per quarter?

How do I create a percentage of the change of accounts per week per year?

Qtr 1

1

2

3

4

5

6

7

8

9

10

11

12

13

Total

AVG

2004

80

60

70

95

70

85

60

90

110

55

90

90

50

1005

77.31

2005

5

0

5

10

20

5

0

5

5

5

5

0

10

75

5.77

-93.8%

-100.0%

-92.9%

-89.5%

-71.4%

-94.1%

-100.0%

-94.4%

-95.5%

-90.9%

-94.4%

-100.0%

-80.0%

Thanks in Advance.

You can use the InScope function to distinguish subtotal cells from other cells. Please check the MSDN documentation about the InScope function:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSCREATE/htm/rcr_creating_expressions_v1_0jmt.asp

With InScope you can determine the current scope of a matrix cell (e.g. in subtotal or not). You would use an IIF-expression to set the cell expression based on the InScope return values. Note: a matrix cell is "in scope" of column and row groupings, so you need at least two InScope function calls in the case where you have one dynamic row and one dynamic column grouping. E.g.

=iif(InScope("ColumnGroup1"), iif(InScope("RowGroup1"), "In Cell", "In Subtotal of RowGroup1"), iif(InScope("RowGroup1"), "In Subtotal of ColumnGroup1", "In Subtotal of entire matrix"))

Replace "In Cell" with =Sum(Fields!Amount.Value)
Replace "In Subtotal..." with =Avg(Fields!Amount.Value)

However, note that since the subtotal cells share the same cell definition as the group instance cells, adding two "subtotals" (one for "Total", the other for "AVG") at the same level is not supported. One way of solving this is to add a rectangle into the matrix cell and use two textboxes to show the total and the average. Then use conditional visibility on the average textbox to only have it visible for subtotals.

-- Robert

|||

Hi Robert,

Can you pls let us know How can we create a rectangle in matrix?.It

will be a great help to us if you can send the detail.Currently we are

hanging on this issue.We not only need average,we need Count and total

also .The Detail is involved expression to convert from minutes(from

stored procedure) to Hours:min.When we are trying to Give sum on this field,it is unable to find sum.it's giving last value or first value

We need Multiple sub totals also.

Any help is highly appreciated

Thank you Robert,

Raj Deep.A

|||

we need all the three aggregations at the end of the Report as three rows

Thank you Robert,

Raj Deep.A

|||

i try the method that you mention above but when i try to put it at expression it give me some error. for example:

i declare a column group name as "matrix1_columngroup1"

=iif(InScope(matrix1_columngroup1), .....) but the system return "unrecogized identifier" .

can you please help me on this. thanks.

sara

|||

You need to use double quotes around the group name. You should be passing as string i.e.

=iif(InScope("matrix1_columngroup1"), .....)

|||somebody explain me step by step how to work with the InScope function, and if it's possible with SSRS 2000. I have less experience with Reporting Services. I alreay have the groups and all stuff. Thanks