Linking an OWC 11 Pivot Table to a Chart in a Windows Forms Project

Introduction

The Microsoft Office Web Components offer some nifty ActiveX controls which can be used to explore data. Two such controls are the PivotTable and ChartSpace controls. In fact, those controls can be linked to each other so that they both provide different views on a single query for that data source.

This is fairly easy to do for a web application. You just embed the controls in the page, wire them up with some Javascript, and they just work. However, linking the two controls in a Windows Forms project is a little less clear. This article aims to show you how it can be done.

The code samples are in F# for brevity, but the same concepts should be applicable to any .NET language.

Generating the Wrappers for the OWC Controls

First of all, we need to find the assembly containing the ActiveX controls we want. It usually lives at %PROGRAMFILES%\Common Files\Microsoft Shared\Web Components\11\OWC11.dll, although there may be some differences on 64-bit Windows operating systems.

Normally, in Visual Studio, you’d add a reference to that dll and the IDE would generate managed wrappers for the ActiveX controls that you could simply drag and drop on your form. We’re going to take the hardcore approach here, and use the AxImp tool which ships with the Windows SDK. It usually lives at %PROGRAMFILES%\Microsoft SDKs\Windows\v6.0A\bin\AxImp.exe.

The command used to generate the wrappers is quite simple. I’m going to drop the wrapper dll in the root of my C drive, which may not work if you don’t have permission. Open up a command prompt and type:

  1. "%PROGRAMFILES%\Microsoft SDKs\Windows\v6.0A\bin\AxImp.exe" "%PROGRAMFILES%\Common Files\Microsoft Shared\Web Components\11\OWC11.dll" /out:C:\OWC11.dll

You’ll hopefully get the following output.

  1. Generated Assembly: C:\OWC11.dll

Using the Wrappers

Now that we have the wrappers, let’s create a new F# script and add the neccessary references for what we want to do.

  1. #r "MSDATASRC"
  2. #r "C:\\OWC11.dll"
  3.  
  4. open AxMicrosoft.Office.Interop.Owc11
  5. open msdatasrc

Those lines add the required references to my program and open up the required namespaces so I’m ready to use the classes.

The types we’re interested in are AxPivotTable and AxChartSpace. These are wrappers for the ActiveX PivotTable and ChartSpace controls. We can treat the wrappers like any other Windows Forms control.

The important properties for linking the two controls are:

  • AxPivotTable.ConnectionString
  • AxPivotTable.DataMember
  • AxChartSpace.DataSource

ConnectionString and DataMember simply point the PivotTable to a data source, while AxChartSpace’s DataSource property should be set to the PivotTable in order to link the two controls.

Problems

Let’s start putting together some real code.

  1. #r "MSDATASRC"
  2. #r "C:\\OWC11.dll"
  3.  
  4. open AxMicrosoft.Office.Interop.Owc11
  5. open msdatasrc
  6. open System.Windows.Forms
  7.  
  8. let run (cs:string) (dm:string) =
  9.     use f = new Form(Width = 1024, Height = 800, Text = "Report")
  10.     use spc = new SplitContainer(Dock = DockStyle.Fill)
  11.     use p = new AxPivotTable(Dock = DockStyle.Fill)
  12.     use c = new AxChartSpace(Dock = DockStyle.Fill)
  13.     spc.Panel1.Controls.Add(p)
  14.     spc.Panel2.Controls.Add(c)
  15.     f.Controls.Add(spc)
  16.     f.Load.Add(fun e -> p.ConnectionString <- cs
  17.                         p.DataMember <- dm
  18.                         c.DataSource <- null // ???
  19.                         p.AutoFit <- false
  20.                         c.AllowPropertyToolbox <- true
  21.                         spc.SplitterDistance <- 500)
  22.     f.ShowDialog()
  23.  
  24. run ("Driver={SQL Server Native Client 10.0};" +
  25.      "Server=.\\SQLEXPRESS;" +
  26.      "Database=Finance;" +
  27.      "Trusted_Connection=yes;") "TransactionsView"

That’s OK, but what do we set c.DataSource to in order to link it to the pivot table? My first thought is to do…

  1. c.DataSource <- p

But this results in a compile time error.

  1. error FS0001: The type 'AxPivotTable' is not compatible with the type 'DataSource'

Hmm. We appear to be stuck. We need to set the chart’s DataSource to the pivot table, but for some reason we’re unable to.

The Solution

The reason why it doesn’t work is fairly clear if you think about it. I know personally for a fact that we’re able to link these two controls as I’ve done it before in ASP.NET projects. However, in that instance we were dealing with the actual ActiveX controls. Because we’re now embedding those controls in a Windows Forms application, we’re now dealing with wrappers. The wrapper class AxPivotControl must be wrapping an instance of the ActiveX control we want to assign to the chart’s DataSource property.

The wrapped instance doesn’t appear to be accessible through the AxPivotTable type’s public members, so let’s open up the assembly in Reflector.

Reflector and AxPivotTable

Reflector showing the disassembly of AxPivotTable

There we can see a few fields. The most promising one looks like ‘ocx’ – it’s an instance of IPivotControl. We can confirm whether or not this is the correct property by checking the disassembled ConnectionString property.

  1. [DispId(0x1796), DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
  2. public virtual string ConnectionString
  3. {
  4.     get
  5.     {
  6.         if (this.ocx == null)
  7.         {
  8.             throw new AxHost.InvalidActiveXStateException("ConnectionString", AxHost.ActiveXInvokeKind.PropertyGet);
  9.         }
  10.         return this.ocx.ConnectionString;
  11.     }
  12.     set
  13.     {
  14.         if (this.ocx == null)
  15.         {
  16.             throw new AxHost.InvalidActiveXStateException("ConnectionString", AxHost.ActiveXInvokeKind.PropertySet);
  17.         }
  18.         this.ocx.ConnectionString = value;
  19.     }
  20. }

Here we can see that the ConnectionString property on AxPivotTable is simply directing calls to the ocx field. This pretty much confirms that the ocx field contains the ActiveX PivotTable we want to assign to the AxChartSpace’s DataSource property.

Let’s add some more code to our F# script.

  1. #r "MSDATASRC"
  2. #r "C:\\OWC11.dll"
  3.  
  4. open AxMicrosoft.Office.Interop.Owc11
  5. open msdatasrc
  6. open System.Windows.Forms
  7. open System.Reflection
  8.  
  9. type AxPivotTable with
  10.     member this.Ocx =
  11.         let ocxField = this.GetType().GetField("ocx",
  12.                         BindingFlags.NonPublic ||| BindingFlags.Instance)
  13.         ocxField.GetValue(this) :?> DataSource
  14.        
  15. let run (cs:string) (dm:string) =
  16.     use f = new Form(Width = 1024, Height = 800, Text = "Report")
  17.     use spc = new SplitContainer(Dock = DockStyle.Fill)
  18.     use p = new AxPivotTable(Dock = DockStyle.Fill)
  19.     use c = new AxChartSpace(Dock = DockStyle.Fill)
  20.     spc.Panel1.Controls.Add(p)
  21.     spc.Panel2.Controls.Add(c)
  22.     f.Controls.Add(spc)
  23.     f.Load.Add(fun e -> p.ConnectionString <- cs
  24.                         p.DataMember <- dm
  25.                         c.DataSource <- p.Ocx
  26.                         p.AutoFit <- false
  27.                         c.AllowPropertyToolbox <- true
  28.                         spc.SplitterDistance <- 500)
  29.     f.ShowDialog()
  30.    
  31. run ("Driver={SQL Server Native Client 10.0};" +
  32.      "Server=.\\SQLEXPRESS;" +
  33.      "Database=Finance;" +
  34.      "Trusted_Connection=yes;") "TransactionsView"

There, I’ve added a type augmentation on the type AxPivotTable which allows us to access the private field ocx through a new Ocx property. We now simply assign AxPivotTable.Ocx to AxChartSpace.DataSource, and the two controls are linked!

Conclusion

With a linked PivotTable and ChartSpace control, you can explore data in what I think is a rather nifty way. What would be even better than what we’ve got above would be the ability to save reports and load them at a later date. Luckily, this can be acheived quite easily using the XMLData properties of both AxPivotTable and AxChartSpace.

The final script featuring this functionality is pasted below.

  1. #r "MSDATASRC"
  2. #r "C:\\OWC11.dll"
  3.  
  4. open System
  5. open System.IO
  6. open System.Reflection
  7. open System.Windows.Forms
  8. open AxMicrosoft.Office.Interop.Owc11
  9. open msdatasrc
  10.    
  11. type AxPivotTable with
  12.     member this.Ocx =
  13.         let ocxField = this.GetType().GetField("ocx",
  14.                         BindingFlags.NonPublic ||| BindingFlags.Instance)
  15.         ocxField.GetValue(this) :?> DataSource
  16.  
  17. type data(pdata : string, cdata : string) =
  18.     static let split = "<!– Pivot/Chart Data Split –>"
  19.     member this.PivotData = pdata
  20.     member this.ChartData = cdata
  21.     member this.Save (file:string) =
  22.         use s = File.Create(file)
  23.         use w = new StreamWriter(s)
  24.         w.WriteLine(pdata + split + cdata)
  25.     static member Load (file : string) =
  26.         use r = new StreamReader(file)
  27.         let tokens = r.ReadToEnd()
  28.                       .Split([| split |],
  29.                              StringSplitOptions.RemoveEmptyEntries)
  30.         data(tokens.[0], tokens.[1])
  31.  
  32. let loadForm (cs:string) (dm:string) (p:AxPivotTable) (c:AxChartSpace) =
  33.     p.ConnectionString <- cs
  34.     p.DataMember <- dm
  35.     c.DataSource <- p.Ocx
  36.     p.AutoFit <- false
  37.     c.AllowPropertyToolbox <- true
  38.  
  39. let saveReport (p:AxPivotTable) (c:AxChartSpace) =
  40.     use sfd = new SaveFileDialog(Filter = "Report Files (*.pvt)|*.pvt")
  41.     if sfd.ShowDialog() = DialogResult.OK then
  42.         let d = data(p.XMLData, c.XMLData)
  43.         d.Save(sfd.FileName)
  44.        
  45. let loadReport (p:AxPivotTable) (c:AxChartSpace) =
  46.     use ofd = new OpenFileDialog(Filter = "Report Files (*.pvt)|*.pvt")
  47.     if ofd.ShowDialog() = DialogResult.OK then
  48.         let d = data.Load(ofd.FileName)
  49.         p.XMLData <- d.PivotData
  50.         c.XMLData <- d.ChartData
  51.         c.DataSource <- p.Ocx
  52.         p.Refresh()
  53.         c.Refresh()
  54.  
  55. let run (cs:string) (dm:string) =
  56.     use f = new Form(Text = "Reporting Tool",
  57.                      Width = 1200,
  58.                      Height = 768)
  59.     use tsc = new ToolStripContainer(Dock = DockStyle.Fill)
  60.     use spc = new SplitContainer(Dock = DockStyle.Fill,
  61.                                  SplitterDistance = 520)
  62.     use c = new AxChartSpace(Dock = DockStyle.Fill)
  63.     use p = new AxPivotTable(Dock = DockStyle.Fill)
  64.     use m = new MenuStrip()
  65.     use mnuFile = new ToolStripMenuItem(Text = "&File")
  66.     use mnuSave = new ToolStripMenuItem(Text = "&Save As…")
  67.     use mnuLoad = new ToolStripMenuItem(Text = "&Load…")
  68.     mnuFile.DropDownItems.Add(mnuLoad) |> ignore
  69.     mnuFile.DropDownItems.Add(mnuSave) |> ignore
  70.     m.Items.Add(mnuFile) |> ignore
  71.     spc.Panel1.Controls.Add(p)
  72.     spc.Panel2.Controls.Add(c)
  73.     tsc.TopToolStripPanel.Controls.Add(m)
  74.     tsc.ContentPanel.Controls.Add(spc)
  75.     f.Controls.Add(tsc)
  76.     f.Load.Add(fun e -> loadForm cs dm p c)
  77.     mnuSave.Click.Add(fun e -> saveReport p c)
  78.     mnuLoad.Click.Add(fun e -> loadReport p c)
  79.     f.ShowDialog()
  80.  
  81. run ("Driver={SQL Server Native Client 10.0};" +
  82.      "Server=.\\SQLEXPRESS;" +
  83.      "Database=Finance;" +
  84.      "Trusted_Connection=yes;") "TransactionsView"

That little script has proved to be very useful to me, especially when used to explore cubes in Team System’s data warehouse. Below is a sample of what this simple script can be used to do.

PivotTable/ChartSpace Report Sample

A Sample PivotTable/ChartSpace Report

Linking the two controls proved to be a little trickier than at first thought, but with a bit of perseverance and some use of the fantastic Reflector we got there.

Share and Enjoy:
  • Print
  • Digg
  • StumbleUpon
  • del.icio.us
  • Facebook
  • Yahoo! Buzz
  • Twitter
  • Google Bookmarks
  • email
  • LinkedIn
  • Technorati

Leave a Reply

Your email address will not be published. Required fields are marked *