PIE chart using Multiple Measures using Relational connection/FREE HAND SQL in WebI
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 .
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 .
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 .
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 .
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])))
Now create PIE with Ageing Bucket Dimension (from Free Hand SQL source) and Amount variable
Hope this small piece of information saves our time .
Thank you for reading 🙂
Please share your inputs .