InScope Example, trouble with multiple row groups

Question

I'm trying to learn the function InScope.

I made a simple matrix report with one row group. The matrix is called "matrix1". The row group is called "matrix1_group1". I created a subtotal for this group. In the data region, I have the following expression.

=iif(inscope("matrix1_group1"),
      "Within Group1 Scope",
      iif(inscope("matrix1"),
          "Within Matrix Scope",
           nothing))

When I preview this report, I get the expected result. That is, on group1's lines, I see the text "Within Group1 Scope" and on the subtotal line, I see the text "Within Matrix Scope".

Next, I added another row group called "matrix1_group2" to the left side of "matrix1_group1", so that group1 is within group2. I also added a subtotal line for group2.

Now I want to change the expression so that I get the following.

On group1's line, I want to see the text "Within Group1 Scope". (the same as before.)
On group1's subtotal line, I want to see the text "Within Group2 Scope".
And lastly, on group2's subtotal line, I want to see the text "Within Matrix Scope".

To do this, I use the following expression.

=iif(inscope("matrix1_group1"),
      "Within Group1 Scope",
      iif(inscope("matrix1_group2"),
          "Within Group2 Scope",
           iif(inscope("matrix"),
              "Within Matrix Scope",
              nothing)))

But this is not working. The result I get is this.
On both group1's line and group1's subtotal, I get "Within Group2 Scope".
"Within Matrix Scope" message is showing up ok.

If you would like a sample data, you can use this.

create table CityAccountInfo
(
City varchar(50) not null,
Account varchar(50) not null,
SomeNumber int not null,
)
insert into CityAccountInfo(City, Account, SomeNumber) values('NYC', 'A', 10)
insert into CityAccountInfo(City, Account, SomeNumber) values('NYC', 'A', 20)
insert into CityAccountInfo(City, Account, SomeNumber) values('NYC', 'B', 30)
insert into CityAccountInfo(City, Account, SomeNumber) values('NYC', 'B', 40)
insert into CityAccountInfo(City, Account, SomeNumber) values('LA', 'A', 50)
insert into CityAccountInfo(City, Account, SomeNumber) values('LA', 'A', 60)
insert into CityAccountInfo(City, Account, SomeNumber) values('LA', 'B', 70)
insert into CityAccountInfo(City, Account, SomeNumber) values('LA', 'B', 80)

In my report, I used Account for group1 and City for group2. SomeNumber was not used, but I have it here, because after I get the above example working, I plan to do some aggregations on this number based on the Scope.

Answer 1

If you are having trouble following my nested iif() with inscope, would you please try the following.

Make a matrix report with Account and City as row groups. Make the account group inside the city group. Add subtotals for each. For the data region, I would like the aggregates to behave as follows.

On each account line, I would like a sum.
On the subtotal line for account and city, I would like an avg.

When I have just one row group, say account, then I am able to get the account subtotal to be the average of the accounts by doing the following. matrix1_group1 is the group for the account.

=iif(inscope("matrix1_group1"),
      sum(Fields!SomeNumber.Value),
      iif(inscope("matrix1"),
          avg(Fields!SomeNumber.Value),
          nothing))

But when I add the city in, that's when I run into trouble. How would I modify the above to also avg at the city level?

Answer 2

Hi,

 

Let’s take another better sample for understanding the scope in a matrix. Here is the sample data. This dataset named dataset1 have four columns:

 

create

table PersonAccount
(
[Month] varchar(50) NOT NULL,
Person varchar(50) NOT NULL,
Account varchar(50) NOT NULL,
CountYN char(1) NOT NULL
)

insert into PersonAccount([Month],Person,Account,CountYN) values('Jan','John','John-A','Y')
insert into PersonAccount([Month],Person,Account,CountYN) values('Jan','John','John-B','Y')
insert into PersonAccount([Month],Person,Account,CountYN) values('Jan','John','John-C','Y')
insert into PersonAccount([Month],Person,Account,CountYN) values('Jan','Mary','Mary-A','Y')
insert into PersonAccount([Month],Person,Account,CountYN) values('Jan','Mary','Mary-B','Y')
insert into PersonAccount([Month],Person,Account,CountYN) values('Jan','Bill','Bill-A','N')
insert into PersonAccount([Month],Person,Account,CountYN) values('Jan','Bill','Bill-B','N')

 

Create a matrix with a row group named RowGroup1 which group on =Fields!Account.Value;

Create another row group named RowGroup2 which group on =Fields!Person.Value;

Create a column group named ColumnGroup1 which group on =Fields!Month.Value;

Create another column group named ColumnGroup2 which group on =Fields!CountYN.Value;

Create the subtotals for each group. The matrix looks like this:

 

 

 

				=Fields!CountYN.Value	Total
				=Fields!Month.Value	Total
	=Fields!Person.Value	=Fields!Account.Value	=Detail Expression
	Total	Total

 

 

After that, set the detail expression to be:

=switch((inscope("RowGroup1") and inscope("ColumnGroup1")), "detail"

 , InScope("RowGroup1") and InScope("ColumnGroup2"), "RowGroup1"

 ,InScope("RowGroup2")and InScope("ColumnGroup1"), "ColumnGroup1"

 , InScope("RowGroup2")and InScope("ColumnGroup2"),"RowCol1"

 , InScope("dataset1")and InScope("ColumnGroup1"),"column1"
  , InScope("dataset1")and InScope("ColumnGroup2"),"column2"

 , InScope("RowGroup1")and InScope("dataset1"),"Row1"
  , InScope("RowGroup2")and InScope("dataset1"),"Row2"

    , InScope("dataset1"),"dataset"

 )

 

You will get the result to help you understand the scope in a matrix. Here is a demo diagram:

 

			Y		Total
			Jan	total
	John	John A	**	**	**
		total	**	**	**
	total		**	**	**

 

There are two path of scope in this matrix:

Dataset1 -> columnGroup2 -> columnGroup1 -> detail data;

Dataset1 -> rowGroup2 -> rowGroup1 -> detail data;

 

The yellow area in scope of “columnGroup1” and “rowGroup1”, the light blue area in scope of  “rowGroup1” and “columnGroup2”, the red area in scope of “rowGroup2” and “columnGroup1”, the purple area in scope of “rowGroup2” and “columnGroup2”, the blue area in scope of “columnGroup1” and “Dataset1”,the gray area in scope of “Dataset1” and “columnGroup2”, the black area in scope of “Dataset1” and “rowGroup1”,the white area in scope of “Dataset1” and “rowGroup2”, at last, the green area in scope of the matrix’s dataset “Dataset1” .

 

So you can specify your own expression for the different scope.

 

Please let me know if this helps.

Raymond

 

Answer 3

Raymond, I am finally getting around to testing this. Makes sense so far.

Answer 4

Hey Raymond, I am trying to transfer what you did above (which is 2 x 2) to something that has 3 row groups and 2 column groups (3 x 2). I think I understand the concept, but cannot make it work. So the data set would be something like,

create

table PersonAccount
(
AdditionalColumn varchar(50) NOT NULL,
[Month] varchar(50) NOT NULL,
Person varchar(50) NOT NULL,
Account varchar(50) NOT NULL,
CountYN char(1) NOT NULL
)

insert into PersonAccount(AdditionalColumn,[Month],Person,Account,CountYN) values('X','Jan','John','John-A','Y')
insert into PersonAccount(AdditionalColumn,[Month],Person,Account,CountYN) values('X','Jan','John','John-B','Y')
insert into PersonAccount(AdditionalColumn,[Month],Person,Account,CountYN) values('X','Jan','John','John-C','Y')
insert into PersonAccount(AdditionalColumn,[Month],Person,Account,CountYN) values('X','Jan','Mary','Mary-A','Y')
insert into PersonAccount(AdditionalColumn,[Month],Person,Account,CountYN) values('X','Jan','Mary','Mary-B','Y')
insert into PersonAccount(AdditionalColumn,[Month],Person,Account,CountYN) values('X','Jan','Bill','Bill-A','N')
insert into PersonAccount(AdditionalColumn,[Month],Person,Account,CountYN) values('X','Jan','Bill','Bill-B','N')

Notice I added an additional column and simply populated with 'X'.
Now, take this additional column and make it the outter most row group.
So, following your naming convention from above, you have RowGroup3 = Fields!AdditionalColumn.Value

Answer 5

can you help me solving this
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/a19090b9-cf4b-44af-acac-1b9a2d4720f0

Answer 6

can you help me solving this
http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/a19090b9-cf4b-44af-acac-1b9a2d4720f0

Answer 7

Hi Raymond,

I have problem doing subtotals for different column groups in a matrix

					Permanent on Loan			Permanent		Total
	WAE No Domicile	WAE Local	WAE Non Local	Total			Total		Total
Jun 09	169	753	92	1014	0		0	2091	2091	3105
Jul 09	38	368	52	458	14		14	1674	1674	2146
Aug 09	13	37	18	68	0		0	869	869	937
Sep 09	54	380	47	481	0		0	2340	2340	2821
Oct 09	16	287	25	328	0		0	747	747	1075
Total	290	1825	234	2349	14		14	7721	7721	10084

In the above report WAE and Permanent are two column groups now I want the subtotals for Permanent just like WAE but for some reason which ever group is in the first column of my matrix has prolblem I am using ssrs 2005 any help will be appreciated.
Thank You.

Answer 8

Hi Suryatej,

 

I would suggest you open a new thread and post the link here in the future, you may get more responses, because this thread has been closed.

 

Anyway, back to the sample you posted, I cannot see the problem. Three subtotal and one grand total:

169

753

92

1014

0

0

2091

2091

3105

 

1)    sum(169, 753, 92 )= 1014

2)    sum(0) = 0

3)    sum(2091) = 2091

grand total sum(1014,0,2091) = 3105

 

Which value is wrong, or what’s the respected result? From my opinion, the matrix works fine. Because there is only value belong a certain group so it sum only one value sum(0), it’s not the first value, it’s the only one value.

 

Or could you post a picture here, this forum support posting image.

 

Hope this helps,

Raymond

Answer 9

Hi Raymond,

where can I open a new thread? I am totally new to this.

coming back to the question

I have 2 column groups in my matrix using ssrs 2005, my requirement is to get subtotals for those two column groups

for example


                   ColumnGroup1                        ColumnGroup2              GrandTotal
                     P1    P2     p3     Total 1         Q1     Q2   Total 2          Total1+Total2  
Aug08             5      2      6       13              2        5      7                       20
Sep08             8       1      2       11             3        3       6                      17
Oct08              5       4      3       12             1       5        6                      18

This is desired output, Exactly how I wanted my matrix output.

but this is what I have right now



                   ColumnGroup1                  ColumnGroup2                   GrandTotal
                     P1    P2     p3     Total 1    Q1    Total   Q2   Total       
Aug08             5      2      6       13          2         2       5      5                20
Sep08             8       1      2       11         3         3       3       3               17
Oct08              5       4      3       12        1         1       5       5                18


Column Group1 I have no problem total1 is perfect  but in columngroup2 I have totals for each individual item in the group Q1 Total, Q2 Total....ect. but the Grand total is fine again it still gives me desired output.

I am really frustrated working on this problem for 2 days but coudn't fix it any help will be greatly appreciated.

I wanted to post my matrix layout but dont know how to do it even though you said this form supports posting image.

 

Answer 10

I still think there are three collumn groups:

 ColumnGroup1                  ColumnGroup2      "the third one"             GrandTotal
                     P1    P2     p3     Total 1    Q1    Total   Q2   Total       
Aug08             5      2      6       13          2         2       5      5                20
Sep08             8       1      2       11         3         3       3       3               17
Oct08              5       4      3       12        1         1       5       5                18

Maybe the third one is some blank string or Null value.

You can test the group header with below expression:
=iif(fields!columngroup.value is nothing or fields!columngroup.value = "" , "the third one", fields!columngroup.value)

- Raymond

Answer 11

I couldnt find the Group Header to write that expression in page header it will not allow me to write an expression.
There are only 2 groups I am sure about that is it possible the third group is null values from the database column because I have different values from a case statement populating the values based on which grouping is done? Is it a possibility?

Thanks
Surya

Answer 12

" After that, set the detail expression to be:

=switch((inscope("RowGroup1") and inscope("ColumnGroup1")), "detail"

 , InScope("RowGroup1") and InScope("ColumnGroup2"), "RowGroup1"

 ,InScope("RowGroup2")and InScope("ColumnGroup1"), "ColumnGroup1"

 , InScope("RowGroup2")and InScope("ColumnGroup2"),"RowCol1"

 , InScope("dataset1")and InScope("ColumnGroup1"),"column1"
  , InScope("dataset1")and InScope("ColumnGroup2"),"column2"

 , InScope("RowGroup1")and InScope("dataset1"),"Row1"
  , InScope("RowGroup2")and InScope("dataset1"),"Row2"

    , InScope("dataset1"),"dataset"

 ) "

I am not fully following the above:

What do the above highlighted elements refer to is not the Group headings/primary dataset?
Ta

---

MerlinJnr