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
- SAP Managed Tags:
37 Comments
