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.

6-1

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

6-2

6-3

Create the Report

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

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

6-4

6-5

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.

6-6

  • 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.

6-7

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

6-8

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”.

6-9

  • 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”.

6-10

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-11

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.

6-12

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

6-13

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.

6-14

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

6-15

 

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

Advertisements

Published by

Girija Shankar Beuria

A software developer by profession with 10+ years of experience in the following technologies : Data Warehousing, Business Intelligence applications using SQL Server BI Stack, Product Frameworks and Test automation framework, MOSS , C# .Net, .NET, POWERSHELL, AMO, HTML 5, JavaScript, Reporting Service Web service, Dynamics AX, Dynamics AX 2012 BI Cubes, Dynamics AX 2012 SSRS Reports, SQL Azure, Windows Azure Web Services, ASP .NET MVC 4 Web API, WCF, Entity Framework, WPF, Excel Object Model, Windows 8 Apps, Windows Phone Apps

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s