Dynamic measure column in a pivot view

Souvik Sarkar

I have a report showing a pivot table where the measures are displayed by Quarter Period. For a measure M1 in the 1st quarter period i.e. Q1, I will always need to show a specific column header A, whereas for the other quarter periods i.e. Q2,Q3 and Q4, I need to show the measure column header as B. I can create a CASE WHERE condition to handle this, but don't know how to do it at column header level. I believe this can be done quite easily in RPD by creating a Repository variable, but would want a different approach, from presentation. Any help on the same would be appreciated.

Gianni Ceresa

So for the same measure in a pivot view you want the header of that measure to be different based on the attributes (quarter) ?

Simple: you can't

The header of the measure is repeated for every value of the attribute columns selected, and the same name will be used. So you can use a static name (written by hand) or a variable to provide that name, but the variable will have the same value for every and each value of your attributes, so the column header will be the same.

Keep in mind that while the pivot view can look a bit like Excel it isn't  Excel at all, there are rules and relationships between measures and attributes. So measures are repeated by every value of the attributes.

Souvik Sarkar

So you mean to say there are no solution to this challenge?

Gianni Ceresa

Inside the same pivot view, when the measure is repeated for the various values of your "quarter" column not out of the box.

This doesn't mean you can't do it ...

OBIEE is a web page, a web page content can be changed by using languages the browser can understand and execute, for example javascript.

But the point is: is it worth the effort? You will have to code it yourself, with no guarantee it will work after your next update/upgrade, when printing or exporting it will also not work. In my opinion it's not worth the effort ...

Thomas Dodds

You are asking the tool to do something it cannot do by design ... what I have done in the past is:

Create a single hybrid logical column to hold past actuals and future forecasts, then by putting the one column in  pivot (or chart) you can show a continuous line, then it is also easy to change the line style/color by time period to differentiate actual form forecast.

JustTheFacts

I may be misunderstanding this requirement, but is this the sort of thing you want (I have added "Product" because it wasn't clear what was on the Y axis of your pivot):

	Q1	Q2	Q3	Q4
Product	M1	B	B	B
P1	nnn	nnn	nnn	nnn
P2	nnn	nnn	nnn	nnn
P3	nnn	nnn	nnn	nnn
P4	nnn	nnn	nnn	nnn

Where all of the 'nnn' are actually measure "M1" from your model?

If that's what you're after, you can do it pretty easily. There is nothing special about the "Measure Labels" item in a Pivot Table. It can be excluded like any other element, and replaced with the column containing your CASE statement. Here's an example from one of my models. I just have a CASE looking at the value for quarter - if it's 1, then the column has 'M1' otherwise it has 'B'. I excluded Measure Labels and put in that column beneath the Quarter dimension labels.

Sorry if I misunderstood.

Gianni Ceresa

You understood right (at least that's what I also understood...)

Good workaround, didn't cross my mind ...

So you have a new attribute column with provide the label, and a single measure column which you exclude the header and visually your newly created attribute column with the CASE WHEN looks like if it's the header of the measure.

Nice way

Of course it works with a single measure only, so I hope the OP isn't looking to add few more next to it

JustTheFacts

Challenge accepted!

If there is more than one measure, it's a bit trickier but still doable. You have to pivot your Criteria tab, sort of, by using a union query. So instead of having Region, Quarter, Label, M1, M2 in the criteria, you have two unioned sub-queries:

Region, Quarter, Label, M1

--union--

Region, Quarter, Label, M2

The "Label" column is the CASE statement appropriate for M1 and M2, respectively. In my example above I just use M1/M2 for quarter 1, and then B/B2 for all other quarters.

Of course union queries introduce their own issues, so this may not work in all situations.

Gianni Ceresa

Ahahaha

Yeah, but the "issues" introduced by the union, as you say, can be too much (losing drill, actions etc.) without forgetting the performance side.

I would still suggest to challenge the requirement in this case, remembering the user OBIEE <> Excel (and that Excel is extremely cheaper if that's what they want).

Souvik Sarkar

That can be achieved easily had there been a single measure column. Have tried that already. What my limitation is, there are more than one measure columns in the report. Moreover, the requirement is such that it demands a combined report to meet the bare requirements of the analysis. So, any other workaround is appreciated.

[Deleted User]

Write a new pivot-table interpretation in D3.