Using a query as a data source in a report

Queries offer the simplest and easiest way to retrieve data for SSRS reports in Dynamics AX R3. They are very advantageous as they are reusable, and the same query can be used as the data source of another SSRS report in Dynamics AX R3. They are also very easy to design.

We can create queries in two ways: either by using the query class, or under the Queries node in Application Object Tree (AOT). In this recipe, we will create a query under the Queries node in AOT and use it as a data source in SSRS reports. Later on, we will guide you on how to add the query as a data source through Visual Studio.

Getting ready

To work through this recipe, you will require AX 2012 R2 or AX 2012 R3 rich clients with developer permission.

How to do it...

Create a new query named PKTVendorDetails under the Queries node in AOT, and add some fields in the query. Create a new SSRS report in Visual Studio 2010 and add the PKTVendorDetails query into that report as a data source.

1. Open the AX Development Workspace (Ctrl + D).
2. Go to AOT | Queries and add a new query.
3. Rename the query to PKTVendorDetails.
4. Go to query's data source node and add the new data source.
5. Rename the data source to VendTable and set property table to VendTable.
6. There are two steps to select fields from VendTable. You can use any of these:
   • Go to the Fields node under the VendTable data source and set Dynamic Property to Yes. This will automatically add all the fields in the VendTable to the query.
   • Drag and drop the required field directly from the table. Drag VendGroup, AccountNum, InvoiceAccount, and Blocked from VendTable as shown in the following screenshot. This is the best way to optimize the query and, consequently, reduce the fetch time, so we will go for this option in our recipes.
   
7. Save the query.
8. Now open Visual Studio.
9. Navigate to File | New | Project.
10. In the new project dialog, select Microsoft Dynamics AX, and then Report Model.
11. Set the name as PKTVendorDetailsReport.
    
12. Now, right-click on project in Solution Explorer and Add a new Report to the PKTVendDetailReport project as shown in the following screenshot:
    
13. Rename the report as PKTVendorDetailsReport.
14. Now open the report by double-clicking on it in Solution Explorer.
15. Right-click on the Dataset node and select the New Dataset option.
16. Rename the dataset as VendorMaster.
17. Now, right-click on the VendorMaster dataset and select Properties.
    
18. Click on the ellipsis (…) button in Query.
19. Select the PKTVendorDetail query from the list and click on the Next button.
    
20. Select All Fields since we dropped all the unwanted fields during the creation of the query. From the All Display Methods node, select Name. Click on the OK button.
    

This will generate the fields list for the dataset. This completes the addition of a dataset to a report.

How it works…

In this receipe we have used queries as a datasource in SSRS report in Dynamics AX R3 as queries are reusable and can help to add the ranges in a report.

Connecting VS to AX

When creating a new report project in Visual Studio, if there is no option such as Microsoft Dynamics AX, then ensure that you have your reporting extensions installed. When you have multiple instances of Dynamics AX installed, Visual Studio identifies the instance to connect to from the client configuration. The active client configuration is used to establish the connection. The layer in which the report must be created is also fetched from the client configuration.

Retrieving metadata and data

With AX 2012, Windows Communication Foundation (WCF) based system services have been introduced. This includes the metadata service, query service, and user session service. The SSRS reporting extension uses the query and metadata services. The metadata service helps the report designer in Visual Studio to retrieve the metadata information of Queries, Tables, and Extended Data Types (EDT). The query service is used to fetch the data.

Verify the query

In the case of a complex query, a better approach would be validating the query before it is included in the report. Write a job in Dynamics AX that will use the query to retrieve the data and print the values to the infolog. This will help in identifying the problem when there is an issue with the report.

No joins

The report supports multiple datasets, but as in AX forms these datasets cannot be joined and they remain independent.