Charts in RPE Documents – the Excel way

Updated on 2014.08.20, 20:00 EEST

RPE does not have native support for drawing charts so you need an external tool to generate the chart. Google Chart Tools: Image Charts have been used successfully in the past but it has been deprecated by Google in  2012 and it is unknown how it will work past 2015.

There are other online providers or applications that can be installed in LAN to provide a REST API for generating image charts. But these solutions may present security/confidentiality risks or additional effort in installing and maintaining them.

An alternative to this is using Microsoft Excel to create charts as Excel can be fully automated through Visual Basic Script which in turn can be invoked from RPE.

This solution assumes Excel is installed which is not a trivial prerequisite. Also you need to be able to run Windows scripts on your machine. To verify your machine can run the example and to better understand how the VB Script work see the last section in this article

The example template and VB Script are available on the RPE devWorks wiki: https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/Rational%20Publishing%20Engine/page/Examples

NOTE: the VB Script is taken from http://automationlab09.wordpress.com/2010/04/29/how-to-draw-a-chart-in-excel-using-vbscript/  and modified it to fit the process. I do not own the copyright and all the credit goes to the original author.

Solution flow

With the prerequisites in place the high level flow of the solution is rather simple:

1. deploy the VB Script with the RPE template
2. add an image element in the RPE template
3. calculate the data labels and values
4. for the image content use a script that calls the VB Script template with the necessary arguments, one of them being the path where the image is to be generated
5. use the path from 3 as the source of the image

The VB Script generates Pie Charts but you can create new scripts to generate other formats too.

NOTE: the JavaScript assumes that the VB script is in d:\\tmp. You need to manually modify the path to where your script is located.

NOTE: the JavaScript script I use combines steps 3 and 4 in a single place as the data is static. A side effect of this is that an image is generated every time you modify and save the image JavaScript.

NOTE: the only information you will receive in RPE when you run the VB Script is the exit code. You cannot see error details or log messages generated by the command.

excelPieChart.vbs

The script is designed to take as input 4 arguments. All arguments are strings and are mandatory

1. the title of the chart
2. a “|” separated string with the data labels. Ex: “High|Medium|Low”
3. a “|” separated string with the data value. Ex: “20|50|30”
4. the path of the exported image. The path must have the png extension and should not exist otherwise the script will prompt the user to overwrite the file

To verify that the script can produce chart images successfully on your machine you can run the following command from a Windows command prompt. If everything is in place you should see the Excel window briefly showing on screen and more importantly, the png image in the specified path.

cscript "d:\tmp\excelPieChart.vbs" "Test chart" "High|Medium|Low" "20|50|30" "d:\tmp\testChart.png"

Author: Dragos Cojocari

Architect for Rational Publishing Engine View all posts by Dragos Cojocari