Issues Dynamic Dimension from measure

Marco Zerbini

Hi All,

I have following issue in a report on OBIEE.

I describe the situation, I have 3 fields:

- Field 1 --> Field of Dimension

- Field 2 --> contains this formula (dynamic description)

CASE WHEN COUNT(DISTINCT "Dimension_1."ID_Dimension")>1 THEN 'Multipli'  

ELSE (

CASE MAX("Dimension_1."ID_Dimension") 

WHEN 1 THEN 'VALORE1' 

WHEN 2 THEN 'VALORE2' END) 

END

- Count Distinct ID --> Count Distinct Measure

Below the image1, in this case I have the expected result, but I need to add a new field to calculate other measure.

So, when I add the Field ID (Id that I count distinct), I have following result:

- Image2 --> if I keep the new field ID visibile on report, the rows are dived and the condition on Field 2 COUNT(DISTINCT "Dimension_1."ID_Dimension")>1 THEN 'Multipli' is not work

- Image3 --> if I exclud the new field ID, I get the error. How can I keep this visualization, but without the error?

I need to add the field ID as exclude for other measure and execute the condition on field 2...but without error.

Any Idea?

Thanks,

Marco

[Deleted User]

Hello Marco,

a) Which exact version?

b) Judging from your screenshots I assume you are working on an "analysis" and not a BI Publisher "report"? Those are two completely different technical objects.

c) It all depends on the level of granularity you're looking at. You have to describe your use case and issue more in detail. Are there any dimensional hierarchies involved? Is the attribute you want to count on a lower level of aggregation as the one you want to analyze by? Why is this not handled in the RPD? etc etc.

Marco Zerbini

Hi Christian,

following I reply your questions:

1) Version OAS 5.5.0 with Oracle Analytics 12.2.1.4

2) I means Oracle Analytics (Answers)

3) I try to describe:

I have in source this rows:

ID - FIELD 1 - Dimension

19A - TEXT 1 - Valore1

05A - TEXT 1 - Valore2

In Analytics, I want result on Image1, so:

Field 1 - Case when Field - Count distinct ID

TEXT 1 - Multiple - 2

Where "Case when Field"=Multiple is calculated as: case when count (distinct Dimension)>2 then 'Multiple' else Dimension end.

If I don't put the ID Field, is ok, but I need to put the field as excluded to calculate other complex measure.

I received error in image 3 regard the "Case when Field"

I tried to calculated the "Case when Field" in RPD, but I didn't succeded

Any idea to try?