How to Bring “Others” in a Pie chart when ranking is applied in the data.

Let’s say there is a set of Region and there Revenue like below:

Region	Revenue
East Japan	1,012,727
Bavaria	783,520
Mid-West	441,594
South	400,899
West	324,640
East Germany	259,752
West Japan	33,292
East Coast	19,124
Ruhr	10,976

My requirement is to get the TOP 3 Region based on Revenue and to display its percentage share from overall revenue. From below table we want to create a pie chart using Region and Percentage column.

Region	Revenue	Percentage_Share_overall
East Japan	1,012,727	30.81%
Bavaria	783,520	23.84%
Mid-West	441,594	13.44%
Sum:	2,237,841	68.09%

Pie chart always display total out of 100, in the above block, the percentage total is 68.09% only.

This will not come in a pie chart properly.

Option will be to show Other’s contribution and show in the pie chart.

For eg.

Region	Revenue	Percentage
Bavaria	783,520	23.84%
East Japan	1,012,727	30.81%
Mid-West	441,594	13.44%
Others	1,048,683	31.91%
Sum:	3,286,524	100.00%

This table can now be properly displayed in Pie chart like below:

Now comes the interesting part, how to bring value “Others” in the data.

I followed below steps:

Step: 1 Added an object in universe with Select statement as ‘Others’ and pointed it to Region from List of tables. Like below:

Step: 2 In the Webi Report which already has Region and revenue data, created another query selecting the newly created object(Region-Others) and revenue.

Step 3: Merge the object Region and Region-Others.

Step 4: Created four variables:

a.       Revenue_variable=If([Region]="Others";[Query 1 (1)].[Revenue];[Query 1].[Revenue])

This variable is used to give data for others in the block along with other regions.

b.      Variable1 = [Query 1 (1)].[Revenue]-(Sum([Query 1].[Revenue]) In Block)

This is used to subtract the value of all regions-sum of regions in block, which will assign others data.

c.       Variable2=If([Region]="Others";[Variable1];[Revenue-Variable])

This issued to display the values for all regions including “Others”.

d.      Final Percentage=([Variable2]/Sum([Variable2]) In Block )

This is used for calculating percentage.

Region	Revenue-Variable	Variable1	Variable2	Percentage
Bavaria	783,520	-3,286,524	783,520	0.24
East Coast	19,124	-3,286,524	19,124	0.01
East Germany	259,752	-3,286,524	259,752	0.08
East Japan	1,012,727	-3,286,524	1,012,727	0.31
Mid West	441,594	-3,286,524	441,594	0.13
Others	3,286,524	0	0	0
Ruhr	10,976	-3,286,524	10,976	0
South	400,899	-3,286,524	400,899	0.12
West	324,640	-3,286,524	324,640	0.1
West Japan	33,292	-3,286,524	33,292	0.01

Step 5: Applied ranking based Revenue_variable. Although I need Top 3 , I selected Top 4(4^th for the Others)

Region	Revenue-Variable	Variable1	Variable2	Percentage
Others	3,286,524	1,048,683	1,048,683	31.91%
East Japan	1,012,727	-2,237,841	1,012,727	30.81%
Bavaria	783,520	-2,237,841	783,520	23.84%
Mid West	441,594	-2,237,841	441,594	13.44%

Step 6: Using region and Percentage turned it to Pie chart.

So the pie chart is correct now.

Note: If you have query filters in Query 1, apply same filter in query 2 also.

Scope of Analysis

Seems very small topic, but I have seen people with different understanding about its functionality in Business Objects. 

So let’s see what exactly it is.

Let’s say I have a hierarchy in universe YearàQuarteràMonthàWeek.

So it means, in the report when I am drilling at these levels it will go through the above hierarchy.

What this feature “Scope of Analysis “actually does?

This is an option in web intelligence query panel.

It allows to set the scope at query time.

For eg. If I selected Year and Revenue in my query and the sql generated here is:

Which is correct.

I ran the report and the results are:

Now I will enable drill option and will try to drill through the hierarchy.

From YearàQuarter

See the results below.

Webi brought quarter in the query.

Same way as you drill down to month and week, it will bring in run time other columns as needed by the drill(it will hit the database to bring the columns data)

Now I will use Scope of analysis and see how it behaves.

Note: Scope of analysis by default is set to none.

I will enable scope of analysis and from drop down I will select  “One level”.

You will notice that it brought Quarter object in scope of analysis and in query, quarter column which is one level after Year but there is no change in result objects pane, there it is still year and revenue.

I will run the report:

Note: Even though we have not selected Quarter in the select, it is there in query and in left pane (data pane).

This means by selecting scope of analysis , we have brought the quarter also, when we ran the query for  year and revenue. 

Even if we drill to quarter from Year now, we don’t have to hit the database to bring quarter results. 

But now if I try to drill further to Month from quarter, query will hit the database to bring month data also. Basically this helps in avoiding database hit each time you are drilling for analysis.

If you already set scope of analysis to Custom, it will bring all the objects in the query for that hierarchy and at time of drilling it won’t hit the database to fetch detail data, as it is already in webi microcube. I have tested the same using SQL profiler.(you can check here when a query hits the database in sql server)

Summary is:

Setting scope of analysis brings objects in the query for the levels you have specified and it wont hit the database while drilling for those levels.

So if you are aware of the users request of a drill you can set this option in the report you have created, so it should not have a time lag at time of drilling.

One important thing to note is in Desktop intelligence reports, it allows drill down till the level you have specified in scope of analysis, for the rest level to drill , you have to right click on the column and use drill by.

But this is not the case with Web Intelligence reports.

There is a Web Intelligence security rights for scope of analysis.

This is in CMCà ApplicationàWebIntelligence

“Extend Scope of Analysis”

What is this and how it works?

Lets see an example:

I am a report designer and create a report with Year and Revenue and I set the “Scope of Analysis” to 1 level.

As such when I run this report , I can drill from YearàQuarter and can also drill to other levels(like month, week)  but than for other levels it will hit the database.

Let’s say I have a user ‘X’, I gave this report to him with “Scope of analysis” set to one level.

Assume he has the right to extend scope of analysis, he will be able to access the report same as I did, i.e. can drill to all levels.

But now, if I go to CMC and deny the right “extend scope of analysis”.

This will restrict the user to drill only till quarter and no further levels as, you have set in your report.

So by this you are actually allowing user to access only the levels you want him to drill down.

Request the reader's to share there views about this topic.