Running AX report through batch job in AX2009 (batch printing)

Running batch report in AX2009 (batch printing)

Since the change of AX2009 batch framework, printing of report through batch can be done through two methods:
- Printing from server (to printer or file)
- Printing from client (the legacy batch processing)
Prerequisites: For both of the method, there're some mandatory setup:

1. Enable any of the AOS that you want to make it as batch AOS
   (Administration -> Setup -> Server configuration)
2. Create batch group for printing.
   Eg. One for client printing and one for server printing.
   (Administration -> Setup -> Batch groups)
3. Add the batch group created in #2 into the AOS enabled for batch processing (in #1)

complete article available here

Walkthrough: Creating a Report Bound to a Report Data Provider Class (X++ Business Logic) [AX 2012]

In this walkthrough, you use a report data provider (RDP) class with business logic to process data and then display the outcome of the business logic on a report.

An RDP class is an X++ class that is used to access and process data for a Reporting Services report. The options for a data source type for a Microsoft Dynamics AX report are query, business logic, and RDP. An RDP class is an appropriate data source type when the following conditions are met.

1. You cannot query directly for the data you want to render on a report.
2. The data to be processed and displayed is from Microsoft Dynamics AX.

The following illustration is a preview of the report that you create in this walkthrough.

Note

The data that displays in your report may vary depending upon the sample data that is available to you.

The following elements are required to set RDP as your data source type.

• Temporary table – RDP class fills a temporary table with data that will be used by Reporting Services to display the report.
• Data contract class – defines the parameters in the report.
• Report data provider class – processes business logic based on parameters and a query, and then returns the tables as a dataset for the report.

This walkthrough illustrates the following tasks:

• Creating a temporary table
• Defining a report data provider class
• Defining the report parameters
• Defining a method to return data to Reporting Services
• Adding business logic for the report
• Creating a reporting project
• Binding a report to a report data provider class

Creating a Temporary Table

Data for an RDP report is preprocessed and then stored in a temporary table. The temporary table is used by Reporting Services when the report is displayed. A table returned by a method can be a temporary table (InMemory or TempDB) or a regular table. When the data returned is used for reporting only, it is a best practice to use a temporary table.

• Use an InMemory temporary table if the dataset is small, for reports that will display fewer than 1000 records.
• Use a TempDB temporary table for large datasets to improve performance.

In this section you will create a temporary table to store the data for the report.

To create a temporary table

1. In the AOT, expand the Data Dictionary node, right-click the Tables node, and then click New Table.
2. Right-click the table, and click Properties.
3. In the Properties window, set the Name property to TmpCustTableSample and set the Table Type property to TempDB. This will define the table as a SQL Server temporary table.
4. Expand the node next to the TmpCustTableSample table so that you can see the Fields node.
5. Press Ctrl+D to open another AOT window and move the window so you can see both AOT windows.
6. In the second AOT, expand the Data Dictionary node, expand the Extended Data Types node, and drag the following types to the Field node in the first AOT window:
   • AccountNum
   • CustName
   • LogisticsAddressing
   • CustGroupId
   • Phone
   • CustInvoiceAccount
   • ActionDays
   • InclTax
7. In the second AOT window, expand the Base Enums node and drag the CustAccountStatement enumeration to the Fields node of the first AOT window.

Defining a Report Data Provider Class

The RDP class is a data provider that allows you to add business logic for the data that is displayed on a report. The business logic for this example prompts the end user for parameter values, processes business logic to generate data in a table, and then returns the table to render in the report. In this section, you define the RDP class by extending the SRSReportDataProviderBase class. Then add the TmpCustTableSample table as a global variable. The following list describes the attributes that are attached to the RDP class for this example:

• Attach the SRSReportQueryAttribute attribute to specify the query to use to get the data for the report.

For this example, set the attribute to the Cust query.

• Attach the SRSReportParameterAttribute attribute to specify the data contract class that defines the report parameters for the report.

For this example, set the attribute to the SrsRDPContractSampledata contract.

To define a report data provider class

1. In the AOT, right-click the Classes node, and then click New Class.
2. Right-click the new class, click Rename, and then enter SrsRDPSampleClass.
3. Expand SrsRDPSampleClass, right-click classDeclaration, and then click View Code.
4. In code editor, enter the following code in the class declaration to define the class.

X++

[

SRSReportQueryAttribute (querystr(Cust)),

SRSReportParameterAttribute(classstr(SrsRDPContractSample))

]

public class SrsRdpSampleClass extends SRSReportDataProviderBase

{

TmpCustTableSample tmpCust;

}

Defining a Data Contract Class

An RDP class must have a data contract if the report has one or more report parameters. This example defines three report parameters for account number, account statement, and whether to include tax. When you print the report, you can specify which data to print based on the report parameters. For example, you can specify to only print transactions for account number 4000.

A data contract is an X++ class with getters, setters, and the DataMemberAttribute attribute. The data contract defines the parameters that the report uses.

To define a data contract class

1. In the AOT, right-click the Classes node, and then select New Class.
2. Right-click the new class, click Rename, and then enter SrsRDPContractSample.
3. Expand SrsRDPContractSample, right-click classDeclaration, and then click View Code.
4. In code editor, enter the following code in the class declaration to define the class.

X++

[DataContractAttribute]

public class SrsRDPContractSample

