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

image

Open the AOT

image

image

In the AOT, right click on Queries and select New Query

image

By default a new query will be create with a default name (in this case “Query1”)

image

Right-Click on that query and select Rename

Give it the name “DemoQueryCustomers” and hit RETURN

image

image

Expand that query in the AOT

image

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”

image

image

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

image

Right click on that data source and select Properties

image

The property window for that data source will appear

image

Navigate to the Table property and change it to the “CustTable”

image

Click on the Name property

You’ll notice that the Name changes to “Cust_Table_1”

image

Close the property window

image

image

Click Save All in the AOT

image

Close the AOT

image

image

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

 

Launch Visual Studio 2008

image

File > New > Project

image

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

image

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”

image

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”

image

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

image

Click on the ellipsis button in the value for the Query field

image

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.

image

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

image

Check the AccountNum field

image

Check the City field

image

Check the Name field

image

Check the State field

image

Click OK to close the dialog

You’ll now see that ”DataSetCustomers” contains all the fields you selected.

image

Select “DataSetCustomers” and drag it into the “Designs” node

image

After you finish dragging, you’ll see that a design has been created. It will be given the name “AutoDesign1”

image

Preview the design

With AutoDesign1 selected, click Preview in the toolbar

image

 image

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

image

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.

image

Eventually it will say “Deployment succeeded” in the status bar

image

View the report via IE

Launch IE and navigate to your SSRS reports server (in this example it is http://isotopex1:81/reports/)

image

Navigate into the Dynamics link

image

Find the report we deployed

It will be listed as “DemoReportsLibrary.DemoReportCustomers.AutoDesign1”

Click on it to view the report

image

And now you’ll see the report

image

 

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

image

Open the Dynamics AX client

Open the AOT

image

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

image