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

image

Set its Data Source to Business Logic

image 

And select that data method in the Query property for the dataset

image

image

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.

image

Now drag the dataset into the Designs node

image

Now preview the report

image

And we see ..

image

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

image

And set the Default Layout to ColumnChart

image

And now drag this into the Designs node

image

Now preview ..

image

  

Only to be greeted by …

image

OK, first lesson – we need column groupings.

Maybe you will be tempted to ‘fix’ this problem by going to the NumWidgets field

image

And changing its Field Type from Data to Grouping

image

And if you do that and drag the dataset  into the designs node you’ll get

image

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

image

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 …

 

image

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 …

image

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

image

And set the Field Type from Data to Grouping

image

And now drag the dataset into the designs node and preview the report…

 

image

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

image

And look at its properties. Under the Style Template property you’ll notice there are two different Column Chart styles

image

Let’s try the first one: ColumnChartStyleTemplate

image

Which gives this result …

image

And the second one: ColumnChartStyleTemplateCC

image

Which gives …

image

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 …

image

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.

image

Ah, zero zero means “Auto” … so how do I get zero? My workaround is simple: I enter a very small value.

image '

Which previews as …

image

Great – now the Y axis shows zero on the bottom.

There is a corresponding property for the top of the Y axis

 

image

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.

image

And look at its properties … specifically the Display Point Label property

 image

It’s set to False by default, set it to True …

image

And preview …

 

image

Excellent. We have a nice looking column chart

To make it complete I’ll select a template for the report layout …

 

image

image

Which gives …

image

 

 

 

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