{

AccountNum accountNum;

CustAccountStatement accountStmt;

boolean inclTax;

}

A data contract class has methods with the DataMemberAttribute attribute. The name that follows this attribute is the parameter name that displays in Visual Studio when you bind a report data set to the RDP class. In this section, add a method for each of the report parameters and name them parmAccountNum, parmAccountStmt, and parmInclTax.

To define data contract methods

1. Right-click SrsRDPContractSample, point to New, and then click Method.
2. Edit the method so that it contains the following code.

X++

[DataMemberAttribute("AccountNum")]

public AccountNum parmAccountNum(AccountNum _accountNum = accountNum)

{

accountNum = _accountNum;

return accountNum;

}

1. Right-click SrsRDPContractSample, point to New, and then click Method.
2. Edit the method so that it contains the following code.

X++

[DataMemberAttribute("CustAccountStatement")]

public CustAccountStatement parmAccountStmt(CustAccountStatement _accountStmt = accountStmt)

{

accountStmt = _accountStmt;

return accountStmt;

}

1. Right-click SrsRDPContractSample, point to New, and then click Method.
2. Edit the method so that it contains the following code.

X++

[DataMemberAttribute("InclTax")]

public boolean parmInclTax(boolean _inclTax = inclTax)

{

inclTax = _inclTax;

return inclTax;

}

Defining a Method to Return Data to Reporting Services

A method to return the processed data in the temporary table to Reporting Services is needed. In this section, add a method named getTmpCustTable and attach the SRSReportDataSetAttribute attribute to indicate the dataset for the report.

To define a method to return the data to Reporting Services

1. Right-click SrsRdpSampleClass, point to New, and then click Method.
2. Edit the method so that it contains the following code.

X++

[SRSReportDataSetAttribute("TmpCust")]

public TmpCustTableSample getTmpCustTable()

{

select * from tmpCust;

return tmpCust;

}

Adding Business Logic for the Report

The business logic for this example prompts the end user for parameter values, processes business logic to generate data in a table, and then returns the table to render in the report.

The report business logic is provided in the processReport method. This method is called by Reporting Services at runtime. The following example illustrates how the processReport method computes data and populates the data table that is returned to Reporting Services. In this section, override the processReport method to provide business logic for your report.

To add business logic for the report

1. Right-click SrsRdpSampleClass, point to Override method, and then click processReport.
2. Edit the method so that it contains the following code.

X++

public void processReport()

{

AccountNum accountNumber;

CustAccountStatement custAcctStmt;

boolean boolInclTax;

Query query;

QueryRun queryRun;

QueryBuildDataSource queryBuildDataSource;

QueryBuildRange queryBuildRange;

CustTable queryCustTable;

SrsRdpContractSample dataContract;

// Get the query from the runtime using a dynamic query.

// This base class method reads the query specified in the SRSReportQueryAttribute attribute.

query = this.parmQuery();

// Get the parameters passed from runtime.

// The base class methods read the SRSReportParameterAttribute attribute.

dataContract = this.parmDataContract();

accountNumber = dataContract.parmAccountNum();

custAcctStmt = dataContract.parmAccountStmt();

boolInclTax = dataContract.parmInclTax();

// Add parameters to the query.

queryBuildDataSource = query.dataSourceTable(tablenum(CustTable));

if(accountNumber)

{

queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTable, AccountNum));

if (!queryBuildRange)

{

queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTable, AccountNum));

}

// If an account number has not been set, then use the parameter value to set it.

if(!queryBuildRange.value())

queryBuildRange.value(accountNumber);

}

if(custAcctStmt)

{

queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTable, AccountStatement));

if (!queryBuildRange)

{

queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTable, AccountStatement));

}

// If an account statement has not been set, then use the parameter value to set it.

if(!queryBuildRange.value())

queryBuildRange.value(int2str(custAcctStmt));

}

if(boolInclTax)

{

queryBuildRange = queryBuildDataSource.findRange(fieldnum(CustTable, InclTax));

if (!queryBuildRange)

{

queryBuildRange = queryBuildDataSource.addRange(fieldnum(CustTable, InclTax));

}

// If flag to include tax has not been set, then use the parameter value to set it.

if(!queryBuildRange.value())

queryBuildRange.value(int2str(boolInclTax));

}

// Run the query with modified ranges.

queryRun = new QueryRun(query);

ttsbegin;

while(queryRun.next())

{

tmpCust.clear();

queryCustTable = queryRun.get(tablenum(CustTable));

        tmpCust.AccountNum = queryCustTable.AccountNum;

        tmpCust.CustName = queryCustTable.name();

        tmpCust.LogisticsAddressing = queryCustTable.address();

        tmpCust.CustGroupId = queryCustTable.CustGroup;

        tmpCust.Phone = queryCustTable.phone();

        tmpCust.CustInvoiceAccount = queryCustTable.InvoiceAccount;

        tmpCust.CustAccountStatement = queryCustTable.AccountStatement;

        tmpCust.InclTax = queryCustTable.InclTax;

        tmpCust.insert();

}

ttscommit;

}

Creating a Reporting Project

Next, create a reporting project in Microsoft Visual Studio. When you create a reporting project, you use the Report Model to create a Reporting Services report.

To create a reporting project

1. Open Microsoft Visual Studio.
2. On the File menu, point to New, and then click Project. The New Project dialog box displays.
3. In the Installed Templates pane, click Microsoft Dynamics AX. In the Templates pane, click Report Model.
4. In the Name box, type SampleRDPReport, and in the Location box, type a location.
5. Click OK.

