>

Tag Archives: Reports

How to Call Report from Dynamics AX 2009

After you created your report in AX you can call report from  AX form by follow the below steps:

1- Create menu item with type output by drag and drop report to MenuItems nodes under AOT.

2- Open the form that you need to call report from.

                  Note: the form should be have dataSource with the table in report

3-  Drag and Drop menu Item created in step 1 in the form under any button group.

4- Add the following method to Report

void initFromCaller(Args _args)
{
    str                   QrderId; // field used as Rang and used to filter data
    QueryBuildDataSource  qbds; // should be represent report datasource and selected record in the form

    ;

    if (! _args ||
        ! _args.caller() ||
          _args.dataset() != tablenum(TableName) )
        return;

    QrderId = _args.record().(fieldnum(TableName,FieldName));

    qbds    =  element.query().dataSourceTable(tablenum(TableName));

    if(!qbds.findRange(fieldnum(TableName,FieldName)))
    {
        qbds.addRange(fieldnum(TableName,FieldName)) ;
    }
    qbds.findRange(fieldnum(TableName,FieldName)).value(queryvalue(QrderId));

}

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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:

image

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

clip_image002

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

clip_image004

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.

Reporting architecture

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.

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers:

error: Content is protected !!