Recently at a client site we had to connect to some tables in owned by another application and generate some reports for our OBIEE user base. We had no control over the table structures in the external application and we had to generate some time series measures. We couldn’t use the time series wizard or the ago functions provided by OBIEE.
The report had to display all the quarters of the current year and the growth for the same period for each of the quarters.
This is how the repots looks like in a regular table view in answers.
And the picture below shows what the client wanted.
We could have done the same with a complex case expression but we found an alternative which is more elegant and better performing, using pivot table calculations.
First up we need to change the formula for the quarter to the one shown below.
Current year is a repository variable, this could also have been a presentation variable.
And the table view would look like
Now create a pivot table for this with the year on the left and the quarter on the top.
Create 2 calculated items on the year column
The first one (Actuals) as $2 and the second one as $2-$1 (growth) and then hide the details and this would give the sales amt for all the quarters for the current year and the growth.
Posts
