How to create a Speedometer inside of a Web Intelligence document
Detailed below is the process I developed for displaying a speedometer inside of a Web Intelligence document.
Example:
It should be noted that I will be showing how to create a chart that mimics the functionality of a speedometer but is not a true speedometer chart. The graphic is achieved by applying custom logic and formatting to a donut chart .
The Web Intelligence Rich Client in Standalone mode was used to create the speedometer example.
Creating the speedometer (donut) chart
- Create a new document
- Select the Excel data source type and click the OK button
- Navigate to the Speedometer_Template.xls template (attached as xml) that was either download from this document or the one you created*, select the file, and click Open.
- Select the Range Definition radio button, specify range “A1:B8”, and click the OK button (ensure “First row contains column names” field is checked)
- Update Name field in the Query Properties as needed and select Apply Changes and Close
- Refresh to populate the table created from the Speedometer_Template.xls with data
- Change document header, rename report, and save the Web Intelligence Document as needed
- Right-click the data table and select “Turn into>More Transformations…”
- Select the Donut Chart in the Pie charts section and click the OK button
- With the donut chart selected, go to the Format tab. In the Chart Style section, select a “Custom…” color palette
- Click New to create a new color palette
- Update the palette name and modify the first 7 color settings to mirror the below image. Click OK. *Important: Ensure the 7th color setting is set to white with a 0% opacity*
- With the newly created color palette select, click OK
- To ensure the donut sectors do not change color, right-click the donut and select “Series Colors>Set as Default Colors”
- Right-click the donut chart and select Format Chart…
- In Global>General Rotation section, change the Start Angle to 270. Click OK
Creating the speedometer variable and updating the speedometer (donut) chart
- In the Data Access tab, select “Data Objects>New Variable>Measure”
- Update the variable name and insert the following formula to the variable Formula field. Click OK
- “=If([Target_Value]=0 And [Actual_Value]=0 ; If([Value]=1; 0; If([Value]=2; 0.034; If([Value]=3; 1-.034; If([Value]=4; 0; If([Value]=5; 0; If([Value]=6; 0; If([Value]=7; 1; 1000000))))))); If([Actual_Value] >[Target_Value]; If([Value]=1; 0; If([Value]=2; 0; If([Value]=3; [Target_Value]; If([Value]=4; [Actual_Value] -[Target_Value]; If([Value]=5; 0.034; If([Value]=6; 1-0.034- [Actual_Value]; If([Value]=7; 1; 1000000))))))); If([Value]=1; [Actual_Value]; If([Value]=2; 0.034; If([Value]=3; [Target_Value]-0.034-[Actual_Value]; If([Value]=4; 0; If([Value]=5; 0; If([Value]=6; 1-[Target_Value]; If([Value]=7; 1; 1000000)))))))))”
- In the above formula, [Target_Value] is the value to which you want the green section of the speedometer to stop and the [Actual_Value] is the value to which the speedometer needle should stop. The target and actual values can be hard coded into the formula but this does not allow for the speedometer to update dynamically depending on the information that is brought into the Web Intelligence document.
- A detailed formula breakout is explained later in this document
- Right-click the donut and select Assign Data…
- Update the Pie Sector Size selection to the Speedometer_Variable that was created in the previous steps and click OK
- The speedometer has now been created it has the ability to update if the target and/or actual values are updated
Example of speedometer with additional formatting
Formula breakout
The formula consist 3 main If statements with additional nested if statements. Each of the main If statements looks at the sector values (1 through 7) and changes the values based the following scenarios
- Actual value is zero
- Actual value is less than target
- Actual value is greater than target
- It is assumed that the actual value will never exceed the specified sector 7 value
The primary focus of the comments for the formula below is on the If statement that determines if the Actual Value is greater than the Target Value.
//If Actual value is zero
=If([Target_Value]=0 And [Actual_Value]=0 ;
If([Value]=1; 0;
If([Value]=2;0.034;
If([Value]=3;1-.034;
If([Value]=4; 0;
If([Value]=5;0;
If([Value]=6;0;
If([Value]=7; 1;1000000)))))));
//If Actual value is greater Target
If([Actual_Value] >[Target_Value];
// Sets the first sector value to 0
If([Value]=1;0;
// Sets the second (needle) sector value to 0 because the need is not in the green portion of the speedometer
If([Value]=2;0;
// Sets the third sector value to the target value becuase that is then entire span of the green section of the graph
If([Value]=3;[Target_Value];
// Sets the fouth sector value to the actual value minues the target value
If([Value]=4; [Actual_Value] -[Target_Value];
//The 0.034 is the width of the speedometer needle. This value will need updated depending on how big you want your needle to be in comparison to the span of the graph
If([Value]=5;0.034;
//Takes the span of the graph “1” minus the needle width and minus the actual value and sets it to the sixth sector
If([Value]=6;1-0.034- [Actual_Value];
//The 1; value is the lower invisible portion of the donut graph. The actual value can not exceed the 7th sector value. The 1000000 value is just a filler value
If([Value]=7; 1;1000000)))))));
//If Actual value is less than Target
If([Value]=1; [Actual_Value];
If([Value]=2;0.034;
If([Value]=3;[Target_Value]-0.034-[Actual_Value];
If([Value]=4; 0;
If([Value]=5;0;
If([Value]=6;1-[Target_Value];
If([Value]=7; 1;1000000)))))))))
Contents of the Excel template
Attached is the file that was used as a template to create the speedometer graph. Additionally, the template can be created by creating a table mirroring the table below in Microsoft Excel and saving the file in a .xsl* format.
Donut_Area | Value |
---|---|
Sector 1 | 1 |
Sector 2 (Needle) | 2 |
Sector 3 | 3 |
Sector 4 | 4 |
Sector 5 (Needle) | 5 |
Sector 6 | 6 |
Sector 7 | 7 |
Hi,
Great trick, I would ask for it to be added to Webi Tricks !
Well done,
Thanks for sharing !!!!!
Cheers,
Rogerio
Done 😉 Really cool by the way !
Yeah !
It shows that creativity plays a great role on any technical stuff !!
Thanks. Because this post has had such a positive response in a short time, I will be posting additional SAP tricks that I have been using to make my life easier.
Regards,
That´s great Jordan,
keep posting !!!
Cheers,
Rogerio
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
William
Thank you for adding me to Webi 4.x tricks, William.
You're welcome.
Sharing knowledge is so positive for us, really good trick ! 🙂
This is simply amazing! Thanks for sharing!
Thank you. I hope to posting more soon.
Thanks for sharing
Hi Jordan,
This is cool, appreciate your effort to write this up. I had this idea on my mind, spoke about it to a friend a week ago as he wanted to do this. I'm sure, he's going to use this post.
Thanks,
Mahboob Mohammed
Mahboob,
Thank you. I hope you find the information useful and if you have any questions, I am happy to help where I can.
Regards,
Jordan Bishop
I didn't understand where do you actually define target_value and actual_value? Excel has only "Value".
Hello Heli-Riita,
The target_value and actual_value is defined in the Formula Breakout section. In the formula, change [Target Value] and [Actual Value] to your desired values. The excel document is only used to create the template for the donut graph.
Kind regards,
Jordan Bishop
Thank you for clarifying this 🙂
Hi Jordan, I want create half donut chart, how to include total value in Pie/Donut Chart
Suchitra,
If you want to include the total or total label, I would suggest creating a calculated cell and overlay that cell with the Pie/Donut chart.
I am unable to calculated Total Sum and status in same table, pls help me on creation of dimension with all status + Total
I am not sure I understand your requirement but if you are looking to sum the values of all your measures together, try the following:
Create a new variable that sums and adds together all your measures. An example formula is below:
Variable "Total"
Formula = Sum([Measure-1])+Sum([Measure-2])+Sum([Measure-3])+Sum([Measure-4])
I need A, B, C & (Total) as the dimensions-1 in one variable.
You can use the same variable to get the totals for all measures broken out by your dimension.
Drag in your dimension and then drag in the Total variable and it sum the measures for A, B, and C.
I need to get this in Webi, any suggestion on creating varibale to get A, B, C & SUM in one column
Hi
Is this trick can work also on WEBI reports based on BEX query?
If yes, can you explain the link between the Excel data and BEX data?
Thanks
Ran