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