A reporting project contains a report model where you can add a report to the model.

Binding a Report to a Report Data Provider Class

Now that you have created a reporting project, you are ready to define an auto design report that displays data from the Cust query. The following procedure explains how to create an auto design report that uses the RDP class as the data source.

To create an auto design report

1. In Solution Explorer, right-click the ReportModel node, point to Add and then click Report.
2. In Model Editor, right-click the Report1 node, and then click Rename.
3. Type CustomerReport as the name.
4. Right-click the Datasets node, and then click Add Dataset.
5. In the Properties window, specify the following values.

Property	Value
Data Source	Dynamics AX
Data Source Type	Report Data Provider
Default Layout	Table
Dynamic Filters	True Note This setting is for dynamic parameters on the report. Setting the property to True allows you to filter the report by setting a range on any fields from the data source table.
Name	Customer
Query	Click the ellipsis button (…). A dialog box displays where you can select an RDP class that is defined in the AOT and identify the fields that you want to use. Select the SrsRDPSampleClass class and click Next. In the Select Fields dialog box, keep all the checkboxes selected, and click OK. Note Based on what you select, the Query property value is updated. In this case, the query is SELECT * FROM SrsRDPSampleClass.TmpCust.

1. In Model Editor, select the Customer node and drag it onto the Designs node. An auto design named AutoDesign1 is created for the report.

Note

If you expand the Parameters node you see the parameters that you defined in the data contract class. When you define nested data contract parameters, they are listed under the Parameters node also. Select the SrsRDPSampleClass_DynamicParameter parameter. In the Properties window, the AOT Query property is set to the query specified in the parmQuery method in the RDP class.

1. Select AutoDesign1 and then click the Preview button.
2. Enter a value for Account number and Account statement that returns data and then click the Report tab to preview the report.

Note

You can use * to display all of the account numbers on the report. Account statement is an enumeration and there is no wild card value for enumerations. Check your sample data to determine a valid enumeration value for Account statement. The Select button displays because you set the Dynamic Filters property to True on the dataset. You have the option to click the Select button to specify a range to filter the report and limit the data that displays on the report.

The next steps are to add layout and style templates, deploy, and add the report to a menu item so you can see it in Microsoft Dynamics AX.

Developing RDP (Report data provider) based SSRS reports – AX 2012

RDP (Report data provider) based reports are developed, when reports has a complex logic that cannot be achieved by just using an AOT query. In RDP based reports, in addition to an AOT query, an RDP class is developed which contains the report logic. After processing all the report logic, the data is filled into the temporary table which is used as a data source for SSRS report. Now, I am going to describe about how to build an RDP based report. We will modify the previous AOT query for this example to add the CustTrans data source. The query will now look like as shown below:

Now create a new RDP class and name it SampleReportDP. To create RDP based report, following objects needs to be created:

• An RDP class.
• An AOT query.
• A temp table.

The query has been modified for this example and now RDP class and temp table needs to be created. Just for a demo purpose, I have thought of a requirement where we need to display the transaction amount based on the customer group. The logic is as follows:

· If (customer group is 20) then multiply the transaction amount by 20

· If (customer group is 40) then multiply the transaction amount by 40

· If (customer group is 60) then multiply the transaction amount by 60

Create a new class and name it SampleReportDP. In class declaration extend the class from SRSReportDataProviderBase class. Add the following line before class declaration:

[SRSReportQueryAttribute(querystr(SampleQuery))]

This attribute specifies the AOT query which is used by this SSRS report. Also declare some class variables which are needed to store the values, retrieved after running the report query. The class declaration should look like as shown below:

The second step is to create a temp table which is used to store the report data. Create a new table and name it SampleReportTmp. Set the TableType property of the new table to InMemory. Create four fields in the temp table as shown below:

Create a new method in RDP class which will select the temp table records and return it afterwards to be used by the SSRS report. The new method should look like as shown below:

Now we are going to implement the logic discussed above in RDP class processReport method. The processReport method in RDP class is the point where the report processing begins. This method is called by the reporting framework. Actual report logic needs to be written in this method. In our case, the code for processReport method is as follows:

public void processReport()
{
    QueryRun queryRun;

    queryRun = new QueryRun(this.parmQuery());

    while (queryRun.next())
    {
        custTable = queryRun.get(tablenum(CustTable));
        custTrans = queryRun.get(tablenum(CustTrans));

        amountCur = custTrans.AmountCur;
        voucher   = custTrans.Voucher;
        custGroup = custTable.CustGroup;
        transDate = custTrans.TransDate;

        switch (custGroup)
        {
            case '20':
                amountCur = amountCur * 20;
            break;

            case '40':
                amountCur = amountCur * 40;

            case '60':
                amountCur = amountCur * 60;
            default:
                amountCur = custTrans.AmountCur;
        }

        this.insertSampleReportTmp();
    }
}

 

code for insertSampleReportTmp method is as follows:

private void insertSampleReportTmp()
{
    ;
    sampleReportTmp.AmountCur   = amountCur;
    sampleReportTmp.Voucher     = voucher;
    sampleReportTmp.TransDate   = transDate ;
    sampleReportTmp.CustGroup   = custGroup ;

    SampleReportTmp.insert();
}

