Skip to Content

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

To report this post you need to login first.

37 Comments

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

  1. Ankit Gupta

    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

    (0) 
    1. Rogerio Plank 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>

      (0) 
        1. Rogerio Plank 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

          (0) 
          1. Dhyanendra Kumar

            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

            (0) 
              1. Ankit Gupta

                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

                (0) 
                1. Dhyanendra Kumar

                  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

                  (0) 
                    1. Suresh Thummalapenta

                      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.

                      (0) 
                      1. Ankit Gupta

                        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

                        (0) 
                        1. Suresh Thummalapenta

                          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

                          (0) 
                          1. Ankit Gupta

                            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

                            (0) 
                            1. Suresh Thummalapenta

                              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.

                              (0) 
          2. Suresh Thummalapenta

            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

            (0) 
            1. Rogerio Plank 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.

              (0) 
              1. Suresh Thummalapenta

                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

                (0) 
  2. Walter Hoekstra

    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.

    (0) 
    1. Rogerio Plank 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

      (0) 
  3. Sakshi Dhakad

    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

    (0) 
    1. Sakshi Dhakad

      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.

      (0) 
  4. Sakshi Dhakad

    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

    (0) 
    1. Rogerio Plank 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

      (0) 
    2. Rogerio Plank 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

      (0) 
      1. Sakshi Dhakad

        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!

        (0) 
  5. Ankit Gupta

    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

    (0) 
  6. 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

    (0) 

Leave a Reply