Hi Guys , I hope you are doing Good.. 🙂
Today i am gonna show SQL Server Reporting Services (SSRS ) development for Dynamics AX. Here i’m going to demonstrate how to develop a simple SSRS report.
Before going to SSRS development , I would like to explain the importance and usage of SSRS in short manner.
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.
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.
SSRS supports two modes of managing the report catalog
- Native Mode
- SharePoint Integrated mode
In Ax 2012 only supports Native Mode, while AX 2012 R2 supports Integrated and SharePoint Integrated Mode.
Refer more details about SSRS
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.
Let’s begin our Simple SSRS Report Development.
Here we are going to see all the 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.
I’m going to 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
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
• 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
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:
- Provide Name as CustTransReport.
- Provide Title as Customer transactions.
- Provide Descriptions as All Customer transactions.
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.
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.
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.
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.
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.
14. Right-click on Sorting under CustomerAcc grouping and select Add Sort.
15. Rename Sorting to AccountNum and SortBy to Fields!AccountNum.Value
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
20. Right-click on CustTableRow and select Add | Field.
21. Rename the field to Name and set Expression to Fields!Name.Value.
22. Go to the Data node under CustTransDSTable and verify that the following
fields (Refer to the following screenshot) were correctly added automatically.
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
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
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.
Publish-AXReport -ReportName CustTransactions
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.
In future we will post various kinds of SSRS.. Enjoy….:)