Customizable columns in Pivot view

Souvik Sarkar

I have a requirement to show a report in a pivot view where the 'Quarter Period' (Q1,Q2,Q3,Q4) would be on the columns to show 3 or more measures (C1,C2,C3,C4) under each 'Quarter Period', for the 4previous quarter periods which is perfectly fine. Now the challenge is, as per the requirement, there should be additional 2 columns (C5,C6), that is calculated from the existing columns (C1,C2,C3,C4), which should only show under the last 'Quarter Period' i.e. (QP4) and not any other 'Quarter Period' (Q1,Q2,Q3). Also, is it possible to have one column name for a column (C1) in one 'Quarter Period' (Q1) and a different name in other 'Quarter Periods' i.e. (Q2,Q3,Q4). Not very sure if this requirement can be achieved, have tried several workarounds with column CSS style and duplicating columns from the result view, but no luck.

Gianni Ceresa

Hi,

As you saw the pivot view can do some nice things but follow a simple rule: always there or never there.

So if you have 6 columns per quarter (your 4 + 2 calculated) they will be there all the time for all the quarters.

Your quarters is an attribute and so all the measures are replicated for each quarter.

An alternative, also allowing you to have different names is to create measures columns for everything: so 4 measure per quarter for 4 quarters = 16 measure columns.

It require more work, it less dynamic in case your data change but it's the simplest way to get close to what you look for.

Otherwise all you can do is to have empty columns for your calculated C5 and C6 for Q1-3 and values only for Q4, but the column will still be there, just empty ...

[Deleted User]

Gianni beat me to it but you're sounding like you try to resolve a conceptual issue (calling everything "column") with GUI tricks. The distinction between attributes, members inside of those attributes and measures is of great importance.

Presumably your source is a relational one rather than a cube. So the 16 measures he mentioned are one way of doing it. Another is to have dynamic calcualtions which then will return NULL values for certain intersections - think FILTER....USING... here or specifc "Last XYZ" measures aggregating as SUM for everything except for your Quarter which will be LAST.

So once again no "click here" answer since your issue isn't one of just drawing numbers on a report but also a modelling one where your conteptual decisions drive what effectively possible.

And before you ask: Yes you can just write formulae in the analysis to do all this and force your data into form but that's not really recommendable.

Souvik Sarkar

Hi,

Very much agree with your reply, and so with the concept of pivot table in OBIEE. But, I do not want to show the extra columns under all the Quarter Period, that you have suggested to create. Basically, I need to skip two or more columns from the Quarter Period. Attaching a sample temple of what I am required to do.

Thanks for the reply.

Souvik Sarkar

Hi Christian,

So, do you want to say that the design needed for the requirement is quite impossible and is not achievable.

Gianni Ceresa

That's what I said: if you want your design you must forget attributes and make everything measures.

(go back to Christian's comment : attributes and measures act in a different way in the pivot view)

If you create measures for everything is the way to get the closest to your requirement (closest <> exactly the same).

Excel has no structure or rules, a column is a column and Excel do not care if inside you have an attribute or a measure, OBIEE make a difference.

Souvik Sarkar

But I suppose I will have a problem there, the most important thing I am concerned about. Adding columns would increase the no. of columns in Total after field as well, how do I control that? I do not have direct control over the Total columns individually. Also, I need to give a different name to all the Total columns as well, i.e. Total C2 and Total C3 would have a different name rather than C2 and C3, which I believe is not possible.

Sherry George

Template based reporting is better suited for BI publisher than OBIEE.

Souvik Sarkar

Hi Sherry,

The template is not uniform over other reports, I have edited the requirement and saved as a template. The report is basically required to be built in OBIEE.

Gianni Ceresa

Souvik Sarkar wrote:Adding columns would increase the no. of columns in Total after field as well, how do I control that? I do not have direct control over the Total columns individually. 

If you create independent measures for everything your totals will also be the same: you will have to create them by hand as columns with a formula giving you the required totals.

That's why I said that if you take this workaround to get an output close to your requirement you lose all the dynamic things like "total for the quarter" etc.

But actually looking at your excel screenshot: if your C1, C2, C3, C4 are measures columns in OBIEE you can't anyway get a total column automatically as totals are by measure (so you can get Q1-4 for C1, Q1-4 for C2 etc. but not C1+C2+C3+C4 for Q1 etc.).

If C1, C2, C3, C4 is an attribute as well and you have a single measure column you still have a change to get some totals calculated correctly by OBIEE.

To keep it short: you are doing a multi-level report mixing all the levels and putting things one next to each other. That's generally what you have when having an Essbase source and doing financial reports, and there isn't a simple way to do the exact same file in OBIEE (this topic has been covered by 2 blogs https://www.rittmanmead.com/blog/2017/02/financial-reports-which-tool-to-use-part-1/  and https://www.rittmanmead.com/blog/2017/02/financial-reports-which-tool-to-use-part-2/ ).

You can use a workaround and create 26 columns in your analysis replacing all the columns underneath Period 1 to Period 4 and Totals, but as you can imagine it's a nightmare from a maintenance point of view and you can easily have errors as you can make a little mistake in one of the 26 columns formula.

[Deleted User]

Completely the opposite - totally possible, achieveable and doable. Question is just "how" and "with which implications" but Gianni anyways took over again so I need not explain my statement :-)