A while ago, I had a request from a client that can be put sort of like this: “we want a report that shows the data, yet the accompanying graph should only show the data evolution for the last 6 months.”
Doesn’t sound that complicated right? We can create two reports, one that shows all the data and another one that will only show the data for the last six months. But this would mean that I’d had to create two nearly identical reports and, even worse, maintain both the reports. This would seriously cut in my coffee time, especially when they would like more reports like this one and can’t decide what specific filtering they want…
So that got me thinking since coffee time is really important for me :)
I used the Sample Sales Lite subject area to create a simple report, as shown below:
Which results in
Seems simple, right? If I now could create a second table, based on the data from the first table but only for the last 6 months, I would be done!
A case statement could help me out here. The only problem here is that I can’t just easily ‘subtract’ 6 months. Thinking of dates, I remembered that the Julian date offered just the thing and, conveniently, it is available in the Sample Sales Lite subject area! (An alternative would be to include a sequence in the time dimension that would identify the months.) I now only need to determine the month number interval and I’m good to go. Using the following function, I can determine the last chosen month in Julian format and subtract 5 from it to define the boundaries for my selected month interval.
case when "More Time Objects"."Julian Month Num" between max("More Time Objects"."Julian Month Num" by 1) - 5 and max("More Time Objects"."Julian Month Num" by 1) then "Time"."Per Name Month" end
I use the ‘by 1’ to make sure the maximum is calculated over all the objects. Using the same logic, I can determine a formula to calculate the revenu for only the last 6 months.
case when "More Time Objects"."Julian Month Num" between max("More Time Objects"."Julian Month Num" by 1) - 5 and max("More Time Objects"."Julian Month Num" by 1) then "Base Facts"."Revenue" end
When I create a pivot table using these two new variables, I get the following result:
This is almost the result that I was looking for, except for that null row… After some investigation, I found out that that null record is the result from an unmatched case statement. Obiee groups all these records into one. To avoid this I have to make sure that the unmatched records in the case statement can be matched to a known record, without influencing the results. We can use one of the dates in the subfiltering ‘between’ statement for this. The formula then becomes:
case when "More Time Objects"."Julian Month Num" between max("More Time Objects"."Julian Month Num" by 1) - 5 and max("More Time Objects"."Julian Month Num" by 1) then "Time"."Per Name Month" else max("Time"."Per Name Month" by 1) end
The resulting pivot table is not quite what I had in mind, but I’m getting there:
The calculation for the last month, the month which I used to store all the ‘else’ values, seems off. Upon examination it appears that all months, other than those mentioned in the ‘Custom Month’ column are summarized in this total:
51,398+117,249+232,638+171,210+221,852+236,112+85,366 = 1,115,826
When I look at the log, I see that OBI performs a REPORT_AGGREGATE for my custom revenue. I can change this manually and see if it summarizes everything like I would expect.
Once this step is completed, I can use the data in a graph to always show the latest evolution of the revenue, if needed, or in a pie chart, or do some other cool stuff with it!