Skip to Content
Author's profile photo Sateesh Kumar Bukkisham

PIE chart using Multiple Measures using Relational connection/FREE HAND SQL in WebI

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 .


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 .

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 .

Assigned Tags

      You must be Logged on to comment or reply to a post.
      Author's profile photo Mahboob Mohammed
      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]...

      Mahboob Mohammed

      Author's profile photo Sateesh Kumar
      Sateesh Kumar
      Blog Post Author

      Hi Mahboob ,

      Okay, I thought of sharing with community.

      That's just Keyfigure names I've used .

      Thank you.