# 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 Friday, September 13, 2013 9:19 AM
Friday, September 13, 2013 9:06 AM

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

Thanks,
Katherine Xiong

Monday, September 16, 2013 8:06 AM

### 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: