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

1. Create a new document
   
2. Select the Excel data source type and click the OK button
   
3. 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.
   
4. Select the Range Definition radio button, specify range “A1:B8”, and click the OK button (ensure “First row contains column names” field is checked)
   
5. Update Name field in the Query Properties as needed and select Apply Changes and Close
   
6. Refresh to populate the table created from the Speedometer_Template.xls with data
   
7. Change document header, rename report, and save the Web Intelligence Document as needed
8. Right-click the data table and select “Turn into>More Transformations…”
   
9. Select the Donut Chart in the Pie charts section and click the OK button
   
10. With the donut chart selected, go to the Format tab. In the Chart Style section, select a “Custom…” color palette
    
11. Click New to create a new color palette
    
12. 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*
    
13. With the newly created color palette select, click OK
    
14. To ensure the donut sectors do not change color, right-click the donut and select “Series Colors>Set as Default Colors”
    
15. Right-click the donut chart and select Format Chart…
    
16. In Global>General Rotation section, change the Start Angle to 270. Click OK
    

Creating the speedometer variable and updating the speedometer (donut) chart

1. In the Data Access tab, select “Data Objects>New Variable>Measure”
   
2. 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
     
3. Right-click the donut and select Assign Data…
   
4. Update the Pie Sector Size selection to the Speedometer_Variable that was created in the previous steps and click OK
   
5. 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.