Oracle Fusion Data Intelligence

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

Displaying last 12 Months of Data based on Current Month Parameter in OAC

Accepted answer
385
Views
11
Comments
Vaigu_27
Vaigu_27 Rank 3 - Community Apprentice

Hello All,

We have a requirement to show last 12 months of data based on Current Month Period in OAC, we know this can be achieved through OAC Classic Home in FDI,Would like to know how this can be achieved in FDI OAC.

In below example we have used filters in different columns to show data by period which is static. wanted to achieve this in dynamic method.

Please let us know your thoughts on displaying last 12 months of data based on current month filter to accommodate dynamic solution

Thanks,

Vaigu

Best Answer

  • BalagurunathanBagavathy-Oracle
    BalagurunathanBagavathy-Oracle Rank 6 - Analytics Lead
    Answer ✓
    • 1. Create a custom table with fiscal period column and corresponding custom date column containing the first date of the fiscal period.
      2. Extend the prebuilt time dimension using this custom table.
      3. Create a filter based on this custom date column and bind a parameter.
      4. Create an expression filter using
      "Financials - GL Balance Sheet"."Time"."Custom Date" between TIMESTAMPADD(SQL_TSI_MONTH, -12,@parameter("Fiscal Date")(current_date)) and @parameter("Fiscal Date")(current_date)

      Other suggestions are welcome.

      Regards,
      Bala.

«1

Answers

  • Hi @Vaigu_27 try if you can use this session variable as filter -

    for example:

    ("HCM - Workforce Core"."Time"."Month" = VALUEOF(NQ_SESSION.TRAILING_12_GREGORIAN_PERIODS))

    Raghu

  • Vaigu_27
    Vaigu_27 Rank 3 - Community Apprentice

    @Raghavendra Raghunath-Oracle

    I tried to create this filter in DV Workbook couldn't proceed much, is there any documents/blog available to implement this

    Additionally do you think with this approach we can display 12 months of actuals based on the current period selection

    Vaigu

  • BalagurunathanBagavathy-Oracle
    BalagurunathanBagavathy-Oracle Rank 6 - Analytics Lead
    edited July 2

    @Vaigu_27

    Please try using 'Dashboard Filters' visual in DV and bind a parameter with the dashboard filter and use the parameter in the expression filter as follows:

    "PC4"."Posted Date"."Date"<=TIMESTAMPADD(SQL_TSI_MONTH, -12, @parameter("Latest Date")(CURRENT_DATE))

  • an another version of this can be like below

    "HCM - Workforce Core"."Time"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-11,CURRENT_DATE) AND CURRENT_DATE

  • Vaigu_27
    Vaigu_27 Rank 3 - Community Apprentice

    @Raghavendra Raghunath-Oracle @BalagurunathanBagavathy-Oracle ,

    As suggested used a 'Dashboard Filter ' Visual in DV, created a Bind Parameter for Fiscal Period, modified parameter with below expression, getting below error in the screenshot

    "Financials - GL Balance Sheet"."Time"."Fiscal Period" <= TIMESTAMPADD(SQL_TSI_MONTH,-11,CURRENT_DATE) AND CURRENT_DATE

    Do review ,let me know your inputs on what am i doing different here.

    Vaigu

  • BalagurunathanBagavathy-Oracle
    BalagurunathanBagavathy-Oracle Rank 6 - Analytics Lead

    @Vaigu_27

    Instead of using dashboard filters and parameter, if you simply the expression filter below, does it help?

    "Financials - GL Balance Sheet"."Time"."Fiscal Date" between TIMESTAMPADD(SQL_TSI_MONTH,-11,CURRENT_DATE) AND CURRENT_DATE

  • Vaigu_27
    Vaigu_27 Rank 3 - Community Apprentice

    @BalagurunathanBagavathy-Oracle

    Our requirement to display last 12 months of Actual values based on monthly prompt selection from users, hence tried with Filter & Parameter. please advise on any alternate approach to attain this

    , Financials - Balance SHeet doesn't have Fiscal Date has only Fiscal period, wrote the expression based on the code getting below is the error

    "Financials - GL Balance Sheet"."Time"."Fiscal Period" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -11,CURRENT_DATE) AND CURRENT_DATE

    Invalid Expression. A general error has occurred. Message returned from OBIS [ecid:6f99537b-1f72-417e-9ea9-cfa0d78adc82-000640b6,0:1:1:6 ts:2024-07-03T06:32:39.625+00:00]. Query Failed: A comparison is being carried out between non-compatible types VARCHAR and DATE. (HY000)

  • Vaigu_27
    Vaigu_27 Rank 3 - Community Apprentice

    @Raghavendra Raghunath-Oracle @BalagurunathanBagavathy-Oracle ,

    In the Out of Box Financials - GL Balance Sheet Subject Area has Time.Fiscal Period values in Jan-23..Dec-23 format

    I have created a Parameter to select the Month Value, since Month is at Varchar Jan-23,Feb-23

    Created Expression Filter using below syntax to show trailing 12 Months Period

    "Financials - GL Balance Sheet"."Time"."Fiscal Period" >=(TIMESTAMPADD(SQL_TSI_MONTH, -12,@parameter("Fiscal_Period")('Jul-23'))) causes below error

    Invalid Expression. Validation of parameter values failed. A general error has occurred. Message returned from OBIS [ecid:6f99537b-1f72-417e-9ea9-cfa0d78adc82-00067d34,0:1:1:6 ts:2024-07-03T13:42:09.535+00:00]. Query Failed: Function TimestampAdd is called with an incompatible type. (HY000)

    Created Filter Expression to trailing 12 Months.

    "Financials - GL Balance Sheet"."Time"."Fiscal Period" >=(TIMESTAMPADD(SQL_TSI_MONTH, -12,@parameter("Fiscal_Period")('Jul-23')))


    can you guide me with correct expression to display last 12 months of data based on Month Parameter selection in DV Report

    Thanks,

    Vaigu

  • BalagurunathanBagavathy-Oracle
    BalagurunathanBagavathy-Oracle Rank 6 - Analytics Lead
    edited July 3

    • 1. Build a custom table with fiscal period column and corresponding custom date column that contains 1st day of the period.
    • 2. Extend the prebuilt time dimension in "Financials - GL Balance Sheet" subject area using this custom table.
    • 3. Use this custom date column in the filter
    • 4. Bind it with a parameter
    • 5. Create an expression filter using the custom date column.

    Other suggestions are welcome.

    Regards,
    Bala.