Ax 2012 SSRS Reporting concepts

The SSRS reporting concepts are very well explained in the below link, you can go through the same for a good understanding of SSRS with Dynamics Ax.

Below are few important related points listed and also the Reporting Services Overview slide.



Reporting services overview

The picture is self explantory and just to brief things up:

• Basically a Report Model can contain Reports, Layout Templates, Table Style templates, Report Database etc..
• And nodes which we can find under Report/Report Model are Datasets, Desgin, Parameters, Data methods and Images.
• In Ax terms, a Datasets can be said to be a data source and collection of fields from data source.
• And basically an Ax Query, SQL Data, OLAP data (cube) and a Data method can act as a Data source for a report
• Design node of a Report/Report model will contain three major parts, namely, Header, Body and Footer.
• And the Desgins can be of two types, namely Auto Design and Precision design.
  Dynamics Ax will take of the conversion form the Designs to RDL files, the format in which reports are stored in the SQL Database.

Internal links:
SSRS Ax 2012: How to create a simple SSRS report - Link
SSRS Ax 2012: How to design reports with built-in templates - Link

Information collected from:
AX2009 SSRS 4 AX SSRS Reporting Concepts - Link

SSRS Ax 2012: How to design reports with builtin templates

We have already created a very simple and basic report. For more details look at the Internal links listed below. And now we are going to make our report look better.

And the simplest way to have the reports to have standard look and layout is "to set AutoDesign's several properties"
AutoDesign has a property called Layout template, and
DataRegion (CustTable) has a property called Style template.
By modifying these two properties we can actually have a better look and feel for our report.

Layout Template:
This can be found under properties for AutoDesign node. When I click the comboBox to see the options avaliable for LayoutTemplate, I see the below and I choose "ReportLayoutStyleTemplateNoCompany".



Lets see what changes does it make to my report.
The report looks much better now. The Header and the Footer of the template have been automatically designed to somewhat similar to standard Ax reports.

Style Template:
This can be found under properties for the DataRegion node (CustTable in this case). And when I click the comboBox to see the options avaliable for StyleTemplate, I see the below options and I choose "TableStyleAlternatingRowsTemplate"

Lets see what changes does it make to my report.
The report looks even better now. The data grid which was not formatted in the above screenshot, looks much better now and very well readable.

Remember, the templates which we used will mostly make changes to the Font of the text in the report, also text colours.. but doesn't manage the column widths, we will see how it is done in the next posts.
And so this is how we can design the reports which we create using the built-in templates. Good luck!

Internal links:
SSRS Ax 2012: How to create a simple SSRS report - Link

Information collected from:
AX2009 SSRS 3 Better Looking Reports with Templates - Link

SSRS AX 2012: Why and how is a Parameter added to report

If we look at the Simple SSRS report we created using dataset CustTableSRS (use below internal link for more details), we observe that while rendering the report - we had to enter a value in the parameters tab. i.e, Value for CustGroup.
So how and why does this value appear?

How does this parameter appear:
The answer can be found if we go to AOT in Dynamics Ax Client. Open the query CustTableSRS which we used as a Dataset for our report.
In the CustTableSRS AxQuery, we find several nodes. Lets concentrate on the DataSources node.

And in the Datasources node, we can find the CustTable from which the data into the report has be rendered from. And we can also see the Fields, AccountNum & CustGroup, which were displayed in the report.

Now if you look at Ranges, we see that CustGroup lies here. That means, the query has a range and is defined by CustGroup.
And this is how the CustGroup parameter is shown and is required while rendering the report.

Why does the parameter appear:
The basic reason, or should say purpose of the CustGroup parameter is - it acts as a filter. If you select CustGroup as 20, all the records with CustGroup as 20. And if you select any other value in CustGroup, all the related fields are shown.

But the actual and main reason for the need of parameter in report is a performance and efficiency reason. Because, we could have defined our own filter in the report to filter the data to any particular scenario. But then what we would have done is we would load all the data and then filter on that data that is on the SSRS server.
But by using the range on the query, means that, the AOT transfers the data after applying the range on the data and then send it to the SSRS server to be rendered on the report. So that way less data is transferred across. So that is the advantage and proper use of Ranges, a.k.a parameters for reports.


Internal links:
SSRS Ax 2012: How to create a simple SSRS report - Link

Information collected from:
AX2009 SSRS 2 The Case of the Mysterious Parameter - Link

SSRS Ax 2012: How to create a simple SSRS report

This is an attempt to create the simplest SSRS report for Dynamics Ax.
And to start with, lets see what all tools I am using.

• Microsoft Dynamics Ax 2012 R2
• Visual studio 2010 Ultimate
• Visual studio tools (Dynamics Ax Component)
• Microsoft SQL 2008 R2

Steps to create the simplest SSRS Report:

1. Launch Visual studio.
   Create a new project and select Template "Microsoft Dynamics Ax" and select Report Model.
2. And in the Solution explorer, you will find a ReportModel.
   You will need to Add report, by clicking the Add button shown in the below image and then selecting Report. Then you will see a new report "Report1" as shown.
3. If you double click the Report1. Then you will see something like a part of AOT in Dynamics Ax as shown.
4. The nodes available under the Report1 are Datasets, Desgins, Images, Data methods & Parameters. And for a simple SSRS report, our task will be to create something under the Datasets & Designs and then bind the two.
   Dataset - would be a tabular set of data that we will get from Ax. We will use Ax queries to get the data for this example.
   And Design is a layout - a page where we will put in the design and map the dataset to a table or something.
   And we should be able to generate a report. Not so beautiful report but a simple SSRS report to start with.
5. To create a new dataset, Right click on the Datasets node and Add Dataset. Rename the newly created Dataset to any appropriate name.
6. And in the properties window of the newly created dataset, set the query to an Ax Query by using which the report will actually fetch the data and display. We have bunch of other options which can be customized as well.
   For selecting query, click on the ellipses and then select appropriate Ax Query ( I selected CustTableSRS and then click next to navigate to a window to select fields needed (illustrated in below figure)
7. Select all fields and click ok. And you will find a query automatically populated in the Query property of Report1. Something like, SELECT CustTable.1.AccountNum,CustTable.1.CustGroup FROM CustTableSRS.
   And also the fields will shop up under the Fields node.
8. So now we have the dataset populated and fields shown in place. The next step would be to create the design.
9. And the simplest way to create a design is to drag the dataset into the Designs node. By doing so, you will actually generate an Auto-Design "AutoDesign1"
10.  And once the AutoDesign is created you are ready to go. You can render the report by Right clicking the AutoDesign1 > and click Preview.
11. In the opened Report1 preview, enter the parameters needed. In this case, select some value for the CustGroup, say, * (to select all values).
12. And click Report Tab, to view the report as below.

So that is how we can create a report, a very simple, basic and a quick report using Visual studio 2010 for Dynamics Ax 2012. 

Information collected from:
AX2009 SSRS 1 Create the Simplest Possible Report with Visual Studio – Link