nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle AI and Analytics

Child Item

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

Find more posts tagged with

Accepted answers

All comments

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
NEW	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.