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 :
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
And the result of it is :
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
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
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
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>
Hi Rogerio,
It gives below error when taken in measure variable.
Regards,
Ankit
Hi Rogerio,
Any solution of "Invalid identifier 'text' at position 59." error.
Regards,
Ankit
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
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
Just ignore, i got it correct.
Thanks again. It is a cool way to show charts...
-Dhyan
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
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
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
Thanks Dhyanendra.
Regards,
Ankit
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.
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
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
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
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.
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
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.
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
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
Hi GB,
thanks for your comment. How can I add the link to Webi tricks ?
Regards,
Rogerio
Hi Rogerio,
If you ask the page owner via a post, it should be ok.
Thanks,
GB
Hi Rogerio,
I'll be pleased to add your entry to the Webi tricks page ! I'll do that ASAP for sure.
William
Great Webi trick !
You've been added to Webi 4.x tricks : summary for a better visibility. Keep posting !
William
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.
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
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
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.
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
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
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
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!
My only guess here is to get rid of the SAP Mobile app and build you´re own through the Restfull /API.
Regards,
Rogherio
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.
Thanks and Regards,
Ankit
Awesome
Great !!!! π
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