Oracle Communities

Oracle Analytics and AI

Child Item

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

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

Find more posts tagged with

Reporting

Filters

Customizing Reports

FDI

OAC

Accepted answers

BalagurunathanBagavathy-Oracle

@Vaigu_27

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.

All comments

Raghavendra Raghunath-Oracle

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

@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

@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))

Raghavendra Raghunath-Oracle

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

@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

@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

@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

@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

@Vaigu_27

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.

BalagurunathanBagavathy-Oracle

@Vaigu_27

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.