locked
Grouping and Aggregation Problems RRS feed

  • 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

Answers

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

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    Wednesday, October 9, 2013 6:02 AM

All replies

  • Hi MilesToGoBeforeISleep,

    This is accomplished by using Rollup please see this article.

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

    _______________________________________________________________________________________

    Please click the Mark as answer button if I answered your question, and vote as helpful if this reply helps you. Thank you!

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

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

    Please mark as answered if a post solves your problem


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

    If you have any more questions, please feel free to ask.

    Thanks,
    Katherine Xiong

    Wednesday, October 9, 2013 6:02 AM