Skip to Content
Author's profile photo Former Member

How to insert a Gauge chart inside a WEBI Document

How to insert a Gauge chart inside a WEBI Document

Hi,

I would like to share how we can insert new visualizations in a WEBI . I´ll use some Javascript code so, in order to work, the document must be viewed in HTML mode.

There is one major problem about this approach that is the chart cannot be printed. If you try to print the document, instead of the chart you will see the Javascript code. I could not find, up to now, how to print the charts generated in with this approach.

I´ll use e-fashion to these exampel.

My task is to show, through gauges, the sales of the stores, per state for the year of 2006.

Why I´m looking at is the following :

/wp-content/uploads/2015/01/final_622124.png
             

The ranges for the colors are :

Red : from 100 to 900 thousand

Yellow : from 900 to 1200 thousand

and Green from 1200 to 3500.

I´m not go into details of how to format the chart. My main goal is to show how the visualization works inside WEBI. I´m using google charts and the link for the Google Charts documentation is :

https://developers.google.com/chart/interactive/docs/gallery/gauge#Example

 

This is the query I used in my example

/wp-content/uploads/2015/01/query_622125.png          

And the result of it is :

result table.png

The HTML to the visualization is something like :

<html>

  <head>

  </head>

  <body>

    <script type=”text/javascript” src=”https://www.google.com/jsapi“></script>

    <script type=”text/javascript”>

      google.load(“visualization”, “1”, {packages:[“gauge”]});

       google.setOnLoadCallback(drawChart);

      function drawChart() {

        var data = google.visualization.arrayToDataTable([

          [‘Label’, ‘Value’],

            [‘New York Magnolia’,1911.43],[‘New York 5th’,1239.59]       ]);

        var options = {

            max : 4000,

          width: 400, height: 120,

            greenFrom :1200, greenTo : 3500,

            yellowFrom :900, yellowTo : 1200,

           redFrom: 100, redTo: 900,

          minorTicks: 5

        };

        var chart = new google.visualization.Gauge(document.getElementById(‘chart_div’));

        chart.draw(data, options);

      }

    </script>

    <div id=”chart_div” style=”width: 400px; height: 120px;”></div>

  </body>

</html>

The red part is the data that feeds the chart.

If you just copy this HTML, drop a blank cell on the report, put this text inside it and set the property “Read as” to HTML it will show the gauge chart shown above.

But this is a hard code chart. If you want to display the sales of, let´s say, Texas, you will have to hardcode the text highlighted in red, which is the data that feed the chart.

But what if you  want to set it to dinamically change the data, without hardcoding it (let´s say through na Input Control over [State]) ?

In order to do so, you will have to create some variables.

Let´s start by creating a standard HTML code  and set it to a measure variable [gauge_preview]. I will change the text highlighted in red to @DATA@

So, [gauge_preview] has the value of :

<html>

  <head>

  </head>

  <body>

    <script type=”text/javascript” src=”https://www.google.com/jsapi“></script>

    <script type=”text/javascript”>

      google.load(“visualization”, “1”, {packages:[“gauge”]});

       google.setOnLoadCallback(drawChart);

      function drawChart() {

        var data = google.visualization.arrayToDataTable([

          [‘Label’, ‘Value’],

            @DATA@       ]);

        var options = {

            max : 4000,

          width: 400, height: 120,

            greenFrom :1200, greenTo : 3500,

            yellowFrom :900, yellowTo : 1200,

           redFrom: 100, redTo: 900,

          minorTicks: 5

        };

        var chart = new google.visualization.Gauge(document.getElementById(‘chart_div’));

        chart.draw(data, options);

      }

    </script>

    <div id=”chart_div” style=”width: 400px; height: 120px;”></div>

  </body>

</html>

Since [Sales revenue] is formatted as currency, you need to get the formatted number without the $ sign and the thousand separator.

  [sales_form] = FormatNumber([Sales revenue]/1000;”##0.00″)

Now, concatenate the value of [State] and [sales_form] into a variable

        [line_data] = =”[‘”+[store_name]+”‘,”+[sales_form]+”]”

Now I have to concatenate all [line_data] to get the necessary matrix of data.

I´ll get the process of concatenating lines from

http://scn.sap.com/community/businessobjects-web-intelligence/blog/2014/11/27/concatenating-values-of-a-column

The variable[conc] is shown bellow

[conc] = [line_data] ForEach ([State];[Store name])+”,” + Previous(Self) ForEach ([State];[Store name])

To use the value of [conc] outside the Block, I have to get the last value of it in the table and use the In Report operator .

     [data_prev] = Last([conc]) In Report

[data_prev] has a trailing “,”   so to get rid of it

   [datatable] = =Left([data_prev];Length([data_prev])-1).

The last thing to do is to insert this value into my standard HTML , substituting the text @DATA@ with [datatable].

[gauge_chart] = Replace([gauge_prev];”@DATA@”;[datatable])

And set the value of the blank cell to [gauge_chart].

If you create na Input value over [State] you can see the gauge chart being altered dinamically.

Regards,

Rogerio

Assigned Tags

      37 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      Trick looks nice but when I tried to implement, it does not show any image just blank area comes. I tried to add Example code of guage from Google Charts then it displays Memory, CPU, Network guages. In place of @DATA@ I have given hard coded values but same blank area displays on webi.

      Is there any setting which is to be performed to show webi data in guage.

      Thanks,

      Ankit

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Ankit,

      sorry for this!

      I´m posting the correct code.

      Regards,

      Rogerio

      <html>

        <head>

        </head>

        <body>

          <script type="text/javascript" src="https://www.google.com/jsapi"></script>

          <script type="text/javascript">

            google.load("visualization", "1", {packages:["gauge"]});

             google.setOnLoadCallback(drawChart);

            function drawChart() {

              var data = google.visualization.arrayToDataTable([

                ['Label', 'Value'],

                   @DATA@       ]);

              var options = {

                  max : 4000,

                width: 400, height: 120,

                  greenFrom :1200, greenTo : 3500,

                  yellowFrom :900, yellowTo : 1200,

                 redFrom: 100, redTo: 900,

                minorTicks: 5

              };

              var chart = new google.visualization.Gauge(document.getElementById('chart_div'));

              chart.draw(data, options);

            }

          </script>

          <div id="chart_div" style="width: 400px; height: 120px;"></div>

        </body>

      </html>

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      It gives below error when taken in measure variable.html_webi_error.JPG

      Regards,

      Ankit

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      Any solution of "Invalid identifier 'text' at position 59." error.

      Regards,

      Ankit

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Ankit,

      I tested the new script here and it worked. To create the variable you simple copy the script text to  the definition of the variable, without the = sign and the open and close ".

      Could I send you a wid with it working ?

      Regards,

      Rogerio

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      Thanks a lot for this work-around.

      I am also getting the same error message while testing the code- Invalid identifier "Text" at position 55.

      can you suggest something to fix it?

      -Dhyan

      Author's profile photo Former Member
      Former Member

      Just ignore, i got it correct.

      Thanks again. It is a cool way to show charts...

      -Dhyan

      Author's profile photo Former Member
      Former Member

      Hi Dhyanendra,

      How you corrected it, can you share your way of resolving it.

      Also When I am login in to webi this particular report opens by default. Why is it happening.

      Regards,

      Ankit

      Author's profile photo Former Member
      Former Member

      Hi Ankit,

      Invalid ientifier issue was coming as my webi preference was set as Applet (modify). I changed it to HTML for view and modify both and then created variable.

      Also, you have set blank cell property- Reas as HTML.

      My other observation regarding chart:

      1. Since it is using blank cell, we have very limited options for formatting.

      2. Data values are showing in chart. I do not know if we can chnage that.

      3. Chart size- If came as fixed size. I tried to change the size but was unable.

      Rogerio can help us if he finds any other way to change.

      Let me know you have any further question.

      Thanks

      -Dhyan

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Dhyanendra Kumar,

      Regarding your questions,

      1 and 3, all the formatting of the chart must be done inside the HTML (size, appearance, etc...)

      2 - To use dinamyc values, use the approach of replacing @DATA@ with your array of values. In order to concatenate the values, please refer to Concatenating Values of a column

      Regards,

      Rogerio

      Author's profile photo Former Member
      Former Member

      Thanks Dhyanendra.

      Regards,

      Ankit

      Author's profile photo Former Member
      Former Member

      HI Ankit,

      I need to implement Gauge in my current assignment, I try to follow the above mentioned steps but I couldn't achieve. Unable to create variable or unable to place in Blank cell. As mentioned above in preferences I set HTML as Read and Write mode and also blank cell property I changed to HTML it didn't workout for me.

      It would be a great helpful if you can clearly provide me the steps to implement Gauge in Webi. It is very urgent.

      @Rogerio, can we have Scorecard component in Webi? If yes please share me the details of it.

      Author's profile photo Former Member
      Former Member

      Hi Suresh,

      Firstly follow the steps to create variables as mentioned above.

      You have to look for the format of data binding of the graph you are using.

      Can you tell me the code that you are using.

      If you think you are doing it correctly then try to increase the size of cell.

      Regards,

      Ankit

      Author's profile photo Former Member
      Former Member

      Hi Ankit,

      Thank you for your reply.

      I followed the steps mentioned above, but no use.

      While creating variable it is taking double cote's and equal symbol even if I am not giving. And tried to implement same in direct blank cell, no luck.

      It would be great help if you can send me with details (screenshots) then I will try to implement again.

      Thank you for your help in Advance.

      You can send me to my mail id as well suribabut@gmail.com

      Regards,

      Suresh Babu

      Author's profile photo Former Member
      Former Member

      Hi Suresh,

      Sorry, I cannot create and send screenshots to you since I've followed same steps as given above.

      I am using BI 4.1 sp5

      Quotes and equal sign is not an issue, it could be the issue with codes.

      Thanks,

      Ankit

      Author's profile photo Former Member
      Former Member

      Hi Ankit,

      Thank you for your prompt response on my post.

      I had used the below Java script code which was shared above in this blog

      <html>

        <head>

        </head>

        <body>

          <script type="text/javascript" src="https://www.google.com/jsapi"></script>

          <script type="text/javascript">

            google.load("visualization", "1", {packages:["gauge"]});

             google.setOnLoadCallback(drawChart);

            function drawChart() {

              var data = google.visualization.arrayToDataTable([['Label','Value'],@DATA@]);

              var options = {

                  max : 4000,

                  width: 400, height: 120,

                  greenFrom :1200, greenTo : 3500,

                  yellowFrom :900, yellowTo : 1200,

                  redFrom: 100, redTo: 900,

                  minorTicks: 5

              };

              var chart = new google.visualization.Gauge(document.getElementById('chart_div'));

              chart.draw(data, options);

            }

          </script>

          <div id="chart_div" style="width: 400px; height: 120px;"></div>

        </body>

      </html>

      Even my BOBJ version also 4.1 and SP5.

      Please check the above code and let me know in case anything i missed on it.

      @Rogerio, could you please suggest is anything i missed in that.

      Regards,

      Suresh Babu T.

      Author's profile photo Former Member
      Former Member

      HI Rogerio,

      Could you please share me WID file which you have created for Gauge, based on it i will try to implement in my report.

      Thank you for your help in advance.

      Regards,

      Suresh Babu

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hey Suresh,

      I'm out of office.

      Why don't you send me your wid file so I can check it?

      Regards,

      Rogerio

      Get my e-mail at my profile.

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      I am unable to attach the WID file in this blog and not able to find your email id from your profile. Could you please send me your personal mail id to me suribabut@gmail.com, then i will attach and send you the wid file.

      Thanks in Advance,

      Regards,

      Suresh Babu

      Author's profile photo Gregory BOTTICCHIO
      Gregory BOTTICCHIO

      Hi,

      Very good article! you should add your article to the "Webi 4.x tricks" article if it is not already the case.

      Link is : http://scn.sap.com/docs/DOC-50171

      Regards,

      GB

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi GB,

      thanks for your comment. How can I add the link to Webi tricks ?

      Regards,

      Rogerio

      Author's profile photo Gregory BOTTICCHIO
      Gregory BOTTICCHIO

      Hi Rogerio,

      If you ask the page owner via a post, it should be ok.

      Thanks,

      GB

      Author's profile photo William MARCY
      William MARCY

      Hi Rogerio,

      I'll be pleased to add your entry to the Webi tricks page ! I'll do that ASAP for sure.

      William

      Author's profile photo William MARCY
      William MARCY

      Great Webi trick !

      You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !

      William

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      great tutorial for making these. My only gripe with this, is that this type of chart is generally not a great way to show data, when accurate interpretation is important. Lines, points and bars are generally much easier for humans to understand accurately than radials.

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Walter,

      thanks for your comment.

      You´re right, it´s a very poor visualization. My intent in this post was to show the feasibility of inserting a Javascript Code into a WEBI doc.

      Regards,

      Rogerio

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      Thanks for opening up another dimension to webi report-making!

      I tried making the gauges and was successfully able to do so. However, somehow when I was making a second gauge changing the content from what is shown in the first one, the changes made in the HTML code were reflected in the first gauge (instead of the second one) and the cell which should be containing the new gauge remains empty.

      Have you ever encountered something like this? Or does all the HTML code have to be in only one HTML code?

      Any insights are appreciated!

      Best,

      Sakshi

      Author's profile photo Former Member
      Former Member

      I figured it out, I think it was happening in my case because the charts had the same id, thus it mapped it to the first chart itself when it started loading the chart.

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      Again, an awesome post!

      Have you tried to view these visualizations in the mobile devices? I made some google charts in a webi report for the desktop, I then assigned the Mobile category to it in order to view it on a tablet. Unfortunately, I could only see the HTML code and no visualization on the tablet. 🙁


      Any thoughts on this?


      Best,

      Sakshi

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hello Sakshi,

      Thanks for your comment.

      The first thing that comes to my mind is to check where the id of the DIV´s are different.

      It has to be two different id´s.

      Besides, can you post the codes of the inserted gauges´s (the values of the variables)

      Regards,

      Rogerio

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Hi Sakshi,

      Unfortunately I didn´t try the mobile app. I think it don´t wok because, It´s just a guess, the app renders the document as a native platform, not HTML.

      Regards,

      Rogerio

      Author's profile photo Former Member
      Former Member

      Thanks Rogerio! 🙂 That totally makes sense.

      Do you think there is any way to render these kind of visualizations in the mobile app? I just think what you have introduced here is a great opportunity to make reports and potential dashboards so much better!

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      My only guess here is to get rid of the SAP Mobile app and build you´re own through the Restfull /API.

      Regards,

      Rogherio

      Author's profile photo Former Member
      Former Member

      Hi Rogerio,

      I have tried to display data using google geo map and it is working fine but user want some other navigational points to those data.

      1) Can we put values over the countries instead of doing mouse hover.

      2) User want to take print of this report, currently it cannot be printed because of online map.

      3) How can we use Input Control to navigate data within map.

      Heat Map by Country1.jpg

      Thanks and Regards,

      Ankit

      Author's profile photo Leonel Dinamik
      Leonel Dinamik

      Awesome

      ssss.JPG

      Author's profile photo Former Member
      Former Member
      Blog Post Author

      Great !!!! 🙂

      Author's profile photo Daniel Cantin
      Daniel Cantin

      Hi everydoby, I tried but it doen't work for me to have gauge on my webi report. However I followed all the steps of Rogerio... For me it doesn't show any gauge. I am in BO version 4.1 SP5.

      Could you help? Regards. Floflo