dynamic columns display depending on the month end prompt

Question

Can we display below column dynamically depending on the month end selection?

Manish G · Answer

How about  Current Year to date value?

Joel Acha · Answer

Hi @3239449

if I understand you correctly, you have a dashboard prompt which users can use to enter Month end for their Balance Sheet.

FIrst thing to do would be to populate a Presentation Variable in your Dashboard Prompt. Pass this variable to your filter of your balance sheet report.

IF you have a time hierarchy set up, you could filter by the year of the month selected and also add in a less than or equal to file on the month and passing in your presentation variable to this part of the filtering.  This will ensure that report will only pick up data for the year of the month selected and only up to the month selected.

ashwarnIDM · Answer

Dynamic month selection a/c to me is not possible in a select query.

Closest I can get to the desired result is : I can set null values to the months out of range. Lets say I can last 3 month data.

CREATE TABLE SALES ( DEPT NUMBER, JAN NUMBER,FEB NUMBER, MAR NUMBER,APR NUMBER,MAY NUMBER,JUN NUMBER);

INSERT INTO SALES  values( 1,111,222,333,444,555,666);

INSERT INTO SALES  values( 2,311,222,433,444,555,666);

Final result would be this.

"DEPT"                    "JAN"                     "FEB"                     "MAR"                     "APR"                     "MAY"                     "JUN"                     "1"                       ""                        ""                        ""                        "444"                     "555"                     "666"                     "2"                       ""                        ""                        ""                        "444"                     "555"                     "666"

CREATE TABLE SALES ( DEPT NUMBER, JAN NUMBER,FEB NUMBER, MAR NUMBER,APR NUMBER,MAY NUMBER,JUN NUMBER);

INSERT INTO SALES  values( 1,111,222,333,444,555,666);

INSERT INTO SALES  values( 2,311,222,433,444,555,666);

WITH last3Month AS (

SELECT LEVEL-1 AS ID

FROM DUAL

CONNECT BY LEVEL <= 3

)

SELECT * FROM (

SELECT tab.* FROM (

SELECT * FROM SALES

UNPIVOT   INCLUDE NULLS ( SALE  FOR MONTH IN (JAN,FEB,MAR,APR,MAY,JUN))) tab,

last3Month WHERE TO_CHAR(ADD_MONTHS(SYSDATE, -ID),'MON') = tab.month)

PIVOT (

MAX(SALE) FOR MONTH IN ('JAN' JAN ,'FEB' FEB,'MAR' MAR,'APR' APR,'MAY' MAY,'JUN' JUN));

Thanks,

Ashwarn