Welcome to EMC Consulting Blogs Sign in | Join | Help

Christian Wade's Blog

Databind ChartFX to OLAP Datasource

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.


Chart with no extra Y axis

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.


Chart with extra Y axis

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





Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Ravimotha said:

In regards to charting anddoing various charts I can thoroughly recommend  an open source project called Zedgraph

It has been kept upto date and while the documentation leaves something to be desired at times it is extremely easy to pick up.

italso is very well supported and is constsnatly eveloving and getting better.

http://zedgraph.org/wiki/index.php?title=Main_Page

It's  both .Net 2.0 and 1.1 (though the new 2.0 build is much better)

It can be configured to draw images on the fly as well as actually outputting images.

It can also do some very complex graphing.

enough , go check it out

regards

ravi

November 10, 2006 5:52 PM

Leave a Comment

(required) 
(optional)
(required) 
Submit
Powered by Community Server (Personal Edition), by Telligent Systems