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!