Powerbuilder – Internet Explorer OLE to display Flash charts

Posted on Thursday, November 18th, 2010 at 8:20 pm in

Powerbuilder datawindows have had charting capabilities for quite a long time and they do give reasonable functionality in an easy to implement format. Version 11 added some enhancements and 12 now makes it straightforward to incorporate third party tools for this. For quite some time, however, it has been possible to embed an Internet Explorer control in a window and take advantage of web based tools. There is a Sybase Tech Note on embedding an IE control you can look at here. I wish Firefox had the same capability but it apparently went away after version 1.7 or so.

What I was looking for was a way to ‘prettify’ my data since managers all over are now entranced with the ‘dashboard’ concept. Sure, I can do extensive work with a datawindow (as in Extreme Datawindows) but for my purposes this is overkill. Doing some quick searching on the web I found that a number of open source charting tools are available and I gravitated to the Adobe Flash based ones. I settled on Open Flash Chart available at here.

Prerequisites

Internet Explorer (v6 or better) with the Flash Plugin (http://get.adobe.com/flashplayer/)
Open Source Adobe Flex SDK (http://www.adobe.com/cfusion/entitlement/index.cfm?e=flex4sdk)
Open Flash Chart (http://sourceforge.net/projects/openflashchart/files/)
Some sort of webserver. I used XAMPP which I run from a thumb drive.

Make sure you have your setup running correctly prior to the Powerbuilder work. Open Flash Chart has a series of tutorials you can used to get your feet wet and validate that all is working correctly. One tidbit if you are not too familiar with webservers, Apache sets the root directory (called the “DocumentRoot”) in the httpd.conf file in the conf folder.

Powerbuilder

The basic concept here is to embed an IE control in a window which I will then point to an HTML file on my webserver which then returns the graph to the window control. The actual data is obtained from a JSON file which is a simple text file containing formatting commands and data values. In this example I am using a static JSON file. At some point I may write a JSON file generator in Powerbuilder but that is for later.

First insert an OLE control in a window of type ‘Microsoft Web Browser’. See example:

Put a button on the window to load the report html page in the control. In the clicked event put the following:

ole_2.object.navigate("http://localhost/chart.html?ofc=data.json")

Where ‘chart.html’ is your base web page and ‘data.json’ is the JSON file which defines the report and the data used to make it.

Chart HTML

<html>
<head>
</head>
<body>









</body> </html>

Chart Data JSON File

{
  "elements": [
    {
      "type": "candle",
      "colour": "#9933CC",
      "values": [
        {
          "high": "16.00",
          "top": "12.00",
          "bottom": "10.00",
          "low": "8.00"
        },
        {
          "high": "17.92",
          "top": "12.96",
          "bottom": "12.16",
          "low": "9.92"
        },
        {
          "high": "19.37",
          "top": "13.68",
          "bottom": "14.29",
          "low": "11.37"
        },
        {
          "high": "19.99",
          "top": "13.99",
          "bottom": "15.85",
          "low": "11.99"
        },
        {
          "high": "19.64",
          "top": "13.82",
          "bottom": "16.47",
          "low": "11.64"
        },
        {
          "high": "18.39",
          "top": "13.20",
          "bottom": "16.00",
          "low": "10.39"
        },
        {
          "high": "16.56",
          "top": "12.28",
          "bottom": "14.54",
          "low": "8.56"
        },
        {
          "high": "14.60",
          "top": "11.30",
          "bottom": "12.47",
          "low": "6.60"
        },
        {
          "high": "12.97",
          "top": "10.28",
          "bottom": "10.28",
          "low": "4.97"
        },
        {
          "high": "12.09",
          "top": "10.04",
          "bottom": "8.51",
          "low": "4.09"
        },
        {
          "high": "12.16",
          "top": "10.08",
          "bottom": "7.60",
          "low": "4.16"
        },
        {
          "high": "13.18",
          "top": "10.59",
          "bottom": "7.76",
          "low": "5.18"
        },
        {
          "high": "14.88",
          "top": "11.44",
          "bottom": "8.96",
          "low": "6.88"
        },
        {
          "high": "16.86",
          "top": "12.43",
          "bottom": "10.91",
          "low": "8.86"
        },
        {
          "high": "18.63",
          "top": "13.12",
          "bottom": "13.12",
          "low": "10.63"
        },
        {
          "high": "19.75",
          "top": "13.88",
          "bottom": "15.06",
          "low": "11.75"
        },
        {
          "high": "19.96",
          "top": "13.98",
          "bottom": "16.25",
          "low": "11.96"
        },
        {
          "high": "19.19",
          "top": "13.60",
          "bottom": "16.40",
          "low": "11.19"
        },
        {
          "high": "17.65",
          "top": "12.82",
          "bottom": "15.47",
          "low": "9.65"
        },
        {
          "high": "15.70",
          "top": "11.85",
          "bottom": "13.69",
          "low": "7.70"
        },
        {
          "high": "13.82",
          "top": "10.91",
          "bottom": "11.50",
          "low": "5.82"
        },
        {
          "high": "12.48",
          "top": "10.24",
          "bottom": "9.43",
          "low": "4.48"
        },
        {
          "high": "12.00",
          "top": "10.00",
          "bottom": "7.99",
          "low": "4.00"
        },
        {
          "high": "12.50",
          "top": "10.25",
          "bottom": "7.53",
          "low": "4.50"
        },
        {
          "high": "13.85",
          "top": "10.93",
          "bottom": "8.17",
          "low": "5.85"
        },
        {
          "high": "15.73",
          "top": "11.87",
          "bottom": "9.74",
          "low": "7.73"
        },
        {
          "high": "17.68",
          "top": "12.84",
          "bottom": "11.87",
          "low": "9.68"
        },
        {
          "high": "19.22",
          "top": "13.61",
          "bottom": "14.03",
          "low": "11.22"
        },
        {
          "high": "19.96",
          "top": "13.98",
          "bottom": "15.69",
          "low": "11.96"
        },
        {
          "high": "19.74",
          "top": "13.87",
          "bottom": "16.45",
          "low": "11.74"
        },
        {
          "high": "18.60",
          "top": "13.30",
          "bottom": "16.12",
          "low": "10.60"
        }
      ],
      "tip": "#x_label#
LBSMax: #high#
LBSOut: #open#
LBSIn: #close#
LBSMin: #low#"
    }
  ],
  "title": {
    "text": "March Branch Inventory"
  },
  "y_axis": {
    "min": 0,
    "max": 40,
    "steps": 10
  }
}

Example

Report Formats

You will have to experiment with the various report formats found at Open Flash Chart to get something which will work for you. Once you have a format you like it’s just a matter of creating the methods which will take your data and insert it into the JSON and then save the file to the location specified in the ‘Navigate’ command.

Using Common Browser Commands

The following information I came across on DotNetIdeas.

You may use ExecWB( cmdID As OLECMDID, cmdexecopt As OLECMDEXECOPT, [pvaIn As Variant,] [pvaOut As Variant]) to perform some common actions in Web Browser Control. For example, ole_2.object.ExecWB(12, 2) is to copy the highlighted text. Below is the list of command arguments:

typedef enum
{
OLECMDID_OPEN = 1,
OLECMDID_NEW = 2,
OLECMDID_SAVE = 3,
OLECMDID_SAVEAS = 4,
OLECMDID_SAVECOPYAS = 5,
OLECMDID_PRINT = 6,
OLECMDID_PRINTPREVIEW = 7,
OLECMDID_PAGESETUP = 8,
OLECMDID_SPELL = 9,
OLECMDID_PROPERTIES = 10,
OLECMDID_CUT = 11,
OLECMDID_COPY = 12,
OLECMDID_PASTE = 13,
OLECMDID_PASTESPECIAL = 14,
OLECMDID_UNDO = 15,
OLECMDID_REDO = 16,
OLECMDID_SELECTALL = 17,
OLECMDID_CLEARSELECTION = 18,
OLECMDID_ZOOM = 19,
OLECMDID_GETZOOMRANGE = 20,
OLECMDID_UPDATECOMMANDS = 21,
OLECMDID_REFRESH = 22,
OLECMDID_STOP = 23,
OLECMDID_HIDETOOLBARS = 24,
OLECMDID_SETPROGRESSMAX = 25,
OLECMDID_SETPROGRESSPOS = 26,
OLECMDID_SETPROGRESSTEXT = 27,
OLECMDID_SETTITLE = 28,
OLECMDID_SETDOWNLOADSTATE = 29,
OLECMDID_STOPDOWNLOAD = 30,
OLECMDID_ONTOOLBARACTIVATED = 31,
OLECMDID_FIND = 32,
OLECMDID_DELETE = 33,
OLECMDID_HTTPEQUIV = 34,
OLECMDID_HTTPEQUIV_DONE = 35,
OLECMDID_ENABLE_INTERACTION = 36,
OLECMDID_ONUNLOAD = 37,
OLECMDID_PROPERTYBAG2 = 38,
OLECMDID_PREREFRESH = 39,
OLECMDID_SHOWSCRIPTERROR = 40,
OLECMDID_SHOWMESSAGE = 41,
OLECMDID_SHOWFIND = 42,
OLECMDID_SHOWPAGESETUP = 43,
OLECMDID_SHOWPRINT = 44,
OLECMDID_CLOSE = 45,
OLECMDID_ALLOWUILESSSAVEAS = 46,
OLECMDID_DONTDOWNLOADCSS = 47,
OLECMDID_UPDATEPAGESTATUS = 48,
OLECMDID_PRINT2 = 49,
OLECMDID_PRINTPREVIEW2 = 50,
OLECMDID_SETPRINTTEMPLATE = 51,
OLECMDID_GETPRINTTEMPLATE = 52,
OLECMDID_PAGEACTIONBLOCKED = 55,
OLECMDID_PAGEACTIONUIQUERY = 56,
OLECMDID_FOCUSVIEWCONTROLS = 57,
OLECMDID_FOCUSVIEWCONTROLSQUERY = 58,
OLECMDID_SHOWPAGEACTIONMENU = 59,
OLECMDID_ADDTRAVELENTRY = 60,
OLECMDID_UPDATETRAVELENTRY = 61,
OLECMDID_UPDATEBACKFORWARDSTATE = 62,
OLECMDID_OPTICAL_ZOOM = 63,
OLECMDID_OPTICAL_GETZOOMRANGE = 64,
OLECMDID_WINDOWSTATECHANGED = 65,
OLECMDID_ACTIVEXINSTALLSCOPE = 66
} OLECMDID;

typedef enum
{
OLECMDEXECOPT_DODEFAULT = 0,
OLECMDEXECOPT_PROMPTUSER = 1,
OLECMDEXECOPT_DONTPROMPTUSER = 2,
OLECMDEXECOPT_SHOWHELP = 3
} OLECMDEXECOPT;

Although it says OLECMDEXECOPT_DONTPROMPTUSER = 2 doesn’t show the dialog, but when you run execwb(6,2) to print a web page, it will always show the dialog. That is be design. Check Microsoft Knowledge Base for more info.

You might also be interested in

Add your comment

Leave a Reply

Top