Multiple Values in single cell in pivot view

Deepthi Suravaram

Hi All,

I want the report in the below format. I have provided more explanation at the bottom.

Status	JAN 16	FEB 16
NEW	OP1 OP2
Process	OP3	OP6
Closed	OP4

I have a requirement to show multiple values in the same cell. For eg

Data

Month         Opportunity         Status

JAN 16          OP1                 New

JAN16           OP2                 New

JAN 16         OP3                  Process

JAN 16         OP4                  Closed

FEB16          OP5                   New

FEB16           OP6                   Process

and so on.

I am looking for a pivot view

Status     Jan 16    Feb 16

New         OP1     

                OP2

Process    OP3          OP6

Closed     OP4                                       

So here in the above example my two opportunities OP1,OP2 which fall as New should come with a merged cell. Is this possible. Also as you see I need to use pivot view as i want the table to show from Jan - Dec for the year selected.

Regards,

Deep

Gianni Ceresa

So you want to use a text column as measure that you can aggregate (concatenate).

Well ... depends on the physical DB as for example in Oracle you have LISTAGG and you can call it by using EVALUATE (google for that related to OBIEE and you find many examples even here in the forum).

Deepthi Suravaram

Hi Gianni,

Thanks for the quick suggestion. But if I do that, I no longer can have a drill down or navigate to detail report to see more details about the OP1 right ? Also if there are any color coding on OP1 and OP2 separately I won't be able to apply on this report as now OP1,OP2 is considered as single value.

Please correct me if I am wrong.

Gianni Ceresa

Well, of course you lose all these things.

But you can't really ask to have OP1 and OP2 acting like independent cells but be into the same cell, at some point you must chose ...

All these things (drill, style etc.) happen on the cell as each cell represent a single value.

STATUS	JAN 16
NEW	OP1
	OP2

If you want to keep them separate with all the functionality of normal cells they must also be separate, so like the example above you have a single, merged (visually), cell for "NEW", but 2 cells for both OP1 and OP2.

Deepthi Suravaram

Hi Gianni,

Yes, I think that makes sense having them separated as you suggested would work. But how can I achieve this? Is there a way I can do this in pivot table?

Regards,

Deep

Gianni Ceresa

That's the default behaviour of a table / pivot in OBIEE : it will merge dimensions values (if the same).

Just keep in mind that to be able to have OP1, OP2 etc. in a "month" column they must be a measure and not a dimension attribute...

Deepthi Suravaram

Hi gianni,

I understand that for numeric fields it does merge values.

But in this case how do I setup a non numeric field as a measure?

Thanks

Gianni Ceresa

Well ...

You can set an aggregation rule like MAX or MIN and it still return the text ;-)

Deepthi Suravaram

Yup. It worked. I made the Oppty column as metric with aggregation rule and also added the same on the rows section and then hid the column added on the rows section.

Thanks for all the help!

Regards,

Deep

Deepthi Suravaram

Hi Gianni,

If I am putting the Oppty column in the rows the report is not displaying as required. But I am getting multiple records. But if I don't put the Oppty column in rows, the aggregation rule is coming into play and only displaying a single value.

Any other suggestions to achieve this?

Regards,

Deep

Deepthi Suravaram

To further explain as if all the oppty's in status new should fall under same line but if i put the oppty column in the row section, it wouldn't. Hence this wont work.