Display Data and Percentage in Pie Chart
Applies to:
SAP Business Objects BI 4.0
Summary:
The document address a solution to display values and percentages in a Pie Chart in Web Intelligence. In BI 4.0 the values and percentages are displayed when you hover over the Pie but if a user were to use a static version of this where they either export it to Excel or PDF then either the Values or percentages can be shown but not both. This solution provides a work around for this issue.
Universe used:
efashion
1. Created a simple query with Year and Sales Revenue as the Result Objects.
2. Drag both the Objects on the report and convert the table to a Pie Chart.
3. Duplicate the Pie Chart that was just created and right click and select Format Chart on the second Pie Chart.
4. Select Global -> Data Values ->
5. Change the data type to Label and Percent or Percent depending on how you want the Labels to Appear.
6. Change data position to Inside you should have the below values.
7. Select Global -> Background set the values as shown below.
8. After Completing step 7 you should have two Pies as shown below
9. Align the Pie Chart with percentages on top of the original Pie Chart to get the below result. You might have to re size the Pie Chart with percentages to get
a correct fit giving a perception of a single Pie Chart as shown below. I have the title shown on both charts to give a better understanding of the
overlapping charts.
This is a great workaround and definitely helps fill this obvious gap in functionality.
However it only works so far as the values showing outside the "value" chart at the back do not keep changing in character count.
Each time the values shrink or get longer, the "value" pie resizes to balance the share of the landscape bewteen the pie and the values shown...
This solution therefore doesn't work where you are drilling up and down a dimension hierarchy (which is in most cases in my world).
The values at the lower end of the dimension granularity are as expected, less than the values toward the top of the hierarchy, and so, the "value" pie's circumference widens and shrinks to a larger degree than does the "percentages" chart (changes are between 0% and 100% i.e. 3 digits tops).
Nevertheless, I find that even after you get both charts matched perfectly, a mismatch is inevitable upon drill / general use.
SAP please provide a "Value and Percentage" option to pie charts?
Lawrence Onunekwu
Where the above solution doesn't work because as described, the user needs to drill up and down a related dimension hierarchy, I would suggest the following compromise - a toggle between value and %:
1. Create a copy of the pie chart as suggested above, and have either chart display percent or values (either inside or outside the chart - I prefer outside on both, as small slices tend to hide numbers when shown inside)
2. Leave the background on both charts opaque rather than translucent
3. Place the copy directly on top of the original
4. Create a Variable [Value or Percent] as ="" (i.e. an empty string variable)
5. Create an Input Control based on the variable [Value or Percent] with custom lov ("Show Values", "Show Percent Share")
6. On each of the charts, enable "Hide When" using the variable [Value or Percent] to show either chart upon selection of either option on the Input Control
Remember to raise your default chart to the front so it appears upon selection of "All Values" on the Input Control (i.e. where there is no filter)...
This would not show Value and Percent(%) at the same time, but allows the user toggle between either option.
Lawrence Onunekwu
I tried another logic to show "Label; Value; Percentage" together as a Data Value Label in a Pie chart.
Considering objects mentioned in above example try following steps:
1) Create one variable as: Var Label =[Year]+";"+[Sales Revenue]
2) Create a Pie chart and right click to assign data option.
For Pie Chart Sector Size select: Sales Revenue.
For Pie Chart Sector Color select: Var Label.
Now Click on + sign and add another row for sector color.
For this second option select : Year. Hide this object from drop down option.
3) Click ok.
4) From Format Chart option: Data Values select :Label and Percent" option.
5) Click ok and now you should get expected results.
Year and Amount will be displayed by Label and percentage by the percent.
I hope this trick helps.
Great, I tried this and worked for my purpose!
Kunal,
Thanks for this piece of information. We were struggling with this when working with BW data. So we actually utilized a lot of what you provided, but in addition, we made the variable a detail of the dimension. So in the case you have above, it would be like having "Var Label" a detail of the "Year" dimension. Just wanted to pass this along in case anyone has different issues with this from a BW perspective.
Very good tutorial, a query, what if information comes a row with values 0.
In version 4, you see the following:
In version 3, the default empty circle shows:
You can simulate this feature in version 4.???
thanks for your comments