One challenge I encountered in Oracle Analytics was creating a DV that would automatically follow the current reporting period so that upon opening, it would already have the current reporting period selected, allowing users to dive right in to the analysis without having to set the year and period manually.
There are probably several smarter ways to do this such as loading in a custom calendar table to define the current reporting period, but I'm going to - detail the relatively simple calculations I created to make it possible in OAC using just logic in a modular way - so that 6 months from now, I can figure out what I did!
Now… I'm not an SQL wizard, I'm not a classically trained data engineer, I'm an Accountant with a penchant for conquering my problems using logic! I knew that using SMML calculations would be the key to how I would conquer this. SMML is a powerful tool in OAC and 9 times out of 10, you can solve your problems with some well placed case statements.
So what did I do?
I knew ultimately, to make the "Fiscal Period" filter "automatically" follow the current month, I would need to create a calculation(s) that would drive my parameter and that the format of the calculation result needed to be in the same format as Fiscal Period e.g. "Jul-2026".
I also knew that in my company, the first 14 or so days of each month, we are reporting on the prior period. I therefore knew that whatever I created, I would have to use some logic that says "If the date is < 14, then give me the prior month"
I broke my logic out into 4 individual calculations that gradually combine to give me what I needed.
The first calculation - finding the current period. This was fairly simple - SMML gives you "CURRENT_DATE" which provides… you guessed it… the current date. I knew this would be a key element of automatically tracking the current time and was something I would lean on heavily. Below is the calculation I landed on to find "Current Reporting Period Number" - baking in that anything less than the 14th of the month would result in being the prior period - this allows for weekends and public holidays in the UK which generally fall on a Monday.
Calculation - Reporting Period Number
CASE
WHEN CAST(RIGHT(TRIM(CAST(CURRENT_DATE AS CHAR)),2) AS NUMERIC) < 14
THEN MONTH(CURRENT_DATE)-1
ELSE MONTH(CURRENT_DATE)
END
Explanation:
This formula takes the current date (Today) and applies the below logic
If (CASE) today is less than the 14th of the month
WHEN CAST(RIGHT(TRIM(CAST(CURRENT_DATE AS CHAR)),2) AS NUMERIC) < 14
n.b. CAST is used as "RIGHT" formulae only works on text data types and dates are "Date" data type. TRIM is used to remove leading or trailing spaces. Date format is YYYY/MMM/DD thus why "RIGHT" formulae is used.
Then take the current period number and subtract 1
THEN MONTH(CURRENT_DATE)-1
Else give me the current period number
ELSE MONTH(CURRENT_DATE)
END
Example of what this calculation outputs:
Second Calculation - the eagle eyed amongst you will know that the reporting period calculation outputs in a Numeric format… which is not "Jul"… So to fix this, I created a very simple case statement. I also needed to factor in the end of year cut over. As if we're in January… period number would be 1 and 1-1 = 0… which is obviously not a valid period. I could have probably handled this in the "Else" argument, but I bundled it into the "Dec" statement.
Calculation - Reporting period Month (MMM- format):
CASE
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) IN ('12','0') THEN 'Dec-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '1' THEN 'Jan-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '2' THEN 'Feb-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '3' THEN 'Mar-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '4' THEN 'Apr-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '5' THEN 'May-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '6' THEN 'Jun-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '7' THEN 'Jul-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '8' THEN 'Aug-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '9' THEN 'Sep-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '10' THEN 'Oct-'
WHEN CAST(@calculation("ReportingPeriodNumber") AS NUMERIC) = '11' THEN 'Nov-'
ELSE 'Dec-'
END
Example of what this calculation outputs:
Third Calculation - now that I had period handled… I needed a calculation that would obtain the year. The complicated part of this calculation was building in a clause that would again handle December/January cut over. The calculation is below:
Calculation - Reporting Year:
CASE
WHEN CAST(RIGHT(TRIM(CAST(CURRENT_DATE AS CHAR)),2) AS NUMERIC) < 14 AND SUBSTRING(CAST(CURRENT_DATE AS CHAR) FROM 4 FOR 3) = 'JAN'
THEN YEAR(CURRENT_DATE)-1
ELSE YEAR(CURRENT_DATE)
END
Explanation:
This Formula obtains the "Fiscal Year" element while also handling the year end roll. The logic is similar to the Reporting Period Number calculation, but adds in another layer of logic to handle Year End which takes place in January.
The logic is:
If (CASE) today is less than 14th of the month AND current month = January
WHEN CAST(RIGHT(TRIM(CAST(CURRENT_DATE AS CHAR)),2) AS NUMERIC) < 14 AND SUBSTRING(CAST(CURRENT_DATE AS CHAR) FROM 4 FOR 3) = 'JAN'
Then take the current year and subtract 1
THEN YEAR(CURRENT_DATE)-1
Else give me the current year
ELSE YEAR(CURRENT_DATE)
END
Example of what this calculation outputs:
Fourth and Final Calculation - nothing crazy to see here… Just something to smash the results together to give me a value in the same format as Fiscal Period i.e. MMM-yyyy
Calculation - Oracle Dynamic Period String:
TRIM(CAST(@calculation("ReportingPeriodMonth") AS CHAR))||TRIM(CAST(@calculation("ReportingYear") AS CHAR))
Explanation:
This is a very simple calculation that simply concatenates Reporting Period Month and Reporting Year together to obtain a value in the same format as Fiscal Period
TRIM - removes any leading or trailing spaces in the obtained values (ensures concatenation and length is clean)
CAST is used to change the data type to CHAR (text) for both the reporting period month and reporting year calculations - to allow for concatenation via double pipe ||
|| (double pipe) symbols are used in calculations to Concatenate values together - this only works on "CHAR" data types.
Example of what this calculation outputs:
Now that I have my "Dynamic Period" calculation created, now just the simple step of creating the parameter. Because my calculation only ever provides a single value, setting the parameter to reference the column and setting the initial value to "First Available Value" is all that's required.
Bind the parameter to the Fiscal Period filter in the dashboard and VOILA! a calculation that will ensure the filter always starts on the current reporting period. Simple logic, simple calculations, built in a modular way to help future you understand what you did. Hopefully some of you will find this useful, or it provides some insight into how you can solve your problems using logical steps that feed into each other.
I will caveat this by saying - this probably isn't best practice and there will more than likely be an OAC wizard who can tell you a better way to do this but this is what I did, and so far… it works beautifully.
Have a nice day Folks!
Rob
