Dynamic Ageing Buckets in Webi

[sateesh_kumar1]

Hi Team,

We’ll be asking for dynamic aging buckets many times at customer places. Each user want to see different aging intervals ,so forced to do multiple copies of  same BEx query with different interval for buckets so do Webi report.

I have got Order Number ,Invoice Number ,Aging Days (Difference between 2 dates Due date and current date)  and Outstanding Amount objects from backend (BEx query).

Logic behind : We’ll be having 6 buckets (Number of buckets are fixed) and will change range  of buckets dynamically with 5 input controls entered by user.

Process:

Create 5 Variables with default values which will act as Input controls.

Bucket1 : Measure variable :=15(default value , so at first we’ll get 0-15 range of bucket)

Bucket2 : Measure variable :=15(default value , so at first we’ll get 16-30 range of bucket)

Bucket3 : Measure variable :=15(default value , so at first we’ll get 31-45 range of bucket)

Bucket4 : Measure variable :=45(default value , so at first we’ll get 46-90 range of bucket)

Bucket5 : Measure variable :=90(default value , so at first we’ll get 91-180 range of bucket)

The last bucket we’ll be the >([Bucket1]+...+[Bucket5]) bucket ,which is >180 .

Add Bucket1..Bucket5 variables input controls with Entry Field selection .

Then create 4 variables to get sum of buckets to add up dynamically.

Bucket 1+2                         =[Bucket 1]+[Bucket 2]

Bucket 1+2+3                     =[Bucket 1]+[Bucket 2]+[Bucket 3]

Bucket 1+2+3+4                 =[Bucket 1]+[Bucket 2]+[Bucket 3]+[Bucket 4]

Bucket 1+2+3+4+5             =[Bucket 1]+[Bucket 2]+[Bucket 3]+[Bucket 4]+[Bucket 5]

Now derive values for bucket ranges .Since we are not showing/using Invoice number and Order number in the Webi table .I have included Invoice number and deal number in calculation context. You can remove them when you are working on with your requirement.

Bucket1 value  

=Sum([Outstanding Amount] Where ([Ageing Days] Between(0;[Bucket 1])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 2 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1]) Then  Sum([Outstanding Amount] Where ([Ageing Days]>[Bucket 1])ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1]+1;[Bucket 1+2]))ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 3 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1+2])  Then Sum([Outstanding Amount] Where ([Ageing Days]  >[Bucket 1+2]) ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days]  Between([Bucket 1+2]+1;[Bucket 1+2+3])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 4 Value

=If([Bucket 1+2+3+4+5]=[Bucket 1+2+3]) Then Sum([Outstanding Amount] Where ([Ageing Days] >[Bucket 1+2+3]) ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1+2+3]+1;[Bucket 1+2+3+4])) ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 5 Value

= If([Bucket 1+2+3+4+5]=[Bucket 1+2+3+4]) Then Sum([Outstanding Amount] Where ([Ageing Days] >[Bucket 1+2+3+4])ForEach([L01 Order Number];[L01 Invoice Number])) Else Sum([Outstanding Amount] Where ([Ageing Days] Between([Bucket 1+2+3+4]+1;[Bucket 1+2+3+4+5]))ForEach([L01 Order Number];[L01 Invoice Number]))

Bucket 6 Value

=Sum([Outstanding Amount] Where ([Ageing Days] >([Bucket 1+2+3+4+5]))ForEach([L01 Order Number];[L01 Invoice Number]))

Now ,derive Buckets headers without this the report is not meaningful.

Bucket 1 Header

=0+"-"+[Bucket 1] +" Days"

Bucket 2 Header

=If([Bucket 1+2+3+4+5]=[Bucket 1]) Then "> "+[Bucket 1] +" Days" Else [Bucket 1]+1+"-"+[Bucket 1+2]+" Days"

Bucket 3 Header

=If([Bucket 1+2+3+4+5]=[Bucket 1+2]) Then  "> "+[Bucket 1+2]+" Days" Else [Bucket 1+2]+1+"-"+[Bucket 1+2+3]+" Days"

Bucket 4 Header

=If([Bucket 1+2+3+4+5]=[Bucket 1+2+3]) Then  "> "+[Bucket 1+2+3]+" Days" Else [Bucket 1+2+3]+1+"-"+[Bucket 1+2+3+4]+" Days"

Bucket 5 Header

= If([Bucket 1+2+3+4+5]=[Bucket 1+2+3+4]) Then  "> "+[Bucket 1+2+3+4]+" Days" Else  [Bucket 1+2+3+4]+1+"-"+[Bucket 1+2+3+4+5]+" Days"

Bucket 6 Header

= "> "+[Bucket 1+2+3+4+5]+" Days"

(This part is optional , we can leave blank columns also.)

The last part is to make the columns shrink/hide when there is 0 value specified as bucket value.

Since anyhow we’ll get Bucket1 and bucket2 value (If at least Bucket1 value is specified ) write formula to hide remaining columns and make them auto width with 0 inch .

3^rd bucket column Value

=If([Bucket 3 Header]<>[Bucket 2 Header];[Bucket 3 Value])

3^rd bucket column Header

=If([Bucket 3 Header]<>[Bucket 2 Header];[Bucket 3 Header])

4^th  bucket column Value

=If([Bucket 4 Header]<>[Bucket 3 Header];[Bucket 4 Value])

4^th bucket column Header

=If([Bucket 4 Header]<>[Bucket 3 Header];[Bucket 4 Header])

5^th  bucket column Value

=If([Bucket 5 Header]<>[Bucket 4 Header];[Bucket 5 Value])

5^th bucket column Header

=If([Bucket 5 Header]<>[Bucket 4 Header];[Bucket 5 Header])

6^th  bucket column Value

=If([Bucket 6 Header]<>[Bucket 5 Header];[Bucket 6 Value])

6^th bucket column Header

=If([Bucket 6 Header]<>[Bucket 5 Header];[Bucket 6 Header])

Caution : Don’t implement this logic if report brings more than 10k rows at view time .This can kill performance.

We can follow similar logic to give bucket range at @prompt level too. But the burden on Webi is same.

Thanks to Sabari Vasan. S for seed of this tip.

Hope somebody will find it helpful. Feel free to suggest changes in logic /other way of doing this. :smile: