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
The basic steps
The report I will create will be a simple unformatted table of customers and customer IDs.
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
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”
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”
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.
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”
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
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
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
In the solution, right click the report project (not the solution) and select Save to AOD
Open the Dynamics AX client
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