I’ve been looking forward to sharing this post for a while. I am going to show exactly how to get a column chart in SSRS using Dynamics AX 2009. I’m going to go step-by-step and show you “gotchas” you will encounter as you develop charts. Although this takes more to do it in this way, ultimately prepares you for the real-world use the reporting components in Dynamics AX.
Ultimately I want a simple column chart showing a list of numbers. Instead of hard-coding the numbers I’ve written a function that generates as many random points as we need. Because we often want to see numbers in a trend, this method generates n numbers from from some starting value to some ending value (i.e. points along a line displaced by some factor)
public static IEnumerable<double> generate_random_sequence(int num_values, double min_value, double max_value,
double start_value, double end_value, double factor)
{
double step = (end_value - start_value) / (num_values - 1);
var r = new System.Random();
var indices = Enumerable.Range(0, num_values);
var values = from i in indices
let delta = (r.NextDouble() * (max_value - min_value) * factor) - (factor * 0.5)
let v = start_value + (i * step)
select v + delta;
var normalized_values = from value in values
select System.Math.Min(max_value, System.Math.Max(min_value, value));
return normalized_values;
}
Now I create a data method …
[DataMethod(), AxSessionPermission(SecurityAction.Assert)]
public static System.Data.DataTable DataMethod_Series_0()
{
int num_values = 14;
double min_value = 0;
double max_value = 100;
double start_value = 30;
double end_value = 70;
double factor = 0.10;
var values = generate_random_sequence(num_values, min_value, max_value, start_value, end_value, factor);
var records = values.Select((value) => new { NumWidgets = (int) value } );
var datatable = Isotope.Data.DataUtil.DataTableFromEnumerable(records);
return datatable;
}
And then build the project to verify everything compiles.
Now create a dataset

Set its Data Source to Business Logic
And select that data method in the Query property for the dataset
Build the project to check if anything is wrong.
Notice that we haven’t altered the Default Layout – it is still set to Table. This is deliberate. When you create any charts, I always advise you first to verify that your datasets render as tables.
Now drag the dataset into the Designs node
Now preview the report

And we see ..
Ugly report. But we don’t care how it looks. We just did this to validate the data.
Now I’ll create a copy of that same data set
And set the Default Layout to ColumnChart

And now drag this into the Designs node
Now preview ..

Only to be greeted by …
OK, first lesson – we need column groupings.
Maybe you will be tempted to ‘fix’ this problem by going to the NumWidgets field
And changing its Field Type from Data to Grouping

And if you do that and drag the dataset into the designs node you’ll get
So now you have 2 errors – 1 more than previously so things got worse not better.
The way to solve is is simple. Create an additional field in the dataset that will serve for the category grouping
To do this we need to modify the data method
[DataMethod(), AxSessionPermission(SecurityAction.Assert)]
public static System.Data.DataTable DataMethod_Series_1()
{
int num_values = 14;
double min_value = 0;
double max_value = 100;
double start_value = 30;
double end_value = 70;
double factor = 0.10;
var values = generate_random_sequence(num_values, min_value, max_value, start_value, end_value, factor);
var records = values.Select((value, index) => new { Index = index, NumWidgets = (int) value });
var datatable = Isotope.Data.DataUtil.DataTableFromEnumerable(records);
return datatable;
}
Notice the Select method! This version of the Select method in LINQ is not that well known – it provides the current index of the enumeration
And now delete the dataset and recreate it but bind it to this new data method
Good, now we see both fields.
Because the data set is new, the Default Layout is set to table … verify that you can see the table rendering …
Good so far.
Now set the Default Layout property of that Data set to ColumnChart and drag the data set into the designs node. You’ll get an AutoDesign that previews as …
Ugh. Of course – didn’t address the original complaint. We haven’t identified which field to use for the category grouping.
Go to the Index field in the dataset
And set the Field Type from Data to Grouping
And now drag the dataset into the designs node and preview the report…
Fantastic – we have an ugly column chart.
I can’t stand looking at this so let’s set a style template
Find the chart element in the AutoDesign
And look at its properties. Under the Style Template property you’ll notice there are two different Column Chart styles
Let’s try the first one: ColumnChartStyleTemplate
Which gives this result …

And the second one: ColumnChartStyleTemplateCC

Which gives …
I like this one better. We’ll stick with it.
Now that we are OK with the look of the chart. Let’s fix up the content of the chart.
Problem #1 – look at the Y axis – the smallest value on the axis 30. Why is that?
This is an automatic behavior of the chart. In many cases we’d like to see the Y axis start at zero, of course.
This behavior is controlled by a property on the chart …

But wait – it says the min value should be zero. What’s going on?
If you click on the property … you’ll see that the property window contains some additional information.
Ah, zero zero means “Auto” … so how do I get zero? My workaround is simple: I enter a very small value.
'
Which previews as …
Great – now the Y axis shows zero on the bottom.
There is a corresponding property for the top of the Y axis
It behaves similarly, Data Scale Min Value but for this example I’ll leave it alone.
Problem #2 – I can’t see the exact number of widgets.
To fix this you’ll have to expand the design nodes and underneath the chart, open the Data node and find the NumWidgets field.
And look at its properties … specifically the Display Point Label property
It’s set to False by default, set it to True …
And preview …
Excellent. We have a nice looking column chart
To make it complete I’ll select a template for the report layout …
Which gives …

SOURCE CODE
You can get it here: http://cid-19ec39cb500669d8.skydrive.live.com/browse.aspx/Public/Dev/SampleCode/Dynamics/SaveenR-Blog-Post-%7C52009-08-02%7C6?view=details