Create a new Microsoft Dynamics AX reporting project from VS 2010 and name it RDPBasedReport. Add a report item to the project by right clicking on the Project. Change the report name to RDPBasedReport. Create a new data set and name it SampleRDPReportDS. Now open the properties page for the newly created dataset by clicking Ctrl + Enter. Set the data source type to ‘Report Data Provider’. Select the query property and click the ellipses to open the RDP class selection window. From the list of classes, select your RDP class which was SampleReportDP and then click next. Afterwards, select all the fields and then click OK. You report should match with the following screen shot:

Notice the query mentioned in the Query property of dataset. It is the syntax for mentioning RDP class and temp table in the query property whenever you use the report data provider as data source type. Add the new report to AOT by right clicking on the report project. Now deploy the report by right click on the report solution and select deploy solution to deploy the SSRS report on report server.

Create a new output menu item, and name it RDPBasedReport. Set its properties as shown below:

Now right click on the menu item and select Open to launch the SSRS report we have just developed. The report output should match with the below screen shot:

Developing Query Based SSRS reports using VS - AX 2012

1- Developing a new SSRS report:

Before start developing SSRS reports for Dynamics AX, you must have installed BI Components and Visual Studio tools from AX setup. After installation of both these additional components, open Visual Studio, click on File -> New Project. New Project dialog is open as shown below:

From the above dialog select Microsoft Dynamics AX in Project types and select Report Model under templates section. The SSRS reports needs a data source from where the report can grab the data and displayed it on the report. There are three types of data source available to be used in SSRS report which is as follows:

• Query
• RDP (Report data provider).
• Business Logic.

When you use query as a data source type for SSRS report then the report is called Query based report. Now, I am going to explain you that how we develop SSRS reports based on Query.

2. Developing Query Based SSRS reports using VS

Before you can start developing query based reports in VS, you need an AOT query to be created in Dynamics AX. Suppose you want to show all the records form CustTable table in an SSRS report. This can be achieved through query based report. Create an AOT query in Dynamics AX as shown below:

The Query name is SampleQuery. Add CustTable under the data sources node of the query. All the fields of the CustTable are included in the AOT query. Now you need to create an SSRS report which used this query as a data source and display all the fields of CustTable on SSRS report. Open Visual Studio and goto File -> New Project. New Project dialog form will be opened as shown below:

Select Microsoft Dynamics AX under Project types and Report Model under Templates section. Specify the name of the report as ‘QueryBasedReporrt’ and press OK button. A new SSRS project will be created as shown below:

Now you need to add a report under the above project. Right click on the project, select Add and then select Report. A report will be added to the project with the name Report1. Change the report name to QueryBasedReport. Now the solution explorer should look like as shown below:

Double click on the newly created report. You will see report items on the left side in Visual Studio as shown in the below screen shot:

The purpose of each report items is described below:

Ø Datasets: The report dataset retrieves data from the AOT query. All fields which are available in AOT query are shown in the report dataset which can be referred in the report design later on.

Ø Designs: The design or layout of the report on which the data would be displayed after retrieving from AX.

Ø Images: Any embedded images that you want to display on SSRS report.

Ø Data Methods: Business logic written in C#. Data methods are no more recommended.

Ø Parameters: Report parameters which are to be displayed on SSRS report and user can filter the report based on those parameters.

You first need to create a dataset that will refer to an AOT query created earlier. Right click on Datasets node and click ‘Add Dataset’. A dataset will be created with name ‘DataSet1’. Rename it to ‘QueryBasedReportDS’. The newly created dataset should refer to the AOT query from where the data can be picked. Right click on the newly created dataset and select ‘Properties’. Set the data source type to ‘Query’ and click on the ‘Query’ property. An ellipses button appears, click on it. A new window will open which shows the entire AOT queries present in Dynamics AX. Select the query ‘SampleQuery’ which you have created earlier as shown below:

Select All Fields from the right column to show all fields on the report dataset and then click ‘OK’ button. All the fields which are added on AOT query are now showing on the report dataset. You can see all the fields by expanding the Fields node under the dataset. You can refer to any of these fields on the report design. Now we have created a dataset which provides the data to the report, the next step is to create a report design.

There are two types of report designs available in SSRS report i.e. AutoDesign and PrecisionDesign. First preference when developing reports is to use AutoDesign unless you need a custom placement of fields in a report and in that case you have to use PrecisionDesign. Generally for complex report designs, we use PrecisionDesign. For the sake of simplicity, we will use AutoDesign here for our first SSRS report.

Right click on the Designs node, click on Add-> AutoDesign. An auto design is created with the name AutoDesign1. Change its name to ‘Report’ by right clicking on the AutoDesign1 and click Rename. It is recommended that you set the name of the Design to either ‘Design’ or ‘Report’. Now drag the data set of the report ‘QueryBasedReportDS’ on Report Design named ‘Report’. A table will be created which contains all the fields that are shown in the report dataset. The value of these fields will be shown on the report output in the same order as it is showing in the data table. The final report design should look like as shown below:

Now we execute the report to see the output. The records will be fetched from Customer table so make sure that the CustTable has some records in it before opening the report otherwise a blank output will be shown. There are three ways of opening an SSRS report which are as follows:

1) From Visual Studio which is actually the preview of the report.

2) From Browser by opening the URL http://localhost/reports.

3) From Dynamics AX through output menu item.

2.1. Previewing SSRS reports from Visual Studio

You can easily preview SSRS reports by right clicking on the design and select Preview option. For our example right click on the Design ‘Report’ and select Preview as shown in the below screen shot:

A separate preview page is open which will show the report output. If your report has some parameters then the parameters will be shown first and then the output will be shown based on the values of the parameters. Following report output will be shown when you preview the report from VS:

2.2. Viewing SSRS report on browser

Before viewing report on browser you need to deploy report on the report server. You can deploy SSRS reports in two ways, first way is to right click the solution in Visual Studio and select deploy solution option and the second way is to go to SSRS Reports/Reports node under AOT, right click on the report you want to deploy and then select Deploy. To deploy SSRS report from AX, you need to first add the report project to AOT. Following screen shot shows how to do this:

Now right click the report solution in VS and select deploy solution as shown below:

To view SSRS report from browser, go to URL http://localhost/reports. When the page is open you will see the Dynamics folder link shown on the page. By clicking on the Dynamics folder you can see the list of all SSRS reports deployed on the report server as shown below:

Click on the report which you want to preview on browser. In our case, click on the SSRS report QueryBasedReport.Report to view it on browser. Following output will be shown when you view the report from browser:

Opening SSRS report from AX

To open SSRS report from AX, you first need to create an output menu item which refers to the SSRS report you have created earlier. Below screen shot shows how to do this:

Step 1: Create an output menu item Step 2: Set the object type and object properties as shown in the above screen shot.

Now right click on the output menu item QueryBasedReport and select Open to launch the report. Following output will be shown when you run the report from AX:

2.3. Filtering a report with an specific parameter

Suppose you want to filter the report we have developed earlier by customer group. In that case you need to show the parameter customer group on the report dialog so that user can run the report for a specific customer group. Steps are shown below:

            

Step 1: Adding range in a report query. Step 2: Add CustGroup field under the Ranges node.

Now compile the query ‘SampleQuery’ to save the changes. Run the report by right clicking the output menu item and select Open. Following report dialog will be shown:

You can see the customer group parameter in the above dialog which we have added through report query. Another parameter customer account is shown on the above dialog. Just ignore this parameter, it is showing here because this field is the part of unique index on CustTable. Now click on the select button to specify a value for customer group parameter shown above. Following screen shot will be shown:

Select the customer group 30 and click OK button. Customer group 30 will be shown selected on the report dialog as shown below:

Again click the OK button on the report dialog to open the report filtered by customer group 30. Following output will be shown:

As you can see that, only those records are shown, which have customer group 30. In the same way you can apply as many filters as you want on the report AOT query to filter the report based on that field’s value.

Conclusion of Reporting in AX 2012

The SQL Server Reporting Services (SSRS) reporting architecture in Microsoft Dynamics AX 2012 is modified to follow a Model-View-Controller (MVC) design pattern variation. This architecture means many different client types can call Microsoft Dynamics AX 2012 SSRS reports including: Microsoft Dynamics AX clients, Enterprise Portal, and Batch Job.

Reports now use services instead of the .NET Business Connector to retrieve Microsoft Dynamics AX online transaction processing (OLTP) data.

Reporting Architecture

Microsoft Dynamics AX enforces security on all data returned. If the user who is running the report is not allowed to see a specific field, the data for that field is not returned.

Reporting Services offers several approaches for deploying server components.

• Scale-out deployment: A report server scale-out deployment is two or more report server instances that share a single report server database. A scale-out deployment enables you to increase the number of users who concurrently access reports and improve the availability of the report server.

• Failover cluster: SQL Server provides failover clustering support so that you can use multiple disks for one or more SQL Server instances.

Failover clustering is supported only for the report server database; you cannot run the Reporting Services Windows service as part of a failover cluster. .

Some reports use online analytical processing (OLAP) cubes to access data. The default OLAP cubes that are provided with Microsoft Dynamics AX require full license and configuration keys. When you turn off license or configuration keys, data is removed from corresponding columns in the online transaction processing (OLTP) database. As a result, cubes cannot access the data they were designed to retrieve. This means that you may see errors displayed in reports and Role Center web parts that use cubes as a data source. You will need to modify these reports and web parts so that they no longer try to retrieve data from a column or field that contains no data.

To install the reporting components in Microsoft Dynamics AX 2009, you had to install the reporting extensions when running the Setup wizard. For Microsoft Dynamics AX 2012, the Setup wizard is changed. It no longer includes an option for installing the reporting extensions. When you run the Setup wizard for Microsoft Dynamics AX 2012, you can install the business intelligence components.

Microsoft Dynamics AX includes many default reports that you must deploy to Microsoft SQL Server Reporting Services. If you did not deploy the reports when you installed the Business Intelligence components, you can use Windows PowerShell to deploy the reports.

Report deployment has moved to PowerShell from the Microsoft Dynamics AX 2009 Reporting Project Deployment form.

To deploy all reports, enter the following command: "Publish-AXReport –ReportName *", and then press Enter.

To deploy a specific report, enter The following command which used to deploy the CustTransList report: "Publish-AXReport –ReportName CustTransList", and then press Enter.

To Retrieve information about the default reports enter the following command: “Get-AXReport -ReportName *", and then press Enter.

Modify the list so that only the Name and ChangedDate fields are displayed by entering the following command: "Get-AXReportName * | Select- Object Name,ChangedDate" and then press Enter.

To filter list of all report which retrieved in previous command and get only specific reports are listed. For example, to filter the list so that only the reports that contain the word CustTrans are listed, enter the following command: "Get-AXReportName * | Select-Object Name,ChangedDate | Where { $_.Name –like "CustTrans*" }", and then press Enter.

Configure Report server

To grant users access to reports, you must configure security settings in Microsoft Dynamics AX and in Microsoft SQL Server Reporting Services. The following sections describe the tasks that you must complete in each application.

Configure security settings in Microsoft Dynamics AX

Complete the following tasks in Microsoft Dynamics AX:

• Determine which reports each Microsoft Dynamics AX role should have access to.

• Verify that each Microsoft Dynamics AX role has the correct duties and privileges assigned to it in order to access the reports.

• Assign users to Microsoft Dynamics AX roles.

• Secure the data shown in reports.

Configure security settings in Reporting Services

Complete the following tasks in Reporting Services:

• Assign users to the DynamicsAXBrowser role in Reporting Services.

• Identify the account that is used to run the Application Object Server (AOS) service and the account that is used as the Business Connector proxy. Assign those accounts to the DynamicsAXBrowser role in Reporting Services.

• Restrict access to report folders and reports. Reporting Services includes security features and tools that you should use to help control access to report folders and published reports.

Procedure: Grant Users Access to Reports in AX 2012

Configure security settings in Microsoft Dynamics AX
Complete the following tasks in Microsoft Dynamics AX:
• Determine which reports each Microsoft Dynamics AX role should have access to.
• Verify that each Microsoft Dynamics AX role has the correct duties and privileges assigned to it in order to access the reports.
• Assign users to Microsoft Dynamics AX roles.
• Secure the data shown in reports.

Configure security settings in Reporting Services Complete the following tasks in Reporting Services:
• Assign users to the DynamicsAXBrowser role in Reporting Services.
For detailed instructions about how to assign users to Reporting Services roles, refer to my post (How to: Grant User Access to a Report Server).
• Identify the account that is used to run the Application Object Server (AOS) service and the account that is used as the Business Connector proxy. Assign those accounts to the DynamicsAXBrowser role in Reporting Services.
• Restrict access to report folders and reports. Reporting Services includes security features and tools that you should use to help control access to report folders and published reports. Refer to the SQL Server documentation on MSDN for detailed conceptual information and step-by-step tutorials that will help you administer
security in Reporting Services.

Reporting Architecture – AX 2012

The following diagram illustrates the architecture of the reporting functionality in Microsoft Dynamics AX.

1. A user requests a report.
Assume that a user clicks a menu item in the Microsoft Dynamics AX client. The menu item is bound to a SQL  Server Reporting Services report.
After the user clicks the menu item, a parameters form is displayed to the user. The user enters parameters to filter the data that will be displayed on the report.
The Microsoft Dynamics AX client then requests the report from Reporting Services. (The request includes the parameters entered by the user.)

2. Reporting Services receives the request and asks the Microsoft Dynamics AX server for the report data.
Reporting Services receives the request and examines the report on the server. The report is stored as an .rdl file. The .rdl file indicates the report’s data source. (The data source could be a Microsoft Dynamics AX query, a report data provider class, or an external data source via report data methods.)
In cases where a Microsoft Dynamics AX data source is used for the report, Reporting Services will use the Microsoft Dynamics AX data extension to retrieve the data.
At this point, Reporting Services asks Microsoft Dynamics AX for metadata about the data source. Reporting Services then requests the data for the report.

3. The Microsoft Dynamics AX server receives the request and sends the report data back to Reporting Services.
The Microsoft Dynamics AX services examine the query in the AOT to return the requested metadata. The services also execute the query to generate the data for the report.

Microsoft Dynamics AX returns the metadata and data to Reporting Services.

NOTE: Microsoft Dynamics AX enforces security on all data returned. If the user who is running the report is not allowed to see a specific field, the data for that field is not returned.

4. Reporting Services renders the report and sends it to the Microsoft Dynamics AX client.
The Microsoft Dynamics AX customization extension formats the report.
The customization extension uses metadata to provide automatic formatting of data and can affect the positioning and layout of elements in the report.
Reporting Services then renders the report into a visual representation and sends that to the Microsoft Dynamics AX client.

5. The report is displayed to the user.
The Microsoft Dynamics AX client displays the report to the user in the report viewer control.

How to- Get-AXReport

Retrieves a list of Microsoft SQL Server Reporting Services reports available from Microsoft Dynamics AX.

Syntax

Parameter Set: Default

Get-AXReport [-OnlyName] [-ReportName <String> ] [-ServicesAOSName <String> ] [-ServicesAOSWSDLPort <Int32> ] [-ServicesFilePath <String> ] [ <CommonParameters>]

 

Detailed description

The Get-AXReport cmdlet retrieves a list of Reporting Services reports available from Microsoft Dynamics AX. The cmdlet returns values for ChangedBy, ChangedDate, CreatedBy, CreatedDate, DataSources, Designs, Name, and VsProjectNames.

Parameters

-OnlyName

Specifies that only report names will be returned by the cmdlet.

Aliases	none
Required?	false
Position?	named
Default Value	none
Accept Pipeline Input?	false
Accept Wildcard Characters?	false

-ReportName<String>

