Forum Stats

  • 3,853,692 Users
  • 2,264,256 Discussions
  • 7,905,433 Comments

Discussions

How to manually sort columns of an Interactive Report with pivot?

DannyS-Oracle
DannyS-Oracle Member Posts: 165
edited Feb 16, 2017 4:11PM in APEX Discussions

Hi guys,

I just created an Interactive Report, and then do a pivot on the data to achieve the display I want. The problem is, the original ordering of the columns now changed automatically to alphabetical order (the months). I can't find an option to manually re-order the columns of this pivot table (cannot Select Columns under the Actions menu)... Does anyone know a solution for this?

P.S. I am using Apex 5.1

Screen Shot 2017-01-26 at 8.31.45 AM.png

DannyS-Oracle

Best Answer

  • fac586
    fac586 Senior Technical Architect Member Posts: 21,217 Red Diamond
    edited Jan 28, 2017 6:02AM Answer ✓
    DannyS-Oracle wrote:I just created an Interactive Report, and then do a pivot on the data to achieve the display I want. The problem is, the original ordering of the columns now changed automatically to alphabetical order (the months). I can't find an option to manually re-order the columns of this pivot table (cannot Select Columns under the Actions menu)... Does anyone know a solution for this?P.S. I am using Apex 5.1
    Screen Shot 2017-01-26 at 8.31.45 AM.png

    I cannot reproduce the sort order problem when the pivot column data type is DATE. On the other hand, when the report is viewed as a pivot table the pivot column heading is displayed as the underlying date value using the application default date format, ignoring any Format Mask or HTML Expression settings on the column. In my opinion that is a bug.

    There is a workaround:

    • In the report query, retrieve the pivot column month/year value as a DATE, not a string [i.e. use trunc(d, 'mm') rather than to_char(d, 'fmMon-YYYY')].
    • In Shared Components, create a dynamic LOV that returns name/value pairs as to_char(d, 'fmMon-YYYY')/trunc(d, 'mm') for the months in the period.
    • Set the pivot column Type to Plain Text (based on List of Values) using the months LOV.
    DannyS-Oracle

Answers

  • Mahmoud_Rabie
    Mahmoud_Rabie Cloud Solution Architect, Member Posts: 3,216 Bronze Crown
    edited Jan 28, 2017 4:53AM

    Hi DannyS

    I can't find an option to manually re-order the columns of this pivot table (cannot Select Columns under the Actions menu)... Does anyone know a solution for this?

    I am not sure if there is code (by the way it is non-documented) in the JS widget of interactive report to do so.

    I think you have to build your pivot report from scratch using VIEWs and PIVOT statements. I hope the following articles help you.

    http://otechmag.com/magazine/2015/spring/scott-wesley.html

    Vishal's blog: Ways to create Matrix report in Oracle APEX

    oracle concepts for you: Matrix Reports in APEX

    Regards

    Mahmoud

    DannyS-Oracle
  • fac586
    fac586 Senior Technical Architect Member Posts: 21,217 Red Diamond
    edited Jan 28, 2017 6:02AM Answer ✓
    DannyS-Oracle wrote:I just created an Interactive Report, and then do a pivot on the data to achieve the display I want. The problem is, the original ordering of the columns now changed automatically to alphabetical order (the months). I can't find an option to manually re-order the columns of this pivot table (cannot Select Columns under the Actions menu)... Does anyone know a solution for this?P.S. I am using Apex 5.1
    Screen Shot 2017-01-26 at 8.31.45 AM.png

    I cannot reproduce the sort order problem when the pivot column data type is DATE. On the other hand, when the report is viewed as a pivot table the pivot column heading is displayed as the underlying date value using the application default date format, ignoring any Format Mask or HTML Expression settings on the column. In my opinion that is a bug.

    There is a workaround:

    • In the report query, retrieve the pivot column month/year value as a DATE, not a string [i.e. use trunc(d, 'mm') rather than to_char(d, 'fmMon-YYYY')].
    • In Shared Components, create a dynamic LOV that returns name/value pairs as to_char(d, 'fmMon-YYYY')/trunc(d, 'mm') for the months in the period.
    • Set the pivot column Type to Plain Text (based on List of Values) using the months LOV.
    DannyS-Oracle
This discussion has been closed.