locked
Help Sorting Multiple Column Totals RRS feed

  • Question

  • All,

    I have a requirement to sort column totals in a my matrix report that has a list of customers on row, two column groups (year and quarter) with column total, and total sales. My sort expression is defined on the (Row Group)->Properties->Sorting Tab as =SUM(Fields!SalesAmount.Value) Z-A. SSRS 2008 R2 does sort except that for multiple years for example (2011 & 2012 in which case I will have two column totals: 2011 totals & 2012 totals) the column total sorts the data alternating between the years Z-A. So for example if all the 2011 sales totals are larger than 2012 sales totals the data sorts per requirement. If some 2012 sales totals are higher than 2011 sales totals, for each row SSRS sorts those 2012 sales totals before the 2011 sales totals. My requirement is to sort the totals Z-A for each year's column total.

    Any help is appreciated.


    Francis, MCTS: BI 2008, MCITP: BI 2008 Stay Thirsty My Friends

    Thursday, November 15, 2012 4:31 PM

Answers

  • Hello,

    Sorry for my delay and misunderstanding about your requirement.

    It seems that you want to add sort for each column group instance in dynamic row group. However, when we specify the sorting in group, the report will sorts groups based on the overall aggregate of the entire group instance. 


    Regards,
    Fanny Liu 

    TechNet Subscriber Support
    If you are
    TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.


    Fanny Liu
    TechNet Community Support



    • Edited by Fanny Liu Wednesday, November 28, 2012 2:36 AM
    • Marked as answer by Fanny Liu Monday, December 3, 2012 9:34 AM
    Wednesday, November 28, 2012 1:06 AM

All replies

  • Hello,

    Based on your descrption,  you want to sort the cstomer rows based on the total values of "SalesAmount” for each "customer"and sort the year columns based on the total values for each "Year". If I have any misunderstanding, please let me know.

    The expression in the “Sorting” tab of the row group has the default scope, “Customer”,(for example, =SUM(Fields!SalesAmount.Value,”Customer”).) So, the order of the “Cusomter” row will be sorted based on the total values for each “Customer”.
    In order to meet the other requirement, please repeat the steps for the column group “Year”: right-click the column group and select “Group Properties”, and using the following expression in the “Sorting” tab.
    =SUM(Fields!SalesAmount.Value) or =SUM(Fields!SalesAmount.Value,”Year”).

    For more information, please see:
    How to: Sort Data in a Data Region

    Regards,
    Fanny Liu

    TechNet Subscriber Support
    If you are
    TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Fanny Liu

    TechNet Community Support

    Friday, November 16, 2012 7:00 AM
  • Fanny,

    I'm now sorting as outlined in your post but still not getting the desire results. My requirement is to sort DESC for each year total but it seems SSRS is looking at both years and altering the sort. I'm not sure if what I want to do is possible - your thoughts.

     

    2011

    2012

    CLIENTS

    Total

    Total

    ABC Cust.

    178

    87

    ZBX Cust.

    46

    56

    HTA Cust.

    21

    73

    Dev Cust.

    47

    18

    LLC Cust.

    25

    9

    XXZ Cust.

    20

    10

    Test Cust.

    18

    8

    111, Cust.

    7

    1

    BCA Cust.

    0

    6

    EFJ Cust.

    4

    1

    AEE Cust.

    3

    1

    Total

    369

    270


    Francis, MCTS: BI 2008, MCITP: BI 2008 Stay Thirsty My Friends

    Monday, November 19, 2012 7:18 PM
  • Hello,

    Sorry for my delay and misunderstanding about your requirement.

    It seems that you want to add sort for each column group instance in dynamic row group. However, when we specify the sorting in group, the report will sorts groups based on the overall aggregate of the entire group instance. 


    Regards,
    Fanny Liu 

    TechNet Subscriber Support
    If you are
    TechNet Subscriptionuser and have any feedback on our support quality, please send your feedback here.


    Fanny Liu
    TechNet Community Support



    • Edited by Fanny Liu Wednesday, November 28, 2012 2:36 AM
    • Marked as answer by Fanny Liu Monday, December 3, 2012 9:34 AM
    Wednesday, November 28, 2012 1:06 AM