AX2012 Create SSRS Report MVC

RDP implments the standard MVC(Model View Controller) design pattern.
In this post I am building one report which is used in the production packing list report for production module.
What is MVC?

• Model-view-controller (MVC) is a pattern used to isolate business logic from the user interface.
• Model: Responsible for retrieving data and for business logic, this can included queries, data methods, or other classes that are designed toretrieve data.
• View: Responsible for the User Interface, this can also be thought of as the design for the report.
• Controller: Orchestrates the flow between Model and View

Microsoft Dynamics AX 2012 Reporting: How to run reports that executes longer than 10 minutes

Sometime ago we run into problem when reports that executes longer than 10 minutes are running into timeout error. To overcome this problem:

- For reports printed on screen job you need to install kernel hotfix KB 2642168 - the KB article is still not published on Partner source but you should be able to get hotfix through Microsoft Dynamics AX Support team

- For reports printed on screen or in batch. You need to:

1. Open configuration:
1. If a configuration file has been created for reporting services according tohttp://technet.microsoft.com/en-us/library/hh389774.aspx (make sure it’s in the right folder!), open that file in Microsoft Dynamics Ax Configuration Utility
2. If no such configuration file has been created, open the Microsoft Dynamics Ax Configuration Utility configuration - change configuration target to Business Connector (non-interactive use only) - this configuration is stored inHKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Dynamics\6.0\Configuration\
2. On the Connection tab, select Configure Services
3. Select the service binding to modify under the Bindings node (QueryServiceEndpoint in this case) and tweak the SendTimeout or ReceiveTimeout parameter as necessary. SendTimeout is the one that needs tweaking:
4. 
5. Restart SQL Reporting Services and Microsoft Dynamics Ax Client

How To: Addressing SSRS Session Timeouts

Dealing with Dynamics AX 2012 Reporting Timeouts and Thresholds

Dynamics AX 2012 uses SQL Server Reporting Services for rendering reports. SSRS gets the data from AOS by using a custom SSRS Extension that uses WCF to communicate with AOS.

Depending on the size of the data and the complexity of the report, it might take a long time for the report to execute, resulting in various timeout and other thresholds being hit, which might cause the report rendering to fail. This article attempts to identify all the places where rendering large reports may cause thresholds could be hit and suggest tweaks or workarounds to address them.

A. Getting data ready

If the report uses Report Data Provider (RDP) to get the data, then it should be modified to use a pre-processed RDP class as the data source to invoke processing logic before a call is made to Reporting Services. For more information about RDP classes, see Using Report Data Provider Classes to Access Report Data and Report Programming Guide.

B. Report Execution Timeout

SSRS defines a Report Execution Timeout, which specifies the number of seconds after which the reporting processing times out. The default value for this is 30mins. If the report execution takes longer than that, then the report execution will fail.

This setting can be updated at the Report Server level or at an individual report level

Site level Settings

The report execution timeout at the report server level should be set to a value greater than the time required for the largest report to render. Alternately it can be set to never time out. This can be done in one of two ways -

1. Using the Report Manager - From the Site settings, the Report Timeout property can be changed.

2. This can also be changed using SQL Server Management Studio – In SSMS, Right-click the name of a report server, then click Properties. On the Server Properties window, click the Execution page and change the value for “Limit report execution to the following number of seconds”.

Report level Settings

The report execution timeout can also be set on each report, using Report Manager. Go to the report properties (see how) and in Processing Options, select either “Do not timeout report” or change the “Limit report processing to the following number of seconds” option.

C. SSRS Session Timeout

SSRS maintains a User Session which may time out if the report takes a long time to execute, causing the report execution to fail. This can be fixed for the report server by setting the 2 properties SessionTimeout and SessionAccessTimeout using the rs.exe tool. Again, these should be set to be greater than the time taken to render the largest report.

You should configure these values to be no less than the time it takes to render your largest report. Here is a sample script for rs.exe which will set these values for you:

Public Sub Main()
    Dim props() as [Property]
    props = new [Property] () { new [Property](), new [Property]() }
    
    props(0).Name = "SessionTimeout"
    props(0).Value = timeout
    
    props(1).Name = "SessionAccessTimeout"
    props(1).Value = timeout
    
    rs.SetSystemProperties(props)
End Sub

You can run this script with the following command:

rs.exe -i <Path to SessionTimeout.rss> -s <Report Server URL> -v timeout="6000"

The tool rs.exe is usually located at “c:\Program Files(x86)\Microsoft SQL Server\110\Tools\Bin”.

The timeout is expressed in seconds, so this example sets the SessionTimeout and SessionAccessTimeouts to about an hour and a half. 

Example: c:\Program Files(x86)\Microsoft SQL Server\110\Tools\Bin\rs.exe -i c:\Temp\sessiontimeout.rss -s http://localhost/reportserver -v timeout="6000"

Important: Do this with caution, keeping a session around longer than necessary can cause your ReportServerTempDB database to grow larger since temporary session snapshots will not be aged out as often.  Also, this utility must be run as Administrator.

You can also check out this msdn blog post for more information.

D. WCF Timeouts and Thresholds

SSRS uses the Query Service (which is a WCF service exposed by the AOS) to get data. For reports with large datasets, the default WCF configuration may cause WCF to hit some thresholds at runtime. So the WCF configuration can be tweaked as follows -

Server side settings –

- Open the Ax32Serv.exe.config file (it is typically under c:\Program Files\Microsoft Dynamics AX\<version>\Server\MicrosoftDynamicsAX\Bin).

- Locate the QueryServiceBinding element. The default value for the sendTimeout on this element is 10mins.

- Increase the sendTimeout to a larger value, say 30mins, like so – sendTimeout=”00:30:00”

Client side settings –

- Create a new local client configuration using the Microsoft Dynamics AX 2012 Configuration as explained here.

- On the Connection tab, click on “Configure Services” to open the SVC configuration utility.

- Navigate to Bindings -> QueryServiceEndpoint (netTcpBinding).

Update the values for the following properties -

o SendTimeout – This is set to 10mins by default. Increase it to a larger value, like 30 mins.

o MaxReceiveMessageSize – This is set to 2147483647 by default. Increase it to double that value or 4294967295. The maximum allowed value is Int64.MaxValue.

SSRS Report Print in ax 2012

public static void print_skilt(ItemId ID) 
{ 
SrsReportRunController controller; 

controller = new SrsReportRunController(); 

controller.parmLoadFromSysLastValue(false); 

controller.parmReportName("mi_prisskilt.a5"); //name of report to print 

controller.parmReportContract().parmPrintSettings().landscape(true); 
controller.parmReportContract().parmPrintSettings().printMediumType(SRSPrintMediumType::File); 
controller.parmReportContract().parmPrintSettings().fileFormat(SRSReportFileFormat::PDF); 
controller.parmReportContract().parmPrintSettings().overwriteFile(true); 
controller.parmReportContract().parmPrintSettings().fileName(strFmt("\\\\shop3\\AxaptaPDF\\P%1.pdf",ID)); //filename to print pdf to 
controller.parmReportContract().parmPrintSettings().fromPage(1); 
controller.parmReportContract().parmPrintSettings().toPage(1); 

controller.parmReportContract().parmRdlContract().setValue("mi_prisskilt_ItemId",ID); //parameters to send to report 

controller.runReport(); 

}

Call SSRS in ax 2012

static void callSSRS(Args _args) 
{ 
SrsReportRun srsReportRun; 

srsReportRun = new SrsReportRun ("vend.Report"); 
srsReportRun.init(); 
if ( srsReportRun ) 
{ 
srsReportRun.executeReport(); 
} 
} "

Create SSRS Report in AX 2012

Create Query Base SSRS report in Dynamics AX 2012:-

Today, let us learn how to quickly create report models and report in Visual studio and add to AOT[Dynamics AX 2012] and see a running report.

Some information:

SQL Server Reporting Services is the primary reporting platform for Microsoft Dynamics AX. Reporting Services is a server-based reporting platform that includes a complete set of tools to create, manage, and deliver reports, and APIs that enable you to integrate or extend data and report processing in custom applications. Reporting Services tools work within the Microsoft Visual Studio environment and are fully integrated with SQL Server tools and components.

In a Visual Studio reporting project for Microsoft Dynamics AX, you can define a report in a report model. A report consists of a collection of items, such as datasets, parameters, images, and report designs. A model can contain more than one report.

Also, for this post I am assuming that all report services are configured in the system.

Let us create a query in AOT as a dataset source for our report.

Create a new query by name – “SR_InventTableQuery” and add InventTable as datasource and add ItemId range to it. I will let you know the significance of adding the range as I proceed further..

So, your new query should like below :

Please note: we can use already existing queries which are in AOT for report as datasource, for better understanding I have created new query above.

Lets proceed further. Open visual studio 2010 and lets us create a new Dynamics AX project.

Once visual studio is launched >> click on file menu >> New project as shown below

Select Microsoft Dynamics AX from the installed templates >> report model and name the model as SR_ReportNewModel as shown below

Now let us add a new report to the newly created report Model as shown below. Right click on the SR_ReportNewModel from the solution explorer, Add >> Report

Rename the report to SR_InventTable by right click and rename option on the newly added report.

Then we need to add the dataset to the newly created report. Right click on the datasets node and chose the option New datset. Rename it to InventTable and go to query property and click on the ellipsis (…) button to select the query which we have created as shown below [picture explains better than 1000 words]

It will open with list of Dynamics AX Queries from which we should select our query “SR_InventTableQuery” and click on next button as shown below

Now , you can select the list of fields and display methods you want to see on your report.
I have selected few fields form the fields and also couple of display methods like site Id and location Id as shown below and click on Ok Button.

wow..There you go… we are done with the datasets part and lets work on the design part real quick now…

Its very simple..Select the InventTable dataset and drag and drop on to your designs node as shown below. It will create autodesign for you 

In my case, when I expand the designs node, i see my fields and the data methods added in the data nodes. we will look in to other nodes in detail later.

Well there are now some important [not mandatory] properties but beautification properties which make your report look good with style. Once you expnad the designs node, you will find InventTableTable with the dataset name. Right click on it and go to properties and set the style template to “TableStyleTemplate” as shown below.

On to autodesigns, we also need to set an important property called Layout Template – set it to ReportLayoutStyleTemplate as shown below

Now, lets switch to parameters node in the report. If you expand the parameters node , you will find some parameters. Lets work on AX_CompanyName parameter. By default it is hidden. Lets unhide or make it visible it as we want to display the items based on the company [dataaread id] selection by the user.

See the screen shot below

wow..we are getting closer. Now we can preview the data by right clicking the autodesign and by chosing option preview as shown below

Note: you can select the company parameter and click on the report tab to view the report. But our main aim is to deploy back this report model to AX.

To deploy the report to AOT, we have a very simple option. Right click on the SR_ReportNewModel from the soultion explorer and select option Add SR_ReportNewModel to AOT as shown below.

We are done with visual stuido development part. Now lets us switch to AX and see whether the report model has been saved to AOT or not. To do so, open your AX client and ogo to AOT >> Visual studio projects >> Dynamics AX Model projects . you should see SR_ReportNewModel project.

Also, In AOT >> SSRS Reports >> Reports >> you should see SR_InventTable report.

Now we are left out with final thing, creating menuitem for this report. This can be easily done by following hthe below process.

Go to AOT >> Menu items >> Output >> Right click and Select New Menu item and set the following properties as shown below.

Cool.. we are done and hope you are excited to view the report now. Well you can add this menu item to relevant menu and I hope you knw this process.

Now , lets open the report, Right click on the newly created menu item and select open.[You should see the following as shown below]

Since we have made the company parameter visible- we have option of generating the report based onthe dataareaid and since we have added range ItemId to the query – we get twow ranges as shown above.

I have selected ceu as my company and I am leaving ItemId as blank to view all the items in the report. Now lets run the report and see how it renders the data. [Below is the report]

Note: AX uses SysOperationTemplateForm and SysOperationDialog classes for this report integration. we shall look in to details in next posts.

What to do: Only integrated security is supported for AX queries ssrs reports in ax 2012

In Dynamics Ax 2012, Microsoft SQL Server Reporting Services (SSRS) is the primary reporting platform. So all the default preconfigured reports that are shipped with Microsoft Dynamics Ax run on the Reporting services platform. 

And therefore, the below installations should be done perfectly:

1. SQL Server Reporting Services (SSRS - Part of SQL Installation)
2. Reporting Server extensions (Business Intelligence components of Microsoft Dynamics Ax)

If your installation went wrong somewhere, then there is a chance that you might get the error:Only integrated security is supported for AX queries when trying to open an report in Ax 2012.

Or you might see that the report will be opened, but you will be asked to enter the username and password to proceed further. Below is the related screenshot:

Solution if error occurs for a single report:

1. Identify the Report name
2. Open Reporting Services Configuration Manager ( All Programs > Microsoft SQL Server 2008/2012 > Configuration Tools > Reporting Services Configuration Manager)
3. Go to "Report Manager URL" Tab and click on URL
   Typically, URL will be in the format http://<ServerName>:80/Reports
4. Select the particualr Report under DynamicsAx Folder
   
5. In the opened page, select Data Sources on the left pane.
   And apply Windows integrated security and click Apply.
   
6. Now the report should be opened automatically without any error message and also shouldn't ask for any username and password.

Solution if errors pops up for all SSRS Reports:

You can still apply the above approach for all the reports and it will work. But that is not the right approach for obvious reason, will take a lot of time to do that. 
More appropriate solution would be to delete all the reports and redeploy all the reports. Steps are as follows:

1. First step is to delete all the existing SSRS Reports. For doing this you can navigate to DynamicsAx folder and delete the entire folder.
   
2. Then go to Report servers form in Dynamics Ax 2012 and click on "create report folder" button to create the Folder DynamicsAx again.
   
3. Now you can open Powershell (Administrative Tools > Microsoft Dynamics Ax 2012 Management Shell). Make sure you run Powershell as Administrator.
4. Deploy all the reports with the help of command:
   Publish-AXReport -ReportName *
5. And once, all the reports are deployed you should be good to go.

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 theDatasets & 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 selectedCustTableSRS 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 byRight 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. 

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

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. 

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!

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.