Tuesday, July 19, 2011

Dynamics AX 2009: Creating a simple SSRS Report

Dynamics AX 2009: Creating a simple SSRS Report
Dynamics AX 2009 features support for writing reports using SQL Server Reporting Services. In this post, I’m going to show you how to create a simple SSRS report that accesses data in the AX database.
The overall process I will describe will focus on the first stage of authoring and deploying the report from the developers perspective.
In future posts I’ll cover
  • Formatting the report
  • Adding menu items to launch the report
  • Security
  • Accessing OLAP data
  • etc.
The basic steps
  • Create an AX Query
  • Create a new AX Reports Project and a new report in that project
  • Create a dataset in the report bound to that query
  • Create a design to report to render the dataset
  • Preview the design
  • Deploy the design to SSRS
  • View the report via IE
  • Save the Report project back into AX
The report I will create will be a simple unformatted table of customers and customer IDs.

Create an AX Query
Launch the Dynamics Client
Open the AOT

In the AOT, right click on Queries and select New Query
By default a new query will be create with a default name (in this case “Query1”)
Right-Click on that query and select Rename
Give it the name “DemoQueryCustomers” and hit RETURN

Expand that query in the AOT
Right-click on Data Sources and click New Data Source
A new data source will be created and by default attached to some table (in this case it will be called “Address_1”

If you hover over this datasource you will see the query string

Right click on that data source and select Properties
The property window for that data source will appear
Navigate to the Table property and change it to the “CustTable”
Click on the Name property
You’ll notice that the Name changes to “Cust_Table_1”
Close the property window

Click Save All in the AOT
Close the AOT

Create a new AX Reports Project and a new report in that project

Launch Visual Studio 2008
File > New > Project
The New Project dialog will launch
Under Project Types, select Visual C#  / Dynamics
Under Templates select Dynamics AX Reporting Project
Change the Name to “DemoReportsLibrary1”
Click OK
An new AX Report Library project is created
By default it contains a report called “Report1”
Right click on this report and rename it to “DemoReportCustomers”
Create a dataset in the report that is bound to the AX query
In the report, right click on Datasets and select new Dataset
By default the name of the Dataset is “Dataset1".
Rename it to “DataSetCustomers”
In the properties window for the Dataset
Verify that the Data Source is “Dynamics AX”
Verify that the Data Source Type is “Query”
Verify that the Default Layout is “Table”
Click on the Query field
Click on the ellipsis button in the value for the Query field
A dialog will launch that will list all the queries in AX. It will take a few seconds to populate this dialog with all the queries so be patient.
Under Select Query, choose the query you previously created “DemoQueryCustomers”
After you select that query the right column will be filled with fields from the query.
By default All will be selected.
Uncheck All
Check the AccountNum field
Check the City field
Check the Name field
Check the State field
Click OK to close the dialog
You’ll now see that ”DataSetCustomers” contains all the fields you selected.
Select “DataSetCustomers” and drag it into the “Designs” node
After you finish dragging, you’ll see that a design has been created. It will be given the name “AutoDesign1”
Preview the design
With AutoDesign1 selected, click Preview in the toolbar
You’ll notice a message at the top of the preview saying “The design has validation warnings” and you can see the warnings in the error list at the bottom.
We’ll ignore this for now.
Click the Save icon to save the report

Deploy the design to SSRS
Right click on the solution and select Deploy
At the status bar in the bottom you’ll see a message on the left and some animation on the right indicating that the deployment is in progress.
Eventually it will say “Deployment succeeded” in the status bar
View the report via IE
Launch IE and navigate to your SSRS reports server (in this example it is http://isotopex1:81/reports/)
Navigate into the Dynamics link
Find the report we deployed
It will be listed as “DemoReportsLibrary.DemoReportCustomers.AutoDesign1”
Click on it to view the report
And now you’ll see the report

Save the Report project back into AX
Close IE
In the solution, right click the report project (not the solution) and select Save to AOD
Open the Dynamics AX client
Open the AOT
In the AOT expand the Report Libraries node
you’ll see the report library “DemoReportsLibrary” is now in AX
if you need to edit the report library again just locate it in the AOT, right-click it and select Edit in Visual Studio

Dynamics AX 2009: SSRS Reports and the role of Business Logic & Data Methods

Dynamics AX 2009: SSRS Reports and the role of Business Logic & Data Methods

This is going to be the first of several posts introducing Data Methods in the context of SSRS reports in AX2009. Before explaining the concepts, we’ll begin by immediately creating a report so that we have something specific to look at.
Platform notes: this demo was done using
  • AX2009 SP1
  • Windows 2008 x64


Let’s create a simple report in Visual Studio. Launch Visual Studio 2008
Create a new Dynamics  AX Reporting Project via File > New > Project
Click OK to create the project
And the project is created with an empty report called '”Report1”
Now let’s add a new Dataset. On the Datasets node, right click and select Add Dataset
And a Dataset called “Dataset1” was created
Now select DataSet1 to see the properties window in the lower right …
And here is a close-up view of the Properties Window

When the Data Source is “Dynamics AX” there are two choices for “Data Source Type”
  • The first is Query – this means an “AX Query”
  • The second is called “Business Logic”
Over the next few blog posts we will focus “Business Logic”

So now, Let’s select “Business Logic”
Now just to help us understand where we are in the process let’s Build the solution and see what happens
As you can see, the Error list shows two messages
  • The first error says that DataSet1 needs to refer to a “data method”
  • The second says that DataSet1 has no fields specified.
So at this point, we have introduced two concepts in the UX: “Business Logic” and “Data Methods”


Simply put, it indicates that there is some code (C#) that is going to run on the SSRS server when rendering this report. “Business Logic” as a term can be confusing – it’s best to think of this as meaning “Get Data for the Report from C# code instead of an AX Query”


In this context, it means that the Data Method (which is just a C# method) will return data that will be used in the data set.


To answer this question It’s easiest to create a simple one …


On the DataMethods node, right click and select Add Data Method
You’ll see that DataMethod1 was created
Let’s take a closer look. Right click on DataMethod1 and select View Code
And here is the code…
  And a close up…

The firs thing to note - the Data Method returns string – but returning string is useless if you want to use the Data Method as a source of data!
We need to the Data Method to return a System.Data.DataTable object.

But this isn’t enough. We need to define columns and add some data.
In this example, let’s just create a simple list of names and ages.
First the columns.
Cool, now let’s fill in some fake data.
So we are done with the data method.
What’s interesting here is that that Data Method didn’t talk to AX at all. This is useful for two reasons:
  • This provides a way to get data from non-AX or even non-database sources of data
  • Because there’s no dependency on AX you can prototype a report first without worrying about getting all the AX stuff in order
Now let’s hook up the Data Method to the DataSet
Click open up the Report model by clicking in “Report1.moxl”
Click on DataSet1
And in the properties window click on Query
The ellipsis button will appear. Click it.
And a new dialog will launch asking you to select a data method
So select DataMethod1 and click OK
You can see that the DataSet has picked up the fields from the DataTable automatically

Now let’s build the solution
Look, the error are gone.
At this point you might be tempted to try previewing the report. It you can’t. The reason is simple. The report doesn’t have any designs (i.e. layouts) to render.
So, let’s create one now.
Now, there are complicated,obvious ways of doing this and there is the simple (but not as obvious way). We shall be lazy and use the simple way: drag-and-drop.
Select “DataSet1”

And drag it into the Designs node
And you can see a design called “Design1” was auto-magically created.
If you expand the nodes you’ll see the design has been bound to DataSet1 and has added the fields
Now let’s preview the report…

OK, a pretty unexciting report …

You’ll notice the message “The design has validation warnings”.
If you look at the warnings
This is simple letting you know that if you want the report to look nice, you should bind the design to a layout and style template.
Making the report look nice isn’t the point of this post, so I will ignore it for now.


Now that you know what a Data Method is technically, you can see it is very simple. And now we can discuss the ways you can use them.
First you can use them to access data (their ostensible purpose)
  • AX DATA: If using AX Query is not good enough for you, you can use a Data Method to connect via BC.NET to get the data you want
  • NON-AX DATA: If you want to create a report against a data source an arbitrary data source (for example some RSS/ATOM feed) you can use a data method for this purpose.
Second, you can use Data Methods as an easy way to prototype a report design without worrying about modifying anything in your AX system, As you can see Data methods can simply generate their own data


The issue you should be aware of with a data method is that it has to construct a full DataTable and for the report. If your datasets are huge, then your DataTable will be huge and it may affect the performance of your reports.