Databind ChartFX to OLAP Datasource

christian.wade — Wed, 08 Nov 2006 17:51:00 GMT

I’ve just gone through an informal and limited evaluation process for a charting tool for a BI dashboard. It included some of the RS extension products, ProClarity and ChartFX. Of course I would have been keen to use ProClarity, but in this case the cost was a little high and, to be honest, it would have been a sledgehammer to crack a nut.

ChartFX is the charting engine used by ProClarity. It looks and feels exactly the same as ProClarity charts. In theory, everything you can do with ProClarity charts, you can do with ChartFX – you just have to code it yourself (which is a lot easier than it sounds).

Anyway, when evaluating ChartFX, I initially looked at the OLAP extension to it. Seemed to work great at first and I was very impressed with it. However, when I got to doing anything a little out of the ordinary like multiple Y axes or specifying X axes labeling, the code simply didn’t work! For example, the code at the bottom of the following snippet has no effect.

AdoMultiDimensionalData ADOMD1 = new AdoMultiDimensionalData();

string connstr = "Provider=msolap; Data Source=localhost;Initial Catalog=Adventure Works;";

string ds =

@"SELECT

[Date].[Calendar].[Calendar Year].Members ON 0,

{

[Measures].[Internet Sales Amount],

[Measures].[Internet Freight Cost]

} ON 1

FROM

[Adventure Works];";

ADOMD1.Connect(connstr, ds);

Olap1.DataSource = ADOMD1;

// This code hides the Analysis Bar

Olap1.AnalysisBar = AnalysisBar.Menu;

ToolBar tb = (ToolBar)Olap1.AnalysisObject;

tb.Visible = false;

Chart1.Gallery = Gallery.Bar;

// Code that doesn't work!

AxisY addlAxisY = new AxisY();

addlAxisY.Visible = true;

addlAxisY.Position = AxisPosition.Far;

Chart1.AxesY.Add(addlAxisY);

Chart1.Series[0].AxisY = addlAxisY;

The reason for the code not working is that the OLAP extension uses databinding, which overrides code such as this. Here’s what the chart looks like.

The way I got round this problem was to not use the OLAP extension at all. Instead, I am using the ChartFX API to iterate an ADOMD.NET CellSet and set the chart data. Here is the code; it works fine for MDX with 2 axes. You could of course take it a little further to display crossjoined sets, etc. Here is the code.

using (AdomdConnection conn = new AdomdConnection("Data Source=localhost;Initial Catalog=Adventure Works;"))

{

conn.Open();

//Create a command, using this connection

AdomdCommand cmd = conn.CreateCommand();

cmd.CommandText =

@"SELECT

[Date].[Calendar].[Calendar Year].Members ON 0,

{

[Measures].[Internet Sales Amount],

[Measures].[Internet Freight Cost]

} ON 1

FROM

[Adventure Works];";

//Execute the query, returning a cellset

CellSet cs = cmd.ExecuteCellSet();

//Get back rows representing each series

TupleCollection tuplesOnRows = cs.Axes[1].Set.Tuples;

//Set the number of series

Chart1.Data.Series = tuplesOnRows.Count;

//Get back columns representing each x axis category

TupleCollection tuplesOnColumns = cs.Axes[0].Set.Tuples;

Chart1.Data.Points = tuplesOnColumns.Count;

//Set the data values and series names

for (int row = 0; row < tuplesOnRows.Count; row++)

{

Chart1.Series[row].Text = tuplesOnRows[row].Members[0].Caption;

for (int col = 0; col < tuplesOnColumns.Count; col++)

{

Chart1.Data[row, col] = Convert.ToDouble(cs.Cells[col, row].Value);

}

//Set the x axis category names

for (int i = 0; i < tuplesOnColumns.Count; i++)

{

Chart1.AxisX.Labels[ i ] = tuplesOnColumns[ i ].Members[0].Caption;

}

conn.Close();

} // using connection

//Set chart type

Chart1.Gallery = Gallery.Bar;

//Create secondary y axes series and its appearance properties

AxisY addlAxisY = new AxisY();

addlAxisY.Visible = true;

addlAxisY.Position = AxisPosition.Far;

Chart1.AxesY.Add(addlAxisY);

Chart1.Series[1].AxisY = addlAxisY;

Here’s what the chart looks like.

We now have an AJAX enabled UI with blistering performance. Changing the slicing of the chart has it refresh instantly. No more RS spinnies!

Christian Wade's Blog

Databind ChartFX to OLAP Datasource