# Grouping and Aggregation Problems

• ### Question

•  I have a table that looks like this:

 CCY Security PNL USD AAA 100 USD AAA 200 USD BBB 500 EUR CCC 100 EUR CCC 300 EUR DDD 100

 I'm trying to create a report that looks like this:

 CCY Security PNL USD AAA 300 BBB 500 Total 800 EUR CCC 400 DDD 100 Total 500

In order to collapse the multiple lines for security description, I've grouped the PNL subtotals by security description and I have a report that looks like this but I'm unable to create totals by CCY:

 CCY Security PNL USD AAA 300 BBB 500 EUR CCC 400 DDD 100
Tuesday, October 8, 2013 4:17 PM

• Hi MilesToGoBeforeISleep,

From your description, you have already added a Group grouping by CCY, and then add a Child Group grouping by Security. In this case, if we want to add a total row to calculate the sum of the PNL for each CCY, we can refer to the following screenshot:

Then the report will achieve your requirement:

Thanks,
Katherine Xiong

Wednesday, October 9, 2013 6:02 AM

### All replies

• Hi MilesToGoBeforeISleep,

http://technet.microsoft.com/en-us/library/bb522495(v=sql.105).aspx

_______________________________________________________________________________________

Tuesday, October 8, 2013 4:26 PM
• Group by CCY first then add a child group for Security. In the group footer for CCY do SUM(Fields!PNL.Value).

--------------------------------

--sIbu

Tuesday, October 8, 2013 4:45 PM
• Hi MilesToGoBeforeISleep,

From your description, you have already added a Group grouping by CCY, and then add a Child Group grouping by Security. In this case, if we want to add a total row to calculate the sum of the PNL for each CCY, we can refer to the following screenshot:

Then the report will achieve your requirement: