none
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.

    Monday, April 13, 2009 11:47 PM

Answers

  • 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

     
    Wednesday, April 15, 2009 4:13 AM
    Moderator

All replies

  • 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?

    Tuesday, April 14, 2009 12:23 AM
  • 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

     
    Wednesday, April 15, 2009 4:13 AM
    Moderator
  • Raymond, I am finally getting around to testing this. Makes sense so far.
    Wednesday, April 22, 2009 4:46 PM
  • 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

    Wednesday, April 22, 2009 7:46 PM
  • 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.


    Wednesday, October 28, 2009 4:15 PM
  • 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

    Thursday, October 29, 2009 3:37 AM
    Moderator
  • 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.

     

    • Edited by Suryatej Friday, October 30, 2009 8:03 PM Matrix Format
    Friday, October 30, 2009 7:57 PM
  • 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

    Wednesday, November 4, 2009 1:57 AM
    Moderator
  • 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
    Wednesday, November 4, 2009 9:52 PM
  • " 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
    Monday, November 9, 2009 3:40 PM

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


    It's just the content of the cell. The sample is used for displaying different content  in different scope, you will get this:



    Collapsed Group:
    \\


    Similar to SSSRS 2008.

    Regards,
    Raymond
    Wednesday, December 23, 2009 7:05 AM
    Moderator
  • In addition to, ther is a known issue in SQL Services 2005, to display the correct result, you may need to install the Server Pack2 and later version. The issue similar to the below threads:
    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/8baa613c-d91f-4427-aaac-bdfb785f15e8

    http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/67d730c5-3961-4854-b4b5-471fb1a5acbe


    Regards,
    Raymond
    Wednesday, December 23, 2009 7:20 AM
    Moderator