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 |
- SAP Managed Tags:
24 Comments
