Detailed below is the process I developed for displaying a speedometer inside of a Web Intelligence document.

Example:

Image24.jpg

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
    Image1.jpg
  2. Select the Excel data source type and click the OK button
    Image2.jpg
  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.
    Image3.jpg
  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)
    Image4.jpg
  5. Update Name field in the Query Properties as needed and select Apply Changes and Close
    Image5.jpg
  6. Refresh to populate the table created from the Speedometer_Template.xls with data
    Image6.jpg
  7. Change document header, rename report, and save the Web Intelligence Document as neededImage7.jpg
  8. Right-click the data table and select “Turn into>More Transformations…”
    Image8.jpg
  9. Select the Donut Chart in the Pie charts section and click the OK button
    Image10.jpg
  10. With the donut chart selected, go to the Format tab. In the Chart Style section, select a “Custom…” color palette
    Image11.jpg
  11. Click New to create a new color palette
    Image12.jpg
  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*
    Image13.jpg
  13. With the newly created color palette select, click OK
    Image14.jpg
  14. To ensure the donut sectors do not change color, right-click the donut and select “Series Colors>Set as Default Colors”
    Image15.jpg
  15. Right-click the donut chart and select Format Chart…
    Image16.jpg
  16. In Global>General Rotation section, change the Start Angle to 270. Click OK
    Image17.jpg

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

  1. In the Data Access tab, select “Data Objects>New Variable>Measure”
    Image18.jpg
  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
      Image19.jpg
  3. Right-click the donut and select Assign Data…
    Image20.jpg
  4. Update the Pie Sector Size selection to the Speedometer_Variable that was created in the previous steps and click OK
    Image21.jpg
  5. The speedometer has now been created it has the ability to update if the target and/or actual values are updated
    Image22.jpg

Example of speedometer with additional formatting

Image23.jpg

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
To report this post you need to login first.

24 Comments

You must be Logged on to comment or reply to a post.

        1. Jordan Bishop Post author

          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,

          (0) 
  1. Mahboob Mohammed

    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

    (0) 
    1. Jordan Bishop Post author

      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

      (0) 
    1. Jordan Bishop Post author

      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

      (0) 
  2. Suchitra Maneesh Suvarna

    Hi Jordan, I want create half donut chart, how to include total value in Pie/Donut Chart

    Status Spend, $M
    Spend $  120.00
    Cancelled $  240.00
    Invoice $  300.00
    Actual $    50.00
    Total $  710.00
    (0) 
    1. Jordan Bishop Post author

      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.

      (0) 
        1. Jordan Bishop Post author

          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])

          webi support.png

          (0) 
            1. Jordan Bishop Post author

              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.

              webi support2.png

              (0) 
  3. Ran Vitels

    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

    (0) 

Leave a Reply