Developing SSRS Reports for Dynamics AX

5 (1 reviews total)
By Mukesh Hirwani
  • Instant online access to over 7,500+ books and videos
  • Constantly updated with 100+ new titles each month
  • Breadth and depth in over 1,000+ technologies
  1. Developing a Simple SSRS Report in Visual Studio

About this book

SQL Server Reporting Services is the primary reporting platform for Microsoft Dynamics AX. Nowadays every business demands reports ranging from showing an aggregate view of their business performance to the transactional data formatted in a way that can be easily filtered, printed, and emailed. SQL Server Reporting Services allows you to do all this and more, quickly and easily.

Developing SSRS Reports for Dynamics AX 2012 covers all the technical aspects of AX 2012 reporting, taking you through various step-by-step guides to quickly develop reports. It encapsulates various real-time scenarios and walkthroughs which will guide you step-by-step to develop reports. You will then go on to learn about various performance-related measures to help you develop or customize reports for Dynamics AX 2012 more efficiently.

Developing SSRS Reports for Dynamics AX 2012 is completely based on Microsoft Dynamics AX 2012 report development. Developing SSRS Reports for Dynamics AX has been written in a way to make reporting complex components quicker and easier. You will come across day-to-day scenarios to perform report development.

Learn various mechanisms of developing reports in Visual Studio for Dynamics AX 2012. Develop reports using various types of data sources like AOT Query, external data source, and Report Data Provider class. You will also learn various performance enhancement techniques that will help you to manage complex reporting requirements.

Publication date:
September 2013
Publisher
Packt
Pages
132
ISBN
9781782177746

 

Chapter 1. Developing a Simple SSRS Report in Visual Studio

SQL Server Reporting Services (SSRS) provides a full set of tools to develop various kinds of reports, manage them, and deploy them to multiple environments. It also provides an independent user interface where users can see all the reports and run them, or administrators can control security permissions for other users.

In this chapter we will also cover the following topics in detail:

  • Reporting services modes

  • Creating an Application Object Tree (AOT) query

  • Creating a Report Model project

  • Creating an Auto Design report

  • Creating a drill-through report

  • How to enable a drill-down action in multiple reports

  • Saving and deploying a report

Microsoft SQL Server reporting services are used now for Microsoft Dynamics AX 2012 reporting requirements. All reports available in AX 2012 are based out of SSRS, and use a rich variety of capabilities available in standard SSRS.

To start with, let’s talk about a few major terms which would be used under chapters written in this book.

Tip

Downloading the example code

You can download the example code files for all Packt books you have purchased from your account at http://www.packtpub.com. If you purchased this book elsewhere, you can visit http://www.packtpub.com/support and register to have the files e-mailed directly to you.

 

Reporting services modes


SSRS supports two modes for managing the report catalog: Native mode and SharePoint integrated mode.

AX 2012 only supports Native mode, while AX 2012 R2 supports Integrated and SharePoint integrated mode.

The Native mode

In Native mode, a Report Server is a standalone application which does not talk to any other application. All Microsoft Dynamics AX 2012 reports are deployed and hosted in the Report Server which could be managed using Report manager.

Screenshot 1: Report manager

SSRS Report Server can be opened in any web browser and looks like the following screenshot:

Screenshot 2: Report Server

The SharePoint integrated mode

In SharePoint integrated mode, a Report Server runs in a SharePoint website web farm. All reports are deployed and hosted to a Reports library under a SharePoint library.

Screenshot 3: SharePoint integrated mode

Microsoft Dynamics AX 2012 has been tightly integrated with Visual Studio 2010, which enables administrators/developers to develop SSRS reports in Visual Studio Integrated Development Environment easily.

Visual Studio provides the ability to create various types of SSRS reports for Microsoft Dynamics AX 2012. Administrators/Developers can create a Report Model project in Visual Studio which hosts multiple SSRS reports.

Screenshot 4: Visual Studio reporting capabilities

 

Walkthrough – Create an Auto Design report with AX Query


Scenario

Matt—A Customer relationship manager, wants to see all his customers and their respective transactions in a report to see how well the company is able to manage customers, and which customer is providing them better sales.

