Hi Team,

Every now and then we will have requirement to show Pie chart for multiple measures in Webi (It is quite straight and easy in EXCEL !!) .In WebI ,answer is NO .


Case


Assume we have BW data for Accounts Receivables at Document and Line Item level .To bring in all this data into WebI and deriving ageing buckets with respect to dates will hamper performance .

In BEx it is easy to derive Amount from dates into Ageing Buckets 0-30 , 31-45 and 46-60 .

So , now we have 3 key figures .

Measures.png

Users always keen in seeing this totals in PIE . Here starts the problem , because PIE in WebI must be derived based out of dimension values and we don’t have one.

Solution 1: We will have a personal data provider (Excel/Text) with all the required bucket names as values , then create universe , use it as another data provider to build report .

Solution 2:In BO 3.x and BI 4.x , in case you already have a relational universe. Select that universe as a data source , select any of the dimension object ,use Custom SQL

Select ‘0-30′ as “Ageing Bucket” from table name UNION Select ’31-45’  as “Ageing Bucket” from table name UNION Select ’46-60′ as “Ageing Bucket” rom table name

make sure you append from clause for every select .

Pre requisites for using relational connection.


We need one connection that points to any of the relational databases (SQL server/ Oracle..)

Don’t bother if there is no database in your environment .Think , luckily most of us at least use one for BOBJ Auditing or our native teacher EFASHION 🙂 That is going to be our relational connection !!

Solution 3 : Viable Solution (I’m thinking of !!)


From BI 4.1 SP6 , Free Hand SQL as a data source supports at great extension .  We are going to derive the Custom dimension to build the PIE .

Code

Select ‘0-30′ as “Ageing Bucket” UNION Select ’31-45′  as “Ageing Bucket” UNION Select ’46-60’ as “Ageing Bucket”

That’s all , our custom dimension is ready to create PIE. Use this code and run the query .


In Report

Create a variable to redefine all the 3 key figures as one measure with respect to our custom dimension .

Amount =If([Ageing Bucket]=”0-30″;[0-30];If([Ageing Bucket]=”31-45″;[31-45];If([Ageing Bucket]=”46-60″;[46-60])))


Create Chart

Now create PIE with Ageing Bucket Dimension (from Free Hand SQL source) and Amount variable

Pie Chart.PNG

Hope this small piece of information saves our time .

Thank you for reading 🙂

Please share your inputs .

To report this post you need to login first.

2 Comments

You must be Logged on to comment or reply to a post.

  1. Mahboob Mohammed

    Hi Sateesh,

    Nice trick, I’ve implemented this quite a few times. I just wanted to bring to your attention few inconsistencies with ranges in Webi Table Image, Code and/or In Report (formula).

    Webi Table Image: 0-15, 31-45 and 46-50

    Code: 0-30, 31-45, 46-60

    In Report (formula): If… “0-30“;[0-15]…

    Thanks,
    Mahboob Mohammed

    (0) 

Leave a Reply