Cognos Analytics

 View Only
  • 1.  Sorting Datatable

    Posted Tue July 13, 2021 07:54 AM

    I can't find a way to sort my datatable. I'm using 11.1.7. I tried to define the sort in the query , but that's not a solution.


    Filip De Decker


  • 2.  RE: Sorting Datatable

    Posted Tue July 13, 2021 12:53 PM

    If you are using a module, you can sort a column in the module.  You can also sort it by setting the sorting rule in the dashboard metadata tree.  The latter can be done on both a module and an uploaded file which is unmediated by being contained in a module

    In both cases you can define the sort of the column by the column's values or by the values of another column in the query subject.

    You can do this by select the query item and choosing properties from the context menu.

    The property which governs sorting is called members. 

    Members controls the display of members as well as sorting.

    The sort properties are members, sort by, order, and Null values.

    Members governs whether sorting and the display of member is enabled, disabled, or automatic, which usually means that the sorting is enabled.

    Sort by is a list of the query items in the query subject where the query item is located.

    By default, the sort by column will be the query item itself.  This means that the default sort will be by the values of the query item.

    You can set the sort by value to any other query item as well.  You could use that to apply a sorting rule which is more appropriate to the nature of the data.  A very simple example of that would be sorting a column with month names in it by another query item which contains the sorting ordinals, as the month names are not alphabetically in the same order as their position in the year.


  • 3.  RE: Sorting Datatable

    Posted Wed July 14, 2021 02:10 AM

    Your solution is perfect when using dashboarding and datamodules, but in my case i'm using the datatable in a report that is based on a package.


    Filip De Decker

  • 4.  RE: Sorting Datatable

    Posted Wed July 14, 2021 09:20 AM
    1. Go to queries.
    2. Add a query item which has the sort order index you want to use to sort the query item which you want to sort.
    You can create a query calculation to create the index if you need it.

    Here's an example of an expression for that.  Keep in mind it would be better if the sort order index exists as a column in the data base as that would reduce processing time and, since these sorts of things usually are needed in tons of reports, you would not need to re-create the expression for each one.

    When MONTH_EN ='January' then 1
    When MONTH_EN ='February' then 2
    When MONTH_EN ='March' then 3
    When MONTH_EN ='April' then 4
    When MONTH_EN ='May' then 5
    When MONTH_EN ='June' then 6
    When MONTH_EN ='July' then 7
    When MONTH_EN ='August' then 8
    When MONTH_EN ='September' then 9
    When MONTH_EN ='October' then 10
    When MONTH_EN ='November' then 11
    When MONTH_EN ='December' then 12
    else 0

    3. Go back to the report page.
    4. Select the query item in the report which you want to apply the sorting rule to.
    5. Click on sort and choose edit layout sorting.
    6. Drag the query item of step 2 into the detail sort list folder.
    7. Set the sort order. By default it is ascending order.
    8. Click OK


  • 5.  RE: Sorting Datatable

    Posted Wed July 14, 2021 09:44 AM

    You described the sort on a normal list. The problem is there is no sort option on a data table. At least not in 11.1.7

    Filip De Decker

  • 6.  RE: Sorting Datatable

    IBM Champion
    Posted Fri February 11, 2022 03:28 PM

    I've experienced similar issues. Seems like data tables are still limited in their functionality. If you group a column, it seems to automatically sort it. I've had moderate success defining the sort in the query by setting the "Pre-sort" for the data item. I say moderate success because it doesn't always seem to work, especially if I do the pre-sort on multiple data items.
    Sorry the response is so late!

    Jonathan McKnight
    5x IBM Champion
    Business Intelligence Analyst
    Nashville, TN

  • 7.  RE: Sorting Datatable

    Posted Mon September 25, 2023 05:40 PM

    Hi all,

    Have you had any success in sorting a report data table column based on another column? I'm not able to find any additional info outside this thread. We're on version 12 now.


    Jeremy Aude

  • 8.  RE: Sorting Datatable

    Posted Tue September 26, 2023 02:52 PM

    Hi Jeremy et al,

    I just checked with the dev-team, the functionality of sorting in a data table was considered, but didn't get implemented.
    You could log an enhancement request and then it can get some visibility for prioritization.

    Kind regards,
    Henk Cazemier