This chapter will cover the following topics:
Using a query as a data source in a report
Creating auto designs from datasets
Grouping in reports
Adding ranges to the report
Deploying a report
Creating a menu item for a report
Creating a report using a query in Warehouse Management
Reports are a basic necessity for any business process, as they aid in making critical decisions by analyzing all the data together in a customized manner. Reports can be fetched in many types, such as ad-hoc, analytical, transactional, general statements, and many more by using images, pie charts, and many other graphical representations. These reports help the user to undertake required actions. Microsoft SQL Reporting Services (SSRS) is the basic primary reporting tool of Dynamics AX 2012 R2 and R3.
This chapter will help you to understand the development of SSRS reports in AX 2012 R3 by developing and designing reports using simple steps. These steps have further been detailed into simpler and smaller recipes. In this chapter, you will design a report using queries with simple formatting, and then deploy the report to the reporting server to make it available for the user. This is made easily accessible inside the rich client.
Microsoft SQL Server Reporting Services (SSRS) is the most important feature of Dynamics AX 2012 R2 and R3 reporting. It is the best way to generate analytical, high user scale, transactional, and cost-effective reports. SSRS reports offer ease of customization of reports so that you can get what you want to see. SSRS provides a complete reporting platform that enables the development, design, deployment, and delivery of interactive reports. SSRS reports use Visual Studio (VS) to design and customize reports. They have extensive reporting capabilities and can easily be exported to Excel, Word, and PDF formats.
Dynamics AX 2012 has extensive reporting capabilities like Excel, Word, Power Pivot, Management Reporter, and most importantly, SSRS reports. While there are many methodologies to generate reports, SSRS remains the prominent way to generate analytical and transactional reports. SSRS reports were first seen integrated in AX 2009, and today, they have replaced the legacy reporting system in AX 2012.
SSRS reports can be developed using classes and queries. In this chapter, we will discuss query-based reports. Reports using classes will be discussed in later chapters.
In query-based reports, a query is used as the data source to fetch the data from Dynamics AX 2012 R3. We add the grouping and ranges in the query to filter the data. We use the auto design reporting feature to create a report, which is then deployed to the reporting server. After deploying the report, a menu item is attached to the report in Dynamics AX R3 so that the user can display the report from AX R3.
Through the recipes in this chapter, we will build a vendor master report. This report will list all the vendors under each vendor group. It will use the query data source to fetch data from Dynamics AX and subsequently create an auto design-based report. So that this report can be accessed from a rich client, it will then be deployed to the reporting servicer and attached to a menu item in AX.
Here are some important links to get started with this chapter:
Install Reporting Services extensions from https://technet.microsoft.com/en-us/library/dd362088.aspx.
Install Visual Studio Tools from https://technet.microsoft.com/en-us/library/dd309576.aspx.
Connect Microsoft Dynamics AX to the new Reporting Services instance by visiting https://technet.microsoft.com/en-us/library/hh389773.aspx.
Before you install the Reporting Services extensions see https://technet.microsoft.com/en-us/library/ee355041.aspx.
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.
To work through this recipe, you will require AX 2012 R2 or AX 2012 R3 rich clients with developer permission.
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.
Open the AX Development Workspace (Ctrl + D).
Go to AOT | Queries and add a new query.
Rename the query to
Go to query's data source node and add the new data source.
Rename the data source to
VendTableand set property table to
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.
Now open Visual Studio.
Navigate to File | New | Project.
In the new project dialog, select Microsoft Dynamics AX, and then Report Model.
Set the name as
Rename the report as
Now open the report by double-clicking on it in Solution Explorer.
Rename the dataset as
Now, right-click on the VendorMaster dataset and select Properties.
- Click on the ellipsis (…) button in Query.
Select the PKTVendorDetail query from the list and click on the Next button.
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.
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.
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.
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.
In auto design, there are layouts that are already built in, and we can choose one in which we want to show the data, based on our preferences.
To develop reports in Dynamics AX 2012 R3, you need Visual Studio, through which you can design, develop, and deploy the reports. SQL Reporting Services must be properly installed and configured. You must also have access to the reporting manager to manage and see the reports present in AX 2012 R3.
There are some standard report templates in AX. You can choose any of them for pre-designed layouts.
Right-click on the Designs node, select Add, and then select Auto Design. This will create a new auto design under the Design node. Rename it as
Right-click on Auto design (VendorMaster) and select Preview. This will show a preview of the report.
Using the right-click menu options, such as Move to Top, Move Up, Move Down, Move to Bottom
Using shortcuts, like Alt + Up/Down arrow
The new format will look like the following screenshot:
Auto design is much easier to design and develop when compared to precision design. The default type (table/chart) for auto design is defined in the properties of the dataset. The default type determines what kind of control is added when the dataset is dragged and dropped into the auto design node.
Templates: Templates are responsible for printing the header, footer, and company name on a report. They also manage the font and colors. Currently, AX does not support printing the company image in the header through auto design.
Grouping means putting things into groups. In the previous recipe, all the data shown in the report was listed sequentially. Grouping data simplifies the structure of the report and makes it more readable. It also helps you to find details, if required.
We can group the data in the query as well as in the auto design node in Visual Studio. In this recipe, we will structure the report by grouping the VendorMaster report based on the VendorGroup to make the report more readable.
In this recipe, we will add fields under the grouping node of the dataset created earlier in Visual Studio. The fields that have been added in the grouping node will be added and shown automatically in the SSRS report.
Go to Dataset and select the VendGroup field.
Drag and drop it to the Groupings node under the VendorMaster auto design.
This will automatically create a new grouping node and add the VendGroup field to the group. Each grouping has a header row where even fields that don't belong to the group but need to be displayed in the grouped node can be added.
The user can filter the data in a report on the basis of the field added as a range. The range must be specified in the query. In this recipe, we will show how we can filter the data and use a query field as a range.
In this recipe, we will add the field under the Ranges node in the query that we made in the previous recipe. By adding the field as a range, you can now filter the data on the basis of VendGroup and show only the limited data in the report.
Open the PKTVendorDetails query in AOT.
In the Visual Studio project, right-click on Datasets and select Refresh.
Under the parameter node, VendorMaster_DynamicParameter collectively represents any parameter that will be added dynamically through the ranges. This parameter must be set to true to make additional ranges available during runtime. This adds a Select button to the report dialog, which the user can use to specify additional ranges other than what is added.
Right-click on the VendorMaster auto design and select Preview. The preview should display the range that was added in the query. Click on the Select button and set the VendGroup value to
10. Click on the OK button, and then select the Report tab, as shown in the following screenshot:
Save your changes and rebuild the report from Solution Explorer. Then, deploy the solution.
The report dialog uses the query service UI builder (that we will cover in later chapters) to translate the ranges and to expose additional ranges through the query.
Dynamic parameter: The dynamic parameter unanimously represents all the parameters that are added at runtime. It adds the Select button to the dialog from where the user can invoke an advanced query filter window. From this filter window, more ranges and sorting can be added. The dynamic parameter is available per dataset and can be enabled or disabled by setting up the Dynamic Filters property to True or False.
SSRS, being a server side solution, needs to deploy reports in Dynamics AX 2012 R3. Until the reports are deployed, the user will not be able to see them or the changes made in them, neither from Visual Studio nor from the Dynamics AX rich client.
Reports can be deployed in multiple ways and the developer must make this decision. In this recipe, we will show you how we can deploy reports using the following:
Microsoft Dynamics AX R3
Microsoft Visual Studio
In order to deploy reports, you must have the permission and rights to deploy them to SQL Reporting Services. You must also have the permission to access the reporting manager configuration.
Before deploying reports using Microsoft PowerShell, you must ensure that Windows PowerShell 2.0 is installed.
Microsoft Dynamics AX R3:
Reports can be deployed individually from a developer workspace in Microsoft Dynamics AX.
SSRS reports can be deployed by using the developer client in Microsoft Dynamics AX R3.
In AOT, expand the SSRS Reports node, expand the Reports node, select the particular report that needs to be deployed, expand the selected report node, right-click on the report, and then select and click on Deploy Element.
The developer can deploy as many reports as need to be deployed, but individually.
Reports can be deployed for all the translated languages.
Microsoft Visual Studio:
This is used to deploy the default reports that exist within Microsoft Dynamics AX R3.
Open Windows PowerShell and by using this, you can deploy multiple reports at the same time.
Visit http://msdn.microsoft.com/en-us/library/dd309703.aspx for details on how to deploy reports using PowerShell.
To verify whether a report has been deployed, open the report manager in the browser and open the Dynamics AX folder. The PKTVendorDetails report should be found in the list of reports.
Report deployment is the process of actually moving all the information related to a report to a central location, which is the server, from where it can be made available to the end user. The following list indicates the typical set of actions performed during deployment:
The RDL file is copied to the server.
The business logic is placed in the server location in the format of a DLL.
Deployment ensures that the RDL and business logic are cross-referenced to each other.
This recipe will tell you how to create a new menu item for a report and set the major properties for it. Also, it will teach you to add this menu item to a module to make it available for business users to access this report.
You can create the new menu item under the Menu Item node in AOT. In this recipe, the output menu item is created and linked with the menu item with SSRS report.
Go to AOT | Menu Items | Output, right-click and select New Menu Item. Name it PKTVendorMasterDetails and set the properties as highlighted in the following screenshot:
Open the Menu Item to run the report. A dialog appears with the Vendor hold and Group ranges added to the query, followed by a Select button. The Select button is similar to the MorphX reports option where the user can specify additional conditions. To disable the Select option, go to the Dynamic Filter property in the dataset of the query and set it to False.
The report viewer in Dynamics AX is actually a form with an embedded browser control. The browser constructs the report URL at runtime and navigates to the reports URL. Unlike in AX 2009, when the report is rendering, the data it doesn't hold up using AX. Instead, the user can use the other parts of the application while the report is rendering. This is particularly beneficial for the end users as they can proceed with other tasks as the report executes.
The permission setup is important as it helps in controlling the access to a report. However, SSRS reports inherit user permission from the AX setup itself.
In Dynamics AX 2012 R3, Warehouse Management is a new module. In the earlier version of AX (2012 or R2), there was a single module for Inventory and Warehouse Management. However, in AX R3, there is a separate module.
AX queries are the simplest and fastest way to create SSRS reports in Microsoft Dynamics AX R3. In this recipe, we will develop an SSRS report on Warehouse Management.
In AX R3, Warehouse Management is integrated with bar-coding devices such as RF-SMART, which supports purchase and receiving processes: picking, packing and shipping, transferring and stock counts, issuing materials for production orders, and reporting production as well. AX R3 also supports the workflow for the Warehouse Management module, which is used to optimize picking, packing, and loading of goods for delivery to customers.
To work through this recipe, Visual Studio must be installed on your system to design and deploy the report. You must have the permission to access all the rights of the reporting server, and reporting extensions must be installed.
Similar to other modules, Warehouse Management also has its tables with the "WHS" prefix. We start the recipe by creating a query, which consists of WHSRFMenuTable and WHSRFMenuLine as the data source. We will provide a range of Menus in the query. After creating a query, we will create an SSRS report in Visual Studio and use that query as the data source and will generate the report on warehouse management.
Open AOT, add a new query, and name it
Add a WHSRFMenuTable table.
Go to Fields and set the Dynamics property to Yes.
Add a WHSRFMenuLine table and set the Relation property to Yes. This will create an auto relation that will inherit from table relation node.
Go to Fields and set the Dynamics property to Yes.
Now open Visual Studio and add a new Dynamics AX report model project. Name it
Add a new report to this project and name it
Add a new dataset and name it
Select the PKTWarehouseMobileDeviceMenuDetails query in the Dataset property.
In the properties, set the layout property to ReportLayoutStyleTemplate.
Now preview your report.
When we start creating an SSRS report, VS must be connected with Microsoft Dynamics AX R3. If the Microsoft Dynamics AX option is visible in Visual Studio while creating the new project, then the reporting extensions are installed. Otherwise, we need to install the reporting extensions properly.