none
Customize Nested Group expression

    Question

  • Hi,

    I have below nested groups (SSRS 2008 R2) Column Grouping

    Year

    Qtr1

    Month1                                                                              

    Value1            Value2               Value2-Value1

    10                       6                             4

    Month2                                                                              

    Value1            Value2               Value2-Value1

    20                      10                             10

    When it roll up to Qtr Group

    Value1 becomes 20+10 = 30

    Value2  becomes 6+10=16

    Value2-1 becomes 4+10=14

    But i want to customise the roll up to have Value1 for Qtr to display as 

    Month1-Value1 + Month2-Value1 + Month2-Value2

    = 10 + 20+ 10 = 40

    How do i do this in report?

    Thanks.


    • Edited by ANURD Friday, September 13, 2013 9:19 AM
    Friday, September 13, 2013 9:06 AM

Answers

  • Hi ANURD,

    According to your description, it seems that you want to display the value of Value1 normally when the Value1 grouped by Month, while customize display the value of Value1 as “Month1-Value1 + Month2-Value1 + Month2-Value2” when it grouped by Qtr. In Reporting Service, we can add a Total and modify the value expression to customize display the value of Value1. In order to achieve the goal, we can refer to the following steps:

    1. In Column Groups pane, right-click the Month group to select Add Total option to add a total after the Month.
    2. Modify the value expression of Value1 to like below:

    =Sum(Fields!Value1.Value)+last(Fields!Value2.Value)

    3. Under the Total row, type Value1, Value2 and Value1-Value2 in the appropriate location.
    4. Right click the Total column to open the Column Visibility dialog box, click the checkbox for Display can be toggled by this report item, and select Qtr in the drop-down list.
    5. Repeat Step4 in the last two columns to show the two columns when grouped by Qtr.

    The following screenshot is for your reference:
    Design:
     
    Result:
     
    Thank you for your understanding.

    Thanks,
    Katherine Xiong

    Monday, September 16, 2013 8:06 AM
    Moderator

All replies

  • Hi Anurd,

    You can use aggregate of aggregate feature of SSRS 2008 R2.

    Your expression for total should be like below one:

    SUM(SUM(Fields!Month.Value,"Monthgroup"),"QuaterGroup")

    Thanks,

    Vishal


    Friday, September 13, 2013 3:29 PM
  • Hi ANURD,

    According to your description, it seems that you want to display the value of Value1 normally when the Value1 grouped by Month, while customize display the value of Value1 as “Month1-Value1 + Month2-Value1 + Month2-Value2” when it grouped by Qtr. In Reporting Service, we can add a Total and modify the value expression to customize display the value of Value1. In order to achieve the goal, we can refer to the following steps:

    1. In Column Groups pane, right-click the Month group to select Add Total option to add a total after the Month.
    2. Modify the value expression of Value1 to like below:

    =Sum(Fields!Value1.Value)+last(Fields!Value2.Value)

    3. Under the Total row, type Value1, Value2 and Value1-Value2 in the appropriate location.
    4. Right click the Total column to open the Column Visibility dialog box, click the checkbox for Display can be toggled by this report item, and select Qtr in the drop-down list.
    5. Repeat Step4 in the last two columns to show the two columns when grouped by Qtr.

    The following screenshot is for your reference:
    Design:
     
    Result:
     
    Thank you for your understanding.

    Thanks,
    Katherine Xiong

    Monday, September 16, 2013 8:06 AM
    Moderator