Hi Team, We have 3 columns region ,duration and units duration is from ( 1 Hrs to 24 Hrs ) and our requirement is we need to group by duration from 8 Hr to till 12 Hr based on units ( like >= 8 hr, >= 9 hr , >= 10 hr , >= 11 hr ,>= 12 hr final report should be like below can any one suggest on this. Version: OBIEE 11.1.1.7 Region Time Units ABC>=8 ( it as to add 8+9+10+11+12)100(Sample Value)ABC>=9 (it as to add 9+10+11+12)80(Sample Value)ABC>=10 (it as to add 10+11+12)70(Sample Value)ABC>=11 (it as to add 11+12)60(Sample Value)ABC>=12 ( it as to sum remaining sum)1000(Sample Value) Thanks in Advance,

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 Analytics and AI

Child Item

Reg: Group report requirement

Newuser1

Hi Team,

We have 3 columns region ,duration and units duration is from ( 1 Hrs to 24 Hrs ) and our requirement is we need to group by duration from 8 Hr to till 12 Hr based on units ( like >= 8 hr, >= 9 hr , >= 10 hr , >= 11 hr ,>= 12 hr final report should be like below can any one suggest on this.

Version: OBIEE 11.1.1.7

Region	Time	Units
ABC	>=8 ( it as to add 8+9+10+11+12)	100(Sample Value)
ABC	>=9 (it as to add 9+10+11+12)	80(Sample Value)
ABC	>=10 (it as to add 10+11+12)	70(Sample Value)
ABC	>=11 (it as to add 11+12)	60(Sample Value)
ABC	>=12 ( it as to sum remaining sum)	1000(Sample Value)

Thanks in Advance,

Find more posts tagged with

Accepted answers

All comments

Sherry George

to just group durations you can use bins.

[Deleted User]

Not sure BINs will do the trick here as he wants to have a) overlapping ranges (which bins could do) and b) wants to have the numbers aggregate with double-counting (multi-counting even) inside the aggregation.

So basically the categorization has to happen pre-aggregation whereas BINs kick in post-aggregation.

(sorry...mobile phone posting joys)

The BIN would have to be based on a formula which forces the lowest level of aggregation (the grain) and then the analysis would have to reconstruct it again and aggregate. Maybe better to push that to the RPD in the first place and nicely model it as a fact-derived attribute in a degenerate dimension.

3068122

Hi @Raa.BI ,

you can create a simple report using REGION , TİME and UNITS colums (I assume that UNITS column is a measure that have SUM aggregation on rpd level). Then use the formula like this for UNITS column

CASE WHEN ("TIME")=8 THEN SUM(FILTER("Units" USING ("TIME")>=8) BY "REGION")

WHEN ("TIME")=9 THEN SUM(FILTER("Units" USING ("TIME")>=9) BY "REGION")

WHEN ("TIME")=10 THEN SUM(FILTER("Units" USING ("TIME")>=10) BY "REGION")

WHEN ("TIME")=11 THEN SUM(FILTER("Units" USING ("TIME")>=11) BY "REGION")

WHEN ("TIME")=12 THEN SUM(FILTER("Units" USING ("TIME")>=12) BY "REGION")

END

Sherry George

Yes you are right, didn't think of it. So in that case another option would be to create a union report, not the cleanest of ideas.

[Deleted User]

But a valid one ;-)

What @3068122 posted is an option which represents one approach but then he'd still need an attribute to split the result set by, representing those tranches of data since the result here is a an aggregated measure.

Newuser1

Hellow All,

Apologies for the delay reply. Thanks for your inputs.

I have achieved the above requirement using combined similar request. I have created 4 criteria's and applied all my filters\logic for time column at each level.

Thanks ,

Sherry George

Hi @Raa.BI,

Glad that one of our suggestions worked for you.