7 Replies Latest reply on Apr 25, 2017 12:31 PM by Sherry George

# Reg: Group report requirement

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)

• ###### 1. Re: Reg: Group report requirement

to just group durations you can use bins.

• ###### 2. Re: Reg: Group report requirement

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.

• ###### 3. Re: Reg: Group report requirement

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

• ###### 4. Re: Reg: Group report requirement

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.

• ###### 5. Re: Reg: Group report requirement

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.

• ###### 6. Re: Reg: Group report requirement

Hellow All,

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 ,

• ###### 7. Re: Reg: Group report requirement

Hi Raa.BI,

Glad that one of our suggestions worked for you.