Microsoft Dynamics AX 2012 Reporting Cookbook
上QQ阅读APP看书，第一时间看更新
上一章目录下一章

Using a query as a datasource in a report

A query is the simplest way to fetch data for a report. Queries are beneficial as they are reusable and are easy to design. This recipe will guide you in creating a query in the AOT with the necessary optimization for a report datasource. The later part will guide you through creating a Visual Studio project for report development and use the query created to create a report datasource.

Getting ready

To work through this recipe, AX 2012 or AX 2012 R2 rich client with developer permission is required.

How to do it...

You can use a query as a datasource in the report, as follows:

  1. Open the AX Development Workspace.
  2. Navigate to AOT | Queries, right-click and select New Query.
  3. Rename the query to PKTReleasedProducts.
  4. Go to the query's datasource node, right-click and select New Data Source.
  5. Rename the datasource to InventTable and set the Table property Table to InventTable.
  6. There are two steps to select fields from InventTable.
  7. Go to the Fields node under the InventTable datasource and set the Dynamic property to Yes. This will automatically add all the fields in the InventTable to the query. Now set the property back to No. This is an easy way to add fields to the query node, alternatively, the property can be kept No and the fields can be dragged-and-dropped from the actual table.
  8. Drop all fields except Item, ItemType, and Namealias. This optimizes the query and consequently the fetch time:
  9. Save the query.
  10. Open Visual Studio.
  11. Navigate to File | New | Project.
  12. In the new project dialog, click on Microsoft Dynamics AX and then Report Model.
  13. Set the name as PKTReleasedProducts:
  14. Now right-click on the project and click on Add PKTRelasedProducts to AOT.
  15. On the reporting model, right-click, select Add and choose Report.
  16. Rename the report as PKTReleasedProductsReport.
  17. Go to the Datasets node and right-click on Add Dataset:
  18. Name the dataset as Products.
  19. Right-click on the dataset and open the properties.
  20. Click on the ellipsis (…) button in the query. How to do it...
  21. This opens a dialog with all queries in AOT:
  22. Select the query that was created for the report PKTReleasedProducts and click on Next:
  23. Select All Fields, since we dropped all the unwanted fields during the creation of the query.
  24. Go to the All Display Methods node and select the inventUnitId, itemGroupId, and itemName methods and click on OK.
  25. This will generate the fields list for the dataset. This completes the addition of the dataset to the report.

How it works...

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, the Visual Studio identifies the instance to connect 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.

Metadata and data retrieval: With AX 2012, WCF-based system services have been introduced. This includes the metadata service, query service, and user session service. 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 query, tables, and EDT while the query service is used to fetch the data.

Verify the query: In 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 nailing 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.

上一章目录下一章