Developing Query Based SSRS reports using VS - AX 2012

1- Developing a new SSRS report:

Before start developing SSRS reports for Dynamics AX, you must have installed BI Components and Visual Studio tools from AX setup. After installation of both these additional components, open Visual Studio, click on File -> New Project. New Project dialog is open as shown below:

From the above dialog select Microsoft Dynamics AX in Project types and select Report Model under templates section. The SSRS reports needs a data source from where the report can grab the data and displayed it on the report. There are three types of data source available to be used in SSRS report which is as follows:

• Query
• RDP (Report data provider).
• Business Logic.

When you use query as a data source type for SSRS report then the report is called Query based report. Now, I am going to explain you that how we develop SSRS reports based on Query.

2. Developing Query Based SSRS reports using VS

Before you can start developing query based reports in VS, you need an AOT query to be created in Dynamics AX. Suppose you want to show all the records form CustTable table in an SSRS report. This can be achieved through query based report. Create an AOT query in Dynamics AX as shown below:

The Query name is SampleQuery. Add CustTable under the data sources node of the query. All the fields of the CustTable are included in the AOT query. Now you need to create an SSRS report which used this query as a data source and display all the fields of CustTable on SSRS report. Open Visual Studio and goto File -> New Project. New Project dialog form will be opened as shown below:

Select Microsoft Dynamics AX under Project types and Report Model under Templates section. Specify the name of the report as ‘QueryBasedReporrt’ and press OK button. A new SSRS project will be created as shown below:

Now you need to add a report under the above project. Right click on the project, select Add and then select Report. A report will be added to the project with the name Report1. Change the report name to QueryBasedReport. Now the solution explorer should look like as shown below:

Double click on the newly created report. You will see report items on the left side in Visual Studio as shown in the below screen shot:

The purpose of each report items is described below:

Ø Datasets: The report dataset retrieves data from the AOT query. All fields which are available in AOT query are shown in the report dataset which can be referred in the report design later on.

Ø Designs: The design or layout of the report on which the data would be displayed after retrieving from AX.

Ø Images: Any embedded images that you want to display on SSRS report.

Ø Data Methods: Business logic written in C#. Data methods are no more recommended.

Ø Parameters: Report parameters which are to be displayed on SSRS report and user can filter the report based on those parameters.

You first need to create a dataset that will refer to an AOT query created earlier. Right click on Datasets node and click ‘Add Dataset’. A dataset will be created with name ‘DataSet1’. Rename it to ‘QueryBasedReportDS’. The newly created dataset should refer to the AOT query from where the data can be picked. Right click on the newly created dataset and select ‘Properties’. Set the data source type to ‘Query’ and click on the ‘Query’ property. An ellipses button appears, click on it. A new window will open which shows the entire AOT queries present in Dynamics AX. Select the query ‘SampleQuery’ which you have created earlier as shown below:

Select All Fields from the right column to show all fields on the report dataset and then click ‘OK’ button. All the fields which are added on AOT query are now showing on the report dataset. You can see all the fields by expanding the Fields node under the dataset. You can refer to any of these fields on the report design. Now we have created a dataset which provides the data to the report, the next step is to create a report design.

There are two types of report designs available in SSRS report i.e. AutoDesign and PrecisionDesign. First preference when developing reports is to use AutoDesign unless you need a custom placement of fields in a report and in that case you have to use PrecisionDesign. Generally for complex report designs, we use PrecisionDesign. For the sake of simplicity, we will use AutoDesign here for our first SSRS report.

Right click on the Designs node, click on Add-> AutoDesign. An auto design is created with the name AutoDesign1. Change its name to ‘Report’ by right clicking on the AutoDesign1 and click Rename. It is recommended that you set the name of the Design to either ‘Design’ or ‘Report’. Now drag the data set of the report ‘QueryBasedReportDS’ on Report Design named ‘Report’. A table will be created which contains all the fields that are shown in the report dataset. The value of these fields will be shown on the report output in the same order as it is showing in the data table. The final report design should look like as shown below:

Now we execute the report to see the output. The records will be fetched from Customer table so make sure that the CustTable has some records in it before opening the report otherwise a blank output will be shown. There are three ways of opening an SSRS report which are as follows:

1) From Visual Studio which is actually the preview of the report.

2) From Browser by opening the URL http://localhost/reports.

3) From Dynamics AX through output menu item.

2.1. Previewing SSRS reports from Visual Studio

You can easily preview SSRS reports by right clicking on the design and select Preview option. For our example right click on the Design ‘Report’ and select Preview as shown in the below screen shot:

A separate preview page is open which will show the report output. If your report has some parameters then the parameters will be shown first and then the output will be shown based on the values of the parameters. Following report output will be shown when you preview the report from VS:

2.2. Viewing SSRS report on browser

Before viewing report on browser you need to deploy report on the report server. You can deploy SSRS reports in two ways, first way is to right click the solution in Visual Studio and select deploy solution option and the second way is to go to SSRS Reports/Reports node under AOT, right click on the report you want to deploy and then select Deploy. To deploy SSRS report from AX, you need to first add the report project to AOT. Following screen shot shows how to do this:

Now right click the report solution in VS and select deploy solution as shown below:

To view SSRS report from browser, go to URL http://localhost/reports. When the page is open you will see the Dynamics folder link shown on the page. By clicking on the Dynamics folder you can see the list of all SSRS reports deployed on the report server as shown below:

Click on the report which you want to preview on browser. In our case, click on the SSRS report QueryBasedReport.Report to view it on browser. Following output will be shown when you view the report from browser:

Opening SSRS report from AX

To open SSRS report from AX, you first need to create an output menu item which refers to the SSRS report you have created earlier. Below screen shot shows how to do this:

Step 1: Create an output menu item Step 2: Set the object type and object properties as shown in the above screen shot.

Now right click on the output menu item QueryBasedReport and select Open to launch the report. Following output will be shown when you run the report from AX:

2.3. Filtering a report with an specific parameter

Suppose you want to filter the report we have developed earlier by customer group. In that case you need to show the parameter customer group on the report dialog so that user can run the report for a specific customer group. Steps are shown below:

            

Step 1: Adding range in a report query. Step 2: Add CustGroup field under the Ranges node.

Now compile the query ‘SampleQuery’ to save the changes. Run the report by right clicking the output menu item and select Open. Following report dialog will be shown:

You can see the customer group parameter in the above dialog which we have added through report query. Another parameter customer account is shown on the above dialog. Just ignore this parameter, it is showing here because this field is the part of unique index on CustTable. Now click on the select button to specify a value for customer group parameter shown above. Following screen shot will be shown:

Select the customer group 30 and click OK button. Customer group 30 will be shown selected on the report dialog as shown below:

Again click the OK button on the report dialog to open the report filtered by customer group 30. Following output will be shown:

As you can see that, only those records are shown, which have customer group 30. In the same way you can apply as many filters as you want on the report AOT query to filter the report based on that field’s value.