none
SSRS 2008 - Sort a repeating column in a matrix report

    Question

  • Hi,

    I have a matrix report that has a repeating column group, if the column count is 1, the sort works fine, if the column repeats more than once, (eg. more than 1 group) then the sort fails.

    Example that works!

                Col1  
    Row1     Data1
    Row2     Data2
    Row3     Data3

    Example that fails:

                Col1       Col2
    Row1     Data3     Data5
    Row2     Data1     Data4
    Row3     Data2     Data6

    Is there something I'm missing in setting the sort order or something else?

    Thanks!

    Thursday, April 07, 2011 3:04 AM

Answers

  • Thanks Bilal,

    I didn't know it sorted the data row based first for multi columns.

    How can I make it sort column based? :)

    What I would like to do is sort all of the data based on Column1 only, regardless of what is in Column2 and onwards. Just Column1.
    eg.
                Col1       Col2
    Row2     Data1     Data4
    Row3     Data2     Data6
    Row1     Data3     Data5

    Is it possible? :)


    Hi Andrew Oliver,

    Thanks for your detail sample report's layout with data, We could understand your requirement completely. Actually, we could achieve this requirement by setting a sort to row group. For detail steps, please follow these:

    1. Right-click RowName in Row Group Panel at the left-bottom of your screem.

    2. Select Group Properties..., then move to Sorting tab.

    3. Click Add button, the type in an expression =SUM(IIF(Fields!ColumnField.Value="Column1'sName",Fields!Data.Value,nothing)) 

    4.  To order, please select Z to A

    5. Click OK

    After top steps, I think you could sort the data by Col1.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 11, 2011 8:38 AM

All replies

  • The output you have given for "example that works" and "example that fails" both look sorting correctly. (Rows are sorted as 1,2,3; First row columns sorted 3,5; second row columns sorted 1,4; third row sorted 2,6. Please note that the column grouping/sorting happens within the row, therefore for a given row the sorting takes place with whatever column groups available.

    However if you want to ensure the sorting is applied, please right click the group in the grouping pane, and go to group properties; under the sorting tab select the field to sort.

    Hope this helps. Any way, if the above does not answer your query, please provide more details specifying how you would like the sorting to be applied. 


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    BH

    Thursday, April 07, 2011 4:09 AM
  • Thanks Bilal,

    I didn't know it sorted the data row based first for multi columns.

    How can I make it sort column based? :)

    What I would like to do is sort all of the data based on Column1 only, regardless of what is in Column2 and onwards. Just Column1.
    eg.
                Col1       Col2
    Row2     Data1     Data4
    Row3     Data2     Data6
    Row1     Data3     Data5

    Is it possible? :)

    Thursday, April 07, 2011 4:58 AM
  • Thanks Bilal,

    I didn't know it sorted the data row based first for multi columns.

    How can I make it sort column based? :)

    What I would like to do is sort all of the data based on Column1 only, regardless of what is in Column2 and onwards. Just Column1.
    eg.
                Col1       Col2
    Row2     Data1     Data4
    Row3     Data2     Data6
    Row1     Data3     Data5

    Is it possible? :)


    Hi Andrew Oliver,

    Thanks for your detail sample report's layout with data, We could understand your requirement completely. Actually, we could achieve this requirement by setting a sort to row group. For detail steps, please follow these:

    1. Right-click RowName in Row Group Panel at the left-bottom of your screem.

    2. Select Group Properties..., then move to Sorting tab.

    3. Click Add button, the type in an expression =SUM(IIF(Fields!ColumnField.Value="Column1'sName",Fields!Data.Value,nothing)) 

    4.  To order, please select Z to A

    5. Click OK

    After top steps, I think you could sort the data by Col1.

    Thanks,
    Challen Fu


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, April 11, 2011 8:38 AM
  • Hi Challen,

    Pardon me for jumping in, I do encounter similar problem, using your expression is correct, but my case is the column name were a variable subject to the parameter choose by the users. 

    My case as brief, an annual sales ranking report compare for various number of years (subject to users choose) and year as matrix column, department as matrix row, sort by the highest turnover for the most left hand side of latest year choose by the user only,

    Below example as user choose 2011 as latest year,

                       2011           2010        2009       

    Dept A       100000        80000     50000

    Dept B         75000        90000     70000

    Dept C         50000        70000     60000

    Dept D         25000        30000     50000

    Below example as user choose 2010 as latest year,

                       2010           2010 

    Dept B        90000        70000 

    Dept A        80000        50000  

    Dept C        70000        60000   

    Dept D        30000        50000    

    As you example expression as below, how can I retrieve the column name from time to time when user choose year parameter ?

         =SUM(IIF(Fields!ColumnField.Value="Column1'sName",Fields!Data.Value,nothing))

    Thks in advance for your any suggestion.

    Thursday, March 08, 2012 9:21 AM
  • Hi,

    Please try the below. For the purpose of explanation, I have assumed that your table name is Sales having fields SalesDept, SalesYear, SalesValue. Also I assume that you have a multi value parameter named pYear in the report to select years.

    1) Add a new dataset, name it as dtLatestYearand set its query as below

    SELECT        max(SalesYear) maxSalesYear  FROM   Sales where  (SalesYear in (@pyear))

    2) Add a parameter named pMaxYear, set the parameter visibility to 'hidden'. In default values tab, select 'get values from query', select the dataset added in step 1 (dtLatestYear), select the value field maxSalesYear.

    3) Now modify the group sorting expression as below

    =SUM(IIF(Fields!SalesYear.Value=Parameters!pMaxYear.Value,Fields!SalesValue.Value,nothing)) 


    Hope this helps. Please feel free to question if any further details needed.

    Irshad (BH)


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    BH

    Thursday, March 08, 2012 7:41 PM
  • Hi Irshad,

    Yes, your assumption is correct, I had create a DB view with the information you describe to consolidate complex relation in dozens of tables.

    I had successfully put your approaches in the reports, your solution is absolutely brilliant ! sounds like it create a dynamic variable from time to time when user trigger the report and after complete the selection criteria.

    May I ask if this is the only way to achieve ? what if the tables had only date fields, and InvYear field will only available after the query statement successfully execute (it is for separate the matrix column) ?

    For example,using your dataset query statement, with the date range parameter @InvDateFr and @InvDateTo select by users:

          SELECT max(Year(InvDate)) as MaxTranYr from AR_Invoice where InvDate  = @InvDateTo

    Then, create a hidden parameter pMaxYear same as you proposed with the same dataset, and the matrix row sorting expression as below,

          = Sum(iif(Fields!InvYear.Value= Parameters!pMaxYear.Value,Fields!SalesValue.Value,nothing))

    Since I cannot control the user select the date range which might on found in the transaction table date field, that is if there are no invoice generate at the date selected by users from the parameter @InvDateTo, then there will be no reference store in pMaxYear for the report to sort the latest year column.

    Please advise and thanks for your help in advance.

    Best Regard,

    Friday, March 09, 2012 2:35 AM
  • I have suggested the method I found to be working. There could be other alternative ways to address the same issue. Glad to note that you have applied the logic perfectly to your requirement.

    With respect to the query that you have in picking the Max year, I would suggest to filter with the from and to dates while picking the maximum year as there could be chances where there is no invoices in the year mentioned in InvDateTo but there are invoices in the year given in InvDateFrom or later. The modified query would be like below. (pls note the between clause in filtering the date)

    SELECT max(Year(InvDate)) as MaxTranYr from AR_Invoice 
    where (InvDate  BETWEEN @InvDateFrom and  @InvDateTo)

    Then to avoid a null value getting returned when there is no invoices within the selected data range, and as a result the report producing error, set the current year to be returned. Below given query returns the current year, when the query does not return any record. I hope you db is MS SQL. If not please replace the GETDATE with appropriate system date function.

    SELECT ISNULL(max(Year(InvDate)),Year(GETDATE()) as MaxTranYr 
    from AR_Invoice 
    where (InvDate  BETWEEN @InvDateFrom and  @InvDateTo)

    Hope this helps.

    Irshad


    Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful.

    BH

    Friday, March 09, 2012 12:00 PM
  • You probably solved this already, but you could have also provided them with a list of dates from your data to select from, and in that way ensured they would never select any dates outside the range of your data.
    Tuesday, August 07, 2012 9:30 AM