This walkthrough illustrates the following tasks:

  • Creating an AOT Query

  • Creating a Report Model project

  • Creating an Auto Design SSRS Report

  • Saving the report to AOT and deploying it to the Report Server

Prerequisites

To learn and implement the following walkthrough, you must have:

  • Microsoft Dynamics AX 2012 with sample data

  • Microsoft Visual Studio 2010 with Microsoft Dynamics AX reporting extension

  • Microsoft Dynamics SQL Server Reporting Services

Creating an AOT Query

  1. Open Microsoft Dynamics AX 2012 from the Start menu.

  2. Open Development Workspace. There are multiple ways to do this which are described as follows:

    1. Press Ctrl + D to open AOT in Development Workspace.

    2. Press Ctrl + Shift + P to open Projects in Development Workspace.

    3. Press Alt + W to open windows and navigate to New Development Workspace.

    4. Press Ctrl + Shift + W to open New Development Workspace.

  3. Go to AOT | Queries node.

  4. Right-click on the Queries node and click on New Query.

  5. Right-click on the newly created query and navigate to Properties.

  6. Set the following properties:

    1. Provide Name as CustTransReport.

    2. Provide Title as Customer transactions.

    3. Provide Descriptions as All Customer transactions.

    Screenshot 5: Query

  7. Under CusTransReport, right-click on Data Sources, and select New Data Source.

  8. Set the Table property for the new data source to CustTable.

  9. Select the Fields node and set Dynamic Property to Yes.

  10. Right-click on the Data Sources node and select new data source under CustTable_1 data source.

  11. Set the Table property for new data source to CustTrans, also set Relations property to Yes.

  12. Select the Fields node and set the Dynamic property to Yes under CustTrans_1 data source.

    Screenshot 6: Data sources under query

Creating a Report Model project

  1. Start Visual Studio; press Ctrl + N to create a new project.

  2. Select Microsoft Dynamics AX under Installed Templates in the left pane, and select Report Model.

  3. Provide a name for the project as CustTransReport.

    Screenshot 7: New visual studio project

Creating an Auto Design SSRS report

  1. Right-click on Solution; navigate to Report under Add submenu. Select the report and rename it to CustTransactions.

  2. Right-click on Datasets and click on Add dataset.

  3. Modify the following properties for the newly added dataset:

    1. Data Source to Microsoft Dynamics AX.

    2. Data Source Type to Query.

    3. Default Layout to Table.

    4. Name to CustTransDS.

    Screenshot 8: New dataset

  4. Switch to the Query property, and press the button at the top right-hand corner to open the Query dialog.

  5. Select CustTransReport from the list and press the Next button.

  6. Select the AccountNum, Name fields from CustTable_1, and select Voucher, TransType, TransDate, Invoice, AmountCur, remainAmountCur() display methods, and Currency fields from CustTrans_1 (Refer to Screenshot 4: New Dataset).

  7. Drag-and-drop CustTransDS to the Design section of the report. This will create a new Auto Design named as AutoDesign1.

  8. Select AutoDesign1, go to Properties, and set the following properties:

    1. LayoutTemplate to ReportLayoutStyleTemplate.

    2. Title to Customer transactions.

  9. Select CustTransDSTable under AutoDesign1 and set the following properties:

    1. StyleTemplate to TableStyleAlternatingRowsTemplate.

    2. Title to List of customer transactions.

  10. Right-click on Groupings inside CustTransDSTable and select Add Grouping.

  11. Rename the new grouping to CustomerAcc.

  12. Right-click on Group on under CustomerAcc grouping and select Add Group on.

  13. Rename the grouping to AccountNum and set Expression to Fields!AccountNum.Value in properties.

    Screenshot 9: Grouping

  14. Right-click on Sorting under CustomerAcc grouping and select Add Sort.

  15. Rename Sorting to AccountNum and SortBy to Fields!AccountNum.Value under properties.

  16. Right-click on Header under CustomerAcc grouping and select Add row.

  17. Rename the row to CustTableRow.

  18. Right-click on CustTableRow and select Add | Field.

  19. Rename the field to AccountNum and set Expression to Fields!AccountNum.Value.

  20. Right-click on CustTableRow and select Add | Field.

  21. Rename the field to Name and set Expression to Fields!Name.Value.

    Screenshot 10: Header row

  22. Go to the Data node under CustTransDSTable and verify that the following fields (Refer to the following screenshot) were correctly added automatically.

    Screenshot 11: Adding fields to data

  23. Save all changes done.

Saving the report to AOT and deploying it to the Report Server

  1. In the Solution Explorer, right-click on the Report Model project and select Build.

  2. Once the report is successfully built, right-click on the Report Model project and select Add CustTransReport to AOT.

  3. We can deploy the report to the Report Server, once it has been added to AOT.

  4. To deploy the report to the Report Server:

    1. Right-click on the Report Model project and select Deploy.

    2. Switch to AOT, right-click on CustTransactions report under AOT | SSRS Reports | Deploy Element.

    3. Deploy using Microsoft Dynamics AX 2012 Management Shell.

Deploying using Microsoft Dynamics AX 2012 Management Shell

The deployment can be done using the following steps:

  1. Click on Start | Administrative tools.

  2. Right-click on Microsoft Dynamics AX 2012 Management Shell.

  3. Click on Run as administrator.

  4. Enter the following PowerShell command to deploy.

    Note

    Publish-AXReport -ReportName CustTransactions

    Screenshot 12: Deployment using Microsoft Dynamics AX 2012 Management Shell

Running the report

  1. Create an Output menu item by right-clicking on AOT | Menu Items | Output | New menu item.

  2. Rename the menu item to CustTransactionsReport.

  3. Set the following properties:

    1. Label to Customer transactions.

    2. HelpText to List of customer transactions.

    3. ObjectType to SSRSReport.

    4. Object to CustTransactions.

    5. ReportDesign to AutoDesign1.

  4. Save all changes.

  5. Right-click on the CustTransactionsReport menu item and select Open.

  6. Provide some dynamics filters if required.

  7. Click Ok to run the report.

    Screenshot 13: Final report

 

Walkthrough – Creating a drill-through report


Scenario

Tom— A Purchasing manager, wants to see all his vendors in a list and wants to iterate vendors to all of their purchase orders, and to their purchase order details. For this, he needs a report in the system which could display a list of vendors with drill-through capability to iterate to all purchase orders for the selected vendor, and also to the purchase order details when the purchase order ID is selected.

This walkthrough illustrates the following tasks:

  • Creating a Report Model project.

  • Creating reports

    • Vendor list

    • Purchase order list

    • Purchase order details

  • Providing drill-through action under designs

  • Saving the report to AOT and deploying it to the Report Server.

Prerequisites

To learn and implement the following walkthrough, you must have:

  • Microsoft Dynamics AX 2012 with sample data.

  • Microsoft Visual Studio 2010 with Microsoft Dynamics AX reporting extension.

  • Microsoft Dynamics SQL Server Reporting Services.

Creating a Report Model project

  1. Start Visual Studio; press Ctrl + N to create a new project.

  2. Select Microsoft Dynamics AX under Installed Templates in the left pane, and select Report Model.

  3. Provide a name for the project as VendPurchaseOrders.

    Screenshot 14: New visual studio project

Creating reports

Creating a Vendor list report

  1. Right-click on Solution and navigate to Report under the Add submenu. Select the report and rename it to VendListRep.

  2. Right-click on datasets and click on Add dataset.

  3. Modify the following properties for the newly added dataset:

    1. Data source to Microsoft Dynamics AX.

    2. Data source type to Query.

    3. Default Layout to Table.

    4. Name to VendListDS.

    Screenshot 15: New dataset

  4. Switch to the Query property and click on the blue button with white dots to open the Query dialog.

  5. Select VendBaseDataView from the list and press the Next button.

  6. Select AccountNum, vendName display methods; BankAccount, Blocked, Invoice fields from VendTable_1 (Refer to Screenshot 2: New Dataset).

  7. Drag-and-drop VendListDS to the Design section of the report. This will create a new Auto Design named as AutoDesign1.

  8. Select AutoDesign1, go to Properties, and set the following properties:

    1. LayoutTemplate to ReportLayoutStyleTemplate.

    2. Title to Vendors.

  9. Select VendListDSTable under AutoDesign1 and set the following properties:

    1. StyleTemplate to TableStyleAlternatingRowsTemplate.

    2. Title to List of vendors.

