Part 6 : AX SRS Reporting: Sample report scenario – Creating an AX SSRS Report with AX Query having SYS Query parameters only

Hi,

In this post we will walk through creating a sample report:

1. The report will be using an AX query for its data.

2. It will have query ranges as parameters (Sys Query parameters / Query contracts).

Pre-Requisite:

1. AX 2012 should be installed with Reporting extensions

2. SSRS should be installed.

3. VS tools should be installed

Let’s get started.

Creating the data source query

Since our report will use an AX query as data source type. We will first create the AX query. In this example we will use SalesTable to display the invoices details. We will use SalesId, DeliveryDate & SalesStatus as Query range parameters.

Open Dynamics AX development environment

1. Create a new project. I name it as “SysQueryOnlyRpt”.

2. In Project Right Click New –> Query.

3. A query gets added. Go to properties and change the name. I keep it as “SysQueryOnlyRptQry” .

4. Expand the Query node and on Data sources right click –> New Data source.

5. A data source get added. In properties window set Table property to Sales Table. Rename the Data source name to SalesTable also.

6. Expand the SalesTable, click on Fields node. In Properties set the Dynamics Propery to “Yes” from “Unselected”. This will show you all the fields now.

7. In the Ranges node right click –> New Range.

8. A Range field will get added. In the properties window set the Field to “SalesId” Column.

9. Repeat steps 7-8 to add two more ranges on field “SalesStatus” and “DeliveryDate”.

Our AX Query is now created and ready. Save the project.

Creating the project:

First, we will create a project.

1. Open Visual Studio.

2. Click File -> New Project.

3. New Project dialog opens. Click on Dynamics AX on left hand side menu and Report Model on Right hand side.

4. Give a name to the project. I will name it as “Query Reports” (as I will keep all query related reports in this project). Give a location also.

5. Click OK. A project is created

Create the Report

1. In the project created above right click Add –> Report

2. A report gets added. Rename that. I name it “SysQueryOnlyRpt”

3. In the report designer (Left hand side. Right click on Datasets à New Dataset.

4. In the properties window of newly created dataset:

• Set the name of data source. I will name it as “SyQueryOnlyRptDS”.
• Set the Data Source to “Dynamics AX”
• Set the Data source type to “Query”
• Ensure that the Default Layout is set to table. This is needed to create the auto design layout as table when we drag and drop the dataset.
• Ensure that Dynamic Filters is set to yes. This will ensure that the ranges we added in query are represented as Query contract (with Query dialog form) and not as individual UI parameters.
• In the Query Property click on ellipsis. A new Dialog should open. This dialog should list all the queries in AX. Select the one we created above for this report (SysQueryOnlyRptQry). Click Next.

• We should see list of fields, field groups, and Display methods available in query. For simplicity we will select SalesId, SalesNane, CustAccount, InvoiceAccount, CurrencyCode & SalesStatus (Label).

Note: Since SalesStatus is an AX enum there are two fields (One for the name of the enum and other for the label). We can choose either one (if that suffices our requirement) or choose both. In our case we just need to show the label so we can choose only one. In cases where you want to do filtration at report side you will need the name field too. We will look at this in future post.

• Click ok. The fields would now be imported to dataset.

Note: You should see many parameters added in the dataset parameters node.

• SyQueryOnlyRptDS_DynamicParameter : This is a parameter for the AX query that we have added.
• AX_PartitionKey: Framework added parameter for passing Partition key.
• AX_CompanyName: Framework added parameter for passing Legal entity id.
• AX_UserContext: Framework added parameter for passing user context values.
• AX_RenderingCulture: Framework added parameter for passing user AX rendering culture.
• AX_ReportContext: Framework added parameter for passing Report context values.

The same parameters would also be found under the “Parameters” node. If you look at properties of parameters under the “Dataset(SyQueryOnlyRptDS)” node you will notice that each of them is related the parameters under “Parameters” node. This is how the relationship is established. You cannot have a parameter under “Dataset(SyQueryOnlyRptDS)”node which is not related to a parameter under “Parameters” node.

Warning: Please avoid changing properties or deleting the Framework related parameters and/or Dynamics parameter.

5. We can now drag the Dataset (SyQueryOnlyRptDS) on the “Designs” node. This will create a Autodesign for the report. In the properties window :

• Set the name. I set it as “AutoDesign”.
• Set the layout template as “ReportLayoutStyleTemplate”.

• Expand the Autodesign node and under that click the “SyQueryOnlyRptDSTable” node. In properties window set Style Template as “ReportStyleTemplate”.
• You can move the fields up and down either by using controls on the menu or using Ctrl + UP/Down Arrow.

6. The report is now ready.

Save and deploy

The Report when created is saved to AOT in AX. But we will add the Project to AOT also. We will then deploy and test the report.

1. Build the Project

2. Right click the report project (QueryReports) in solution explorer à Add “QueryReport to AOT”. Wait and you should see a message at bottom of VS as “Add to AOT succeeded”.

3. Open AX development environment and open the project we have created (SysQueryOnlyRpt).

4. From AOT Under “Visual Studio Projects” –> “Dynamics AX Model Project”. Drag the “QueryReports” project and add to the AX project (SysQueryOnlyRpt).

5. From AOT Under “SSRS Reports” –> “Reports”. Drag the “SyQueryOnlyRpt” report and add to the AX project (SysQueryOnlyRpt).

6. Now right click on the “SyQueryOnlyRpt” in the project and Deploy. For prerequisite and deployment steps (also common errors) for any report please refer to this post.

7. We should get and info log for report deployed successfully.

View the report

For viewing the report we need to create a menu item bind it to the report. The menu item then can be used in menus, forms etc.

1. In the project (SysQueryOnlyRpt) we had created. Right click and New –> Menu Item

2. In properties window:

• Give a name. I name it as SysQueryOnlyRpt.
• You can give a label to it. I am just hardcoding this for now. I set it to “Sample Query report with SYS Query”   Note: For development purposes please create a label in label file and mention that here.
• Set a help text (again should be a label). I am leaving it blank.
• In Object Type, Select “SSRS Report” from the drop down
• In Object, select the report (SysQueryOnlyRpt) from the dropdown.
• In ReportDesign, select AutoDesign from the dropdown.

3. Now right click on the menu item and click ok. You should see an dialog.

4. You can see that the 3 ranges we added are shown as query ranges. Click on select and it should open another query form dialog, where we can set range values for filtration. Right now we will not set anything so click ok on SYS query dialog.

5. On the dialog click OK. Another dialog will open and the report will render.

 

You can now change the query ranges and play around and test the report to see if filtration works.

 

Hope this helps. In next post we will walkthrough more scenario(s).

– Girija

 

 

Disclaimer

Part 5 : AX SSRS Reporting: Report Development IDE

Hi,

In this post, let us walk through basics of AX SSRS Reports development IDE.

 

Pre-Requisite:

1. AX 2012 should be installed with Reporting extensions
2. SSRS should be installed.
3. VS tools should be installed

For detailed installation and deployment please follow the detailed MSDN article.

 

Creating an AX SSRS Report Project

Below are the steps to create a project:

1. Open Visual Studio.
2. Go to File à New Project. A dialog opens
3. Select Microsoft Dynamics AX on left hand side menu, on right hand pane select Report Model.
4. Give a name and select a Location. Click Ok. This should create the project

5.  To add a Report , Right click on project –> Add –> Report

6. Your report should be added now.

 

Report Development IDE Explained

Once you have added a report in a Report project you should see the following on the left hand side as shown in the screenshot above. These are:

1. Datasets: Any dataset(s) that are used in report. A single report can have one or more data sets from one or more sources.
2. Designs: Any design(s) that are used in the report. A report can have one or more designs.
3. Images: Any Image(s) that are used in report. Can Zero or more.
4. Data Methods: Any C# data methods that are used by report. Can be Zero or more.
5. Parameters: Any parameters that are used by report. These contains default framework parameters, Query parameter, Contract parameters, and UI parameters.

– Girija

 

 

Disclaimer

Part 4 : AX SSRS Reporting: Fundamentals of AX SSRS Reports development framework

Hi,

In this post we will walk through some basic concepts/terms for Dynamics AX SSRS reporting.

Report types definition

Broadly speaking reporting in AX 2012 can be classified into two types of reporting:

1. AX SSRS: Typically reports that are transactional in nature and consume data from Dynamics AX (through AX Query, RDP etc.) for data are referred to as AX SSRS reports.
2. AX OLAP: Typically reports that are analytical in nature and consume data from AX cubes (by MDX queries) are referred to as AX OLAP

Let’s go through them in detail:

In AX 2012 SSRS reporting there is a new framework available that developers have to use to create and run reports. Let’s go through some of the terms that we will be frequently using:

1. Query: Typically refers to AX query in case of AX transitional reports.
2. Report Data Provider (RDP): X++ data class used in AX SSRS reporting which can be used to retrieve data, process business logic.

Base class is “SrsReportDataProviderBase”. Any class that is defined as a Data Provider class for a report should inherit from this base class.

3. Query Contract: This is the query lookup in report dialog that shows the query ranges. This is handled internally by framework.
4. Report Data Contract (RDP contract): X++ class containing parameters definitions to be used by RDP for parameter value processing to be used in RDP business logic.

Any class defined as a contract should have the “[DataContractAttribute]” on top of class definition and “[DataMemberAttribute (<ParameterName>)]” on top of parameter properties.

5. RDL Data Contract: X++ class that can be used to access and modify design level parameters used in the report that are not part of RDP contract.

Base class is “SrsReportRdlDataContract”. Any class defined as controller class should inherit from the base class.

6. Report Controller: A class that can be used to control the report execution and validation. These classes can also be used to control the dialog and validations before & after the dialog is shown.

Base class is “SrsReportRunController”. Any class defined as controller class should inherit from the base class.

7. UI Builder: X++ class that can be used to design a report parameter dialog for custom look and feel. The same can be sued for any event handling that is required on the report dialog. Also can be used for adding custom lookups to the parameters too.

Base class is “SrsReportDataContractUIBuilder”. Any class defined as controller class should inherit from the base class.

8. Data Methods: A C# method that can be used in report data sets. This can contain retrieval mechanism logic as well as processing of business logic.
9. Enum Provider: An extension provided by framework which can retrieve the name/label data for an AX Enum.

 

In AX OLAP reports the only way of retrieving data is using MDX query. The MDX query is the same as used in SQL Server Analysis Services cubes. Detailed language reference for MDX can be found in MSDN.

– Girija

 

 

 

Disclaimer

Part 3 : AX SSRS Reporting : Data sources & types

Hi,

In Dynamics AX 2012 there are multiple data sources and data source types supported. In this post we will walk through them.

Data sources

AX 2012 SSRS reports supports two data sources. Data sources options are:

1. Dynamics AX
2. Dynamics AX OLAP

Dynamics AX:

This data source should be used if data is to be retrieved from Dynamics AX. There are various sub types (data source types) which can be used. In this option the data retrieval mechanism connects to the Dynamics AX instance and retrieves data.

 

Dynamics AX OLAP:

This data source should be used if data is to be retrieved from Dynamics AX cubes. In this option MDX query can be used to retrieve the data. In this option the data retrieval mechanism connects to the Dynamics AX cubes deployed on a SQL Server Analysis service (SSAS) instance and retrieves data.

 

Data source types

 

AX 2012 SSRS reports supports multiple data sources types. Data source types are used to define how the data is accessed in AX SSRS report from AX system. The options available are:

1. Query
2. Report Data provider
3. Business Logic
4. Enum Provider

 

The data source type to data source mapping is listed below:

	Dynamics AX	Dynamics AX OLAP
Query	Supported	Supported
Report Data Provider	Supported	Not Supported
Enum Provider	Supported	Not Supported
Business Logic	Supported	Not Supported

 

Query

A Query data source type is used if all the data retrieval logic can be achieved through a query.

In case of data source being Dynamics AX it will be an AX query (AOT query) or in case data source is Dynamics AX OLAP it will be MDX query. Typically this is the most efficient way of retrieving data. In case of AOT queries the tables (used in the AOT query) fields & Display Methods can be used for data retrieval.

In case of data source being Dynamics AX OLAP, a MDX query has to be written to retrieve the data. The MDX query can have parameters as well.

Report Data Provider

A Report Data Provider data source type is used when there is some business logic that you want to add after retrieving the data.

Not the most effective way of retrieving data but if logic has to be run this will be used.

Business Logic

A Business logic is typically used when you want to access a data source other than Dynamics AX / Dynamics AX OLAP. This is also used for drill through to forms.

Not the efficient way to retrieve data. Should be avoided where ever possible.

Enum Provider

An Enum provider is used when the requirement is to retrieve the values/label for an AX Enum. This is typically used to populate drop down selection in parameters.

Should be used to populate Enum parameter drop downs.

 

This MSDN article also mentions some parameters to consider before choosing a data source type.

– Girija

 

Disclaimer

Part 2 : AX SSRS Reporting : Deployment

Hi,

Continuation of my post on SSRS reporting,  in this post I would cover reports deployment in AX 2012.

In AX 2012 reports need to be deployed to Report server for user to be able to generate and view them. In this post we will cover the process and ways to deploy an AX SSRS reports. So lets get started.

Pre-Requisite:
1. AX 2012 should be installed with Reporting extensions
2. SSRS should be installed.
For detailed installation and deployment please follow the detailed MSDN article.

Deployment of Reports
Before you start the deployment of reports on Report Server please verify the configuration. To do so you can navigate to the following path in AX client:
System Administration — Setup — Business Intelligence — Reporting Services — Report Servers

Few things to check here:
1.  Check the URL (http://<servername>/Reports && http://<serverName>/ReportServer ) are running and accessible by browser.
2.  Click on the Validate Settings. If everything is good you should get success. If it throws an error :
a) If error is like “Ensure that reporting services is configured ….” : Please check the SSRS configuration and make sure it is up and running.
b) If error is like “The folder Dynamics AX was not found on the reporting server at the URL …” : In this error please click on the “Create Report Folder” button on the above form and create the folder. This will create the folder on Report Server.

There are two ways to deploy the reports:

1. AOT
2. PowerShell

1. To deploy report manually from AOT follow these steps:

a) Open AOT in AX development environment.
b) Expand the “SSRS Reports” node. Then expand the “Reports” node under it. This will contain all reports.
c) Navigate to the report you want to deploy. In this case I will deploy “CustTransList” Report. You can select more than one report also in the AOT.
d) Right click the selected reports and click “Deploy Element”

e) If deployed successfully you should get the success info log, else error (in case of any error).

2. To deploy reports through PowerShell follow these steps:

a) In the server go to Start  Administrative tools  Microsoft Dynamics AX 2012 Management Shell. This opens a PowerShell window and imports some namespaces.
b) In the console you can write the command “Publish-AXReport –ReportName <ReportName(s)>”. The ReportName(s) can be one report AOT name or multiple report AOT names (comma separated). In this example we will deploy CustTransList report, so command will be “Publish-AXReport –ReportName CustTransList”. Press enter.

Note:  In cases where UAC is enabled we might get error for “UAC enabled …” In those cases please open AX Client or PowerShell (Whatever you are using for deployment) as administrator.

– Girija

 

Disclaimer

Part 1 : AX SSRS Reporting : Introduction

Hi,

In the very first post  of the series will cover the basic of SSRS reporting.

In AX 2012 (all versions and releases) SQL Server Reporting Services is used as basic reporting platform. The architecture is quite different from the last version (AX 2009) and is much more flexible and supports a lot more data retrieval mechanism. The programming model is also enhanced to cater to the various scenarios and report types.

For understanding and developing reports in AX 2012, a good to have pre-requisite would be:

1. SSRS reporting development

3. AX knowledge

2. X++

Reporting Architecture

The reporting architecture is very clearly explained in this MSDN article. I will not go into detailed explanation of the architecture but two quick basic points to keep in mind would be:

1. There is a custom extension that is used for data retrieval. So essentially this extension is same as any other out-of-box extension that SSRS already has (e.g.: SQL Server, SQL Server Analysis Services, Oracle, etc…). This extension gets available when we install Reporting services extensions using the AX 2012 Setup.
2. Services are available which ensure the connectivity between SSRS server and AX. The specific service is “Services”. This can be found in AX client under System Administration->Services and Application Integration Framework->Inbound ports. Note: Always ensure that this service is deployed and running.

I will cover the deployment in the next article.

– Girija

 

 

Disclaimer