Specifies names of reports to query for in Microsoft Dynamics AX.

Aliases	none
Required?	false
Position?	named
Default Value	none
Accept Pipeline Input?	false
Accept Wildcard Characters?	false

-ServicesAOSName<String>

Specifies the name of a Microsoft Dynamics AX Application Object Server (AOS) instance to connect to instead of the default value.

Aliases	none
Required?	false
Position?	named
Default Value	none
Accept Pipeline Input?	false
Accept Wildcard Characters?	false

-ServicesAOSWSDLPort<Int32>

Specifies the web service (WSDL) port of an AOS instance to connect to instead of the default value.

Aliases	none
Required?	false
Position?	named
Default Value	none
Accept Pipeline Input?	false
Accept Wildcard Characters?	false

-ServicesFilePath<String>

Specifies a client configuration parameter file (.axc) to use instead of the configuration that is stored in the registry.

Aliases	none
Required?	false
Position?	named
Default Value	none
Accept Pipeline Input?	false
Accept Wildcard Characters?	false

<CommonParameters>

This cmdlet supports the common parameters: Verbose, Debug, ErrorAction, ErrorVariable, OutBuffer, OutVariable, WarningAction, and WarningVariable. For more information, see about_CommonParameters http://go.microsoft.com/fwlink/?LinkID=113216

Inputs

The input type is the type of the objects that you can pipe to the cmdlet.

• None

You cannot pipe input to this cmdlet.

Outputs

The output type is the type of the objects that the cmdlet emits.

• None

The cmdlet does not generate any output.

Examples

Example 1

This example returns detailed information for the AssetAddition report.

C:\PS>Get-AXReport -ReportName AssetAddition

ChangedBy : Admin
ChangedDate : 5/10/2011 12:00:00 PM
CreatedBy : Admin
CreatedDate : 5/10/2011 12:00:00 PM
DataSources : {}
Designs : {Report}
Name : AssetAddition
VSProjectNames : {}

Example 2

This example returns a list of all of the reports whose names begin with cust.

C:\PS>Get-AXReport -OnlyName -ReportName Cust*

CustAccountStatement_FR
CustAccountStatementExt
CustAccountStatementInt
CustAgingReport
CustAuditor
CustBalanceList
CustBalanceList_MY

Grant users access to reports [AX 2012]

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

This topic explains how to give users access to reports. Two procedures are described in this topic. The procedure that you should use depends on whether you are running Microsoft SQL Server Reporting Services in native mode or SharePoint integrated mode.

Note

SharePoint integrated mode is supported if you are using Microsoft Dynamics AX 2012 R2.

Assign users to the DynamicsAXBrowser role on the Report Manager site

If you are running Reporting Services in native mode, you must assign users or groups to the DynamicsAXBrowser role on the Report Manager site. The following procedure explains how to complete this task.

1. Open the Report Manager website for the Reporting Services instance. By default, the URL is http://[SSRSServerName]:80/Reports.

2. Click the DynamicsAX folder.

3. Click Folder Settings.

4. Click Security.

5. Click New Role Assignment.

6. Enter the Active Directory user name or group to assign to the DynamicsAXBrowser role.

7. Select the DynamicsAXBrowser role.

8. Click OK.

Grant users permission to view reports in SharePoint

If you are running Reporting Services in SharePoint integrated mode, you must grant users permission to view reports in SharePoint. To grant this permission, grant users Read permission to the document library that stores the reports. Alternatively, if the document library inherits permissions from the site, you can grant users Read permission to the site. The following procedure describes how to grant users Readpermission to the site.

Important

If the SharePoint site is configured for claims-based authentication, you must also grant the following accounts Read permission to the document library or site:

• The account that is used as the Business Connector proxy

• The account that is used to run the Microsoft Dynamics AX Application Object Server (AOS) service.

1. Open your browser and navigate to the SharePoint site that contains the document library that stores the reports.

2. Click Site Actions > Site Permissions.

3. Click Grant Permissions. The Grant Permissions window is displayed.

4. In the Users/Groups field, enter the Active Directory names of the users or groups that you want to view reports.

5. In the Grant Permissions area, select the Grant users permission directly option.

6. Select the Read check box.

   Note

   If you want users of Enterprise Portal for Microsoft Dynamics AX to be able to filter reports by using a custom parameter value, select the Design check box. For more information about the permissions that are required to use Enterprise Portal, see Enable users to access Enterprise Portal.

7. Click OK.

Create a document library to store reports [AX 2012]

Applies To: Microsoft Dynamics AX 2012 R2

If you are using Microsoft Dynamics AX 2012 R2, and if Microsoft SQL Server Reporting Services is running in SharePoint integrated mode, create a document library in SharePoint to store your reports. Complete this procedure before you deploy the default reports that are included with Microsoft Dynamics AX.

Note

This procedure does not apply to you if you are running Reporting Services in native mode.

Create a document library

Create a document library on your SharePoint site to store reports. For information about how to create a document library, see the SharePoint documentation.

After you create the document library, add Reporting Services content types to the library. For more information, see Add Report Server Content Types to a Library (Reporting Services in SharePoint Integrated Mode) in the SQL Server documentation.

Specify the URL of the document library

After you have created the document library, complete the following procedure to specify the URL of the document library in the Report servers form in Microsoft Dynamics AX.

1. Open Microsoft Dynamics AX.

