dynamic columns display depending on the month end prompt — Oracle Analytics

Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

dynamic columns display depending on the month end prompt

Received Response
13
Views
6
Comments
Manish G
Manish G Rank 1 - Community Starter
edited Aug 13, 2024 6:16PM in Oracle Analytics Cloud and Server

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

Answers

  • choracy69
    choracy69 Rank 6 - Analytics Lead

    Hi,

    Maybe more info, for example: Do you have any time hierarchy? How selections?

    You can set for this columns time level on month.

  • Manish G
    Manish G Rank 1 - Community Starter

    I want to create a balance sheet report where balance will be shown depending on the month selection.

    For eg: if June 2016 is selected then it show end balance by account till june 2016:

    Account                      January     February     March       April        Previous Month May       Current Month June    YTD

    ABC Account                    100            200         300         400                  500                             600                     5454

    I am using General Ledger Transaction Balances Real time subject area in oracle fusion financial cloud.

    Thanks.

  • Manish G
    Manish G Rank 1 - Community Starter

    Please help!

  • ashwarnIDM
    ashwarnIDM Rank 1 - Community Starter

    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

  • Joel
    Joel Rank 8 - Analytics Strategist

    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.

  • Manish G
    Manish G Rank 1 - Community Starter

    How about  Current Year to date value?