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

DannyS-Oracle

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

fac586

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

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.

Mahmoud_Rabie

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

fac586

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

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.