2. Click System administration > Setup > Business intelligence > Reporting Services > Report servers.

3. In the Configuration ID field, enter a name that identifies the Reporting Services instance and the Application Object Server (AOS) instance that you are connecting.

4. In the Description field, enter a brief description to help you identify the Reporting Services instance and the AOS instance that you are connecting.

5. Select the Default configuration check box to make the Reporting Services and AOS instances that are specified in this record the active connection.

6. On the Reporting Server information tab, enter the following information:

   1. In the Server name field, enter the name of the server that is running Reporting Services.

   2. In the Server instance name field, enter the name of the Reporting Services instance.

      Note

      If you are using Reporting Services 2012, enter @Sharepoint.

   3. Leave the Report Manager URL field blank. This field becomes unavailable when you select the SharePoint integrated mode check box in a later step.

   4. In the Web service URL field, enter the URL of the Reporting Services web service.

      • If you are using Reporting Services 2008, the URL is typically http://[SSRSServerName]/ReportServer.

      • If you are using Reporting Services 2012, the URL is typically http://[SharePointServerName]/_vti_bin/ReportServer or http:[SharePointServerName]/sites/[SiteName]/_vti_bin/ReportServer.

   5. Select the SharePoint integrated mode check box.

   6. In the Microsoft Dynamics AX report folder field, enter the URL of the document library that you created to store reports.

      For example, suppose that you have created a document library that is named Reports on a SharePoint site that is named Contoso. In this example, the URL is as follows:

      http://[SharePointServerName]/sites/Contoso/Reports

7. On the Application Object Server information tab, select the name of the AOS instance.

Deploy reports for the new Reporting Services instance [AX 2012]

Applies To: Microsoft Dynamics AX 2012 R2, Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012

Microsoft Dynamics AX includes many default reports that you must deploy. You can deploy these reports by using Windows PowerShell. The following procedures can help you deploy the reports.

Before you begin

Before you can deploy the reports by using Windows PowerShell, you must complete the following tasks:

• Verify that Windows PowerShell 2.0 is installed on the computer that you are using.

• Verify that your Windows domain account is a member of the Administrators group on the server that is running Microsoft SQL Server Reporting Services.

  Note

  If your Windows domain account is assigned to a group that is a member of the Administrators group, it may take some time to validate that you are a member of the Administrators group. If you experience a delay when you deploy reports, consider adding your Windows domain account directly to the Administrators group.

• If Reporting Services is running in native mode, verify that you are assigned to the System Administrator role on the Report Manager website.

• If Reporting Services is running in SharePoint integrated mode, verify that you have been granted Contribute permission to the document library where you plan to deploy the reports.

  Note

  SharePoint integrated mode is supported if you are using Microsoft Dynamics AX 2012 R2.

Open Windows PowerShell and view a list of reports

Complete the following procedure to open Windows PowerShell and view a list of the reports that are included with Microsoft Dynamics AX.

1. Open Windows PowerShell as an administrator by following these steps.

   1. Click Start > Administrative Tools.

   2. Right-click the Microsoft Dynamics AX 2012 Management Shell option.

   3. Click Run as administrator.

2. Retrieve a list of the reports that are included with Microsoft Dynamics AX, and store the list in a local variable by entering the following command:

   Windows PowerShell

   $reports = Get-AXReport -ReportName *
   

   
   

   For more information about the Get-AXReport command, see Get-AXReport.

   
   
3. 
   

   View the list of reports by entering the following command:
   

   Windows PowerShell

   $reports
   

Filter the list of reports

In the previous procedure, you displayed a list of all the reports that are included with Microsoft Dynamics AX. To modify and filter the list, you can use the following commands.

• 
  

  To modify the list so that only the Name and ChangedDate fields are displayed, enter the following command:
  

  Windows PowerShell

  $reports | Select-Object Name,ChangedDate
  

  
  
• 
  

  To filter the list so that only specific reports are listed, enter keywords or report names. For example, to filter the list so that only reports that contain the word CustTrans are listed, enter the following command:
  

  Windows PowerShell

  $reports | Select-Object Name,ChangedDate | Where { $_.Name -like "CustTrans*" }
  

Deploy the reports

After you have retrieved a list of reports, you can deploy the reports. The Publish-AXReport command is used to deploy the reports. The following examples show how to use this command. For more information, see Publish-AXReport.

Note

In the following examples, SSRSConfigID refers to a configuration ID that was defined in Microsoft Dynamics AX. To view these configuration IDs, open Microsoft Dynamics AX and then open the Report servers form. (ClickSystem administration > Setup > Business intelligence > Reporting Services > Report servers.) To deploy reports to the new Reporting Services instance, enter the configuration ID that is associated with that instance.

• 
  

  To deploy a specific report, enter the name of the report. For example, to deploy the CustTransList report, enter the following command:
  

  Windows PowerShell

  Publish-AXReport –Id SSRSConfigID -ReportName CustTransList
  

  
  
• 
  

  To deploy two or more specific reports, enter the names of the reports. For example, to deploy the CustTransList and CustTransOpenPerDate reports, enter the following command:
  

  Windows PowerShell

  Publish-AXReport –Id SSRSConfigID -ReportName CustTransList, CustTransOpenPerDate
  

  
  
• 
  

  To deploy all reports, enter the following command:
  

  Windows PowerShell

  Publish-AXReport –Id SSRSConfigID –ReportName *