Microsoft Office Web Components

Recently I came across the software tool Microsoft Office Web Components. It is not new, but I don't see many references to it and I believe it should/could be used a lot more. Especially for management reporting.
Basically it allows spreadsheets, pivot tables and graphs to be placed in HTML pages.
It is the last element - Graphs; that I will consider here.

I felt that using HTML for management reports lacked power without a graphic element.
Yes, it has been possible to prepare a graph; say in Excel; and save it as an image file to be placed in a HTML page. But obviously, this takes time and requires co-ordination. With Microsoft Office Web Components that all changes.

With Microsoft Office Web Component (OWC) and using your prefered script language - VBScript, JavaScript, etc. web pages can be built to provide management reports that automatically draw data from a background database and at the same time present a graph using that data. Thus, automating the process and providing the power of graphs to management when reviewing financial information. 

As an example,with Acceptum Business Software it is possible to prepare and hold a HTML page that provides the outline of a management report. In the HTML are SQL Select statements and Table Look Up commands that are evaluated prior to the HTML being sent to Internet Explorer/browser.  With OWC and VBScript, graphs are placed in the management report making it more vibrant. Having got the data into a temporary HTML file, it can now be saved to a central folder for all managers to review. This way the database is only queried once and provides a "frozen" image for period end reporting to which all can refer. The following week/month the same HTML template is used to produce the next installment.

With other accounting/ERP systems using a COM interface or Web RecordSet interface,  would provide a similar method of extracting data.

Now lets take a look at an example web page (brackets changed to get it to display).

[BODY topMargin=1]
[script language="VBScript"]
Dim oChart1, categories,  bud_get, act_ual, var_iance, c1
Sub Window_Onload
' Clear the contents of the chart workspace.
' This removes any old charts that may already exist
' and leaves the chart workspace
' completely empty. One chart object is then added.
 ChartSpace1.Clear
 ChartSpace1.Charts.Add
 Set c1 = ChartSpace1.Constants

' Add a series to the chart.
 ChartSpace1.Charts(0).SeriesCollection.Add
 ChartSpace1.Charts(0).SeriesCollection.Add
 ChartSpace1.Charts(0).SeriesCollection.Add
' Set the series caption (the text that appears in the legend).
 ChartSpace1.Charts(0).SeriesCollection(0).Caption = "Revenue Year to Date"
 categories = "Actual"

' Set the categories for the first series (this collection is zero-based)
 ChartSpace1.Charts(0).SeriesCollection(0).SetData c1.chDimCategories, c1.chDataLiteral, categories

' Acceptum Business Software table lookup to get the data, then convert to a number type - Long
 act_ual = CLng("&$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A005' ", "")$&")
 ChartSpace1.Charts(0).SeriesCollection(0).Caption = categories
 ChartSpace1.Charts(0).SeriesCollection(0).SetData c1.chDimCategories, c1.chDataLiteral, categories
 ChartSpace1.Charts(0).SeriesCollection(0).SetData c1.chDimValues, c1.chDataLiteral, act_ual

 categories = "Budget"
 bud_get = CLng("&$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A001' ", "")$&")
 ChartSpace1.Charts(0).SeriesCollection(1).Caption = categories
 ChartSpace1.Charts(0).SeriesCollection(1).SetData c1.chDimCategories, c1.chDataLiteral, categories
 ChartSpace1.Charts(0).SeriesCollection(1).SetData c1.chDimValues, c1.chDataLiteral, bud_get

 categories = "Variance"
 var_iance = CLng("&$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A009' ", "")$&")
 ChartSpace1.Charts(0).SeriesCollection(2).Caption = categories
 ChartSpace1.Charts(0).SeriesCollection(2).SetData c1.chDimCategories, c1.chDataLiteral, categories
 ChartSpace1.Charts(0).SeriesCollection(2).SetData c1.chDimValues, c1.chDataLiteral, var_iance

 ' Format the chart to have a legend
 ChartSpace1.Charts(0).HasLegend = True
 ChartSpace1.Charts(0).HasTitle = True
 ChartSpace1.Charts(0).Legend.Position = c1.chLegendPositionTop
 ChartSpace1.Charts(0).Title.Caption = "Revenue Year to Date"
 'ChartSpace1.Charts(0).Axes(c1.chAxisPositionLeft).MajorUnit = 1000
  set c1 = nothing

End Sub
'**************************************************
Sub Window_OnUnload
 'Executed when window is closed.
 Set oChart1 = nothing
 Set chartspace1 = nothing
End Sub
'*************************************************
[/script]

[H2]Year To Date upto Period 6[/H2]
Year to Date Revenue is £ &$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A005' ", "")$& [br /]
compared to Budget of £ &$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A001' ", "")$& [br /]
giving a Variance of £ &$TABLELOOKUP("results", "SCORECARD", "company = m.gccomp and id = 'A009' ", "")$& .[br /]

[object id='ChartSpace1' classid=CLSID:0002E546-0000-0000-C000-000000000046 style="width:300;height:300"][/object]
[br /]
[br /]
[H3]Explanation - Revenue Amount[/H3]
&$TABLELOOKUP("notes", "SCORECARD", "company = m.gccomp and id = 'A005' ", "")$&
[br /]
[H3]Explanation - Budget Amount[/H3]
&$TABLELOOKUP("notes", "SCORECARD", "company = m.gccomp and id = 'A001' ", "")$&
[br /]
[H3]Explanation - Variance Amount[/H3]
&$TABLELOOKUP("notes", "SCORECARD", "company = m.gccomp and id = 'A009' ", "")$&
[br /]
[br /]
R. Bean - Accountant[br /]
XYZ Ltd. [br /][/BODY]   

Here is an image of the Html page. www.acceptum.co.uk\graphics\managementreport.bmp

It is a basic report, a full management report would of course be more complex. But it gives the general idea of what can be achieved. The classid=CLSID:0002E546-0000-0000-C000-000000000046 clause of the object statement may need to vary depending on the installed version of MS OWC.

It also appears that you do not need to have MS Office installed for this to work as OWC is standalone.