Creating a Purchase order list report

  1. Right-click on Solution; select Report under the Add submenu. Select the report and rename it to VendPurchOrders.

  2. Right-click on datasets and click on Add dataset.

  3. Modify the following properties for the newly added dataset

    1. Data source to Microsoft Dynamics AX.

    2. Data source type to Query.

    3. Default Layout to Table.

    4. Name to PurchOrderListDS.

    Screenshot 16: New dataset

  4. Switch to the Query property and press the icon to open the Query dialog.
  5. Select PurchTableListPage from the list and press the Next button.

  6. Select PurchId, PurchName, OrderAccount, InvoiceAccount, PurchStatus, PurchaseType fields from PurchaseTable (Refer to Screenshot 3: New Dataset).

  7. Drag-and-drop PurchOrderListDS to the Design section of the report. This will create a new Auto Design named as AutoDesign1.

  8. Select AutoDesign1, go to Properties, and set the following properties:

    1. LayoutTemplate as ReportLayoutStyleTemplate.

    2. Title as Purchase orders.

  9. Select PurchOrderListDSTable under AutoDesign1 and set the following properties:

    1. StyleTemplate as TableStyleAlternatingRowsTemplate.

    2. Title as List of purchase orders.

Creating a Purchase order details report

  1. Right-click on Solution, select Report under Add submenu. Select report, rename the report to PurchDetails.

  2. Right-click on datasets, click on Add dataset.

  3. Modify the following properties for the newly added dataset:

    1. Data source to Microsoft Dynamics AX.

    2. Data source type to Query.

    3. Default Layout to Table.

    4. Name to PurchDetailDS.

    Screenshot 17: New dataset

  4. Switch to the Query property, and click on the button at the corner to open the Query dialog.

  5. Select PurchTableDocument from the list and click on the Next button.

  6. Select PurchId, PurchName, OrderAccount, InvoiceAccount, Email, DeliveryDate fields from PurchTable, and select ItemId, Name, QtyOrdered, PriceUnit, PurchPrice, and CurrencyCode, LineAmount fields from PurchLine (Refer to Screenshot 4: New Dataset).

  7. Drag-and-drop PurchDetailDS to the Design section of the report. This will create a new Auto Design named as AutoDesign1.

  8. Select AutoDesign1, go to Properties, and set the following properties:

    1. LayoutTemplate to ReportLayoutStyleTemplate.

    2. Title to Purchase order details.

  9. Select PurchDetailDSTable under AutoDesign1, and set the following properties:

    1. StyleTemplate to TableStyleAlternatingRowsTemplate.

    2. Title to Purchase order details.

  10. Right-click on Groupings inside PurchDetailDSTable, and select Add Grouping.

  11. Rename the new grouping to PurchId.

  12. Right-click on Group On under PurchId grouping and select Add Group on.

  13. Rename Grouping to PurchIdGrp and set Expression to Fields!PurchId.Value in Properties.

    Screenshot 18: Adding a PurchId group

  14. Right-click on Header under PurchId grouping, and select Add row.

  15. Rename the row to PurchTableRow.

  16. Right-click on PurchTableRow and select Add | Field.

  17. Rename the field to PurchId and set Expression to Fields!PurchId.Value.

  18. Right-click on PurchTableRow and navigate to Add | Field.

  19. Rename the field to Name and set Expression to Fields!PurchName.Value.

  20. Right-click on PurchTableRow and select Add | Field.

  21. Rename the field to Name and set Expression to Fields!OrderAccount.Value.

  22. Right-click on PurchTableRow and select Add | Field.

  23. Rename the field to Name and set Expression to Fields!InvoiceAccount.Value.

  24. Right-click on PurchTableRow and select Add | Field.

  25. Rename the field to Name and set Expression to Fields!Email.Value.

  26. Right-click on PurchTableRow and select Add | Field.

  27. Rename the field to Name and set Expression to Fields!DeliveryDate.Value

    Screenshot 19: Header row

  28. Go to the Data node under PurchDetailDSTable and verify that the following fields were correctly added automatically.

    Screenshot 20: Adding fields to the data section

  29. Save all changes done.

Providing a drill-through action under designs

Adding a parameter – the Purchase order list report

  1. Right-click on Parameters and select Add | Parameter.

  2. Rename the parameter to VendAccount.

  3. Right-click on filters, and select Add Filter under VendPurchOrders report.

  4. Rename the filter to VendAccount and set the following properties:

    1. Expression to Fields!OrderAccount.Value.

    2. Name to VendAccount.

    3. Operator to Equals.

    4. Value to Parameters!VendAccount.Value.

    Screenshot 21: Adding a filter to a Purchase order list report

Adding a parameter – the Purchase order details report

  1. Right-click on Parameters, and select Add | Parameter.

  2. Rename the parameter to PurchId.

  3. Right-click on filters, and select Add Filter under PurchDetails report.

  4. Rename the filter to PurchId and set the following properties:

    1. Expression as Fields!PurchId.Value.

    2. Name as PurchId.

    3. Operator as Equals.

    4. Value as Parameters!PurchId.Value.

    Screenshot 22: Adding a filter to a Purchase order details report

Drill-through action

The Vendor list report

  1. Right-click on AccountNum, and select Add Report Drill Through Action under AutoDesign1 | VendListDSTable | Data.

  2. Select ReportDrillThroughAction and go to Properties.

    Screenshot 23: ReportDrillThroughAction for Vendor list report

  3. Set the ReportDesign property to AutoDesign1 of VendPurchOrders report.

  4. Select the AX_CompanyName parameter under ReportDrillThroughAction and set the value to Parameters!AX_CompanyName.Value.

  5. Select the VendAccount parameter under ReportDrillThroughAction and set value to Fields!AccountNum.Value.

The Purchase order list report

  1. Right-click on PurchId, and select Add Report Drill Through Action under AutoDesign1 | PurchOrderListDSTable | Data.

  2. Select ReportDrillThroughAction and go to Properties.

    Screenshot 24: ReportDrillThroughAction to Purchase order details report

  3. Set the ReportDesign property to AutoDesign1 of PurchDetails report.

  4. Select the AX_CompanyName parameter under ReportDrillThroughAction and set the value to Parameters!AX_CompanyName.Value.

  5. Select the PurchId parameter under ReportDrillThroughAction and set the value to Fields!PurchId.Value.

Saving the report to AOT and deploying it to the Report Server

  1. In the Solution Explorer, right-click on the Report Model project, and select Build.

  2. Once the report is successfully built, right-click on the Report Model project, and select Add VendPurchaseOrders to AOT.

  3. Once the report is added to AOT, we can deploy the same to the Report Server.

  4. To deploy the report to the Report Server:

    1. Right-click on the Report Model project and select Deploy.

Preview

The Vendor list will be as follows:

Screenshot 25: All purchase orders for the selected vendor

Screenshot 26: Purchase order details for the selected Purchase order

Screenshot 27: Final report

 

Summary


In this chapter we discussed the various reporting services modes and how to create a variety of reports for Microsoft Dynamics AX 2012 using Visual Studio. It is recommended to draw a design to analyze how, and what, report has to show, and then perform the development work, as it will give a clear picture about the development work to be done, and the correct way of developing a report can be chosen wisely.

In the next chapter we will learn how to develop advanced reports for Microsoft Dynamics AX 2012 using Visual Studio.

About the Author

  • Mukesh Hirwani

    Mukesh Hirwani is currently working as a Technical Solution Architect with Microsoft Dynamics Gold Partner. The company provides specialized solutions to the family of Microsoft Dynamics Partners/Resellers/Customers. Mukesh started his career back in the year 2008 parallel to his Post-graduation (Master of Science Studies in Software Engineering from the Birla Institute of Technology and Science). Since then, he has been a recognized Microsoft Certified Professional for AX in all the major areas: Development, Configuration and Installation, Integration, Financials, Projects, Customer Relationship Management, Human Resources, and Trade and Logistics. He has also been delivering trainings for AX Technical Track. From 2008 to 2013, Mukesh has participated in over 12 Microsoft Dynamics AX implementations. He has handled a wide range of development, consulting, and leading roles, while always maintaining a significant role as an Enterprise Application Developer. In the early days of the year 2013, Mukesh has worked with Microsoft for AX 2012 R2 learning material preparation.

    Browse publications by this author

Latest Reviews

(1 reviews total)
Excellent