How to Create SSRS Report in D365

Overview
Consider a scenario where we are going to print a list of customers and their invoiced sales order counts.

This tutorial will guide you in developing Report Data Provider (RDP) based SSRS reports in MS Dynamics D365.
Pre-requisites

1. Microsoft Dynamics D365
2. Visual studio 2015
3. SQL Server Reporting Services (SSRS) must be configured
4. Reporting services extensions must be installed in D365.

Important Concepts

1.      Report Data Provider (RDP) Class

Report Data Provider Class is an X++ class that is used to access and process data for a SSRS report. The RDP class processes the business logic based on a specified parameter and/or query and returns a dataset to the reporting services. In order to create a RDP class in AX, you have to extend that class with SRSReportDataProviderBase. This tells AX that this class will be used by reporting services to process the data.

Two important attributes are used in RDP classes:

1. SRSReportQueryAttribute: specifies which AOT query will be used in this report. If the RDP class uses an AOT query to process data, define this attribute at the beginning of the class.
2. SRSReportParameterAttribute: defines the data contract class that will be used by this report to prompt for parameter values. If the RDP class contains any parameters this define this attribute at the beginning of the class.

Both the attributes are optional. If the report does not use any query or does not want any parameter to filter report data, these attributes do not need to be used.

·         Data Contract Class

A data contract class is an X++ class which contains parm methods with the DataMemberAttribute defined at the beginning of the method. This class is used to define one or more parameters that will be used in a SSRS report.

·         Table

An AX table is used as the dataset to store data for the report. The RDP class processes the data and stores it in the table which is then used by a SSRS report to render data.

A table can be a temporary table (InMemory or TempDB) or a regular table, but it is Microsoft best practice to use a temporary table.

The type of temporary table is based upon the performance considerations. InMemorytemporary table is used when the data set is small, while TempDB is normally used for larger datasets to improve performance.

Create Model

Before we start how to create a new model in Dynamics 365 for Operations (AX7), it is important to understand what a model is. A model can be defined as a collection of elements that represent a distributed software solution.

To create a new model, in Visual Studio – from the menu, click on the Dynamics AX >> Model Management >> Create model.This will open the model wizard.

From the model wizard, you can specify the Model name, Model Publisher, Layer, Version, Model description, and Model display name.

After clicking Next, you will need to determine if you want to Create new package, or Select existing package.

Next I am prompted to give a name for the new project being created associated with my new model.

After hitting Ok, my new project (associated with my model) can be seen in the Solution Explorer window. From picture below, you may identify Project name, layer, and the Model name.

Now Create table and name it “dev_CustReportRDPDemoTmp”. To create table right click on Solution explorer tab and over select option and select New item.

To add Fields in the table right click on the Fields node and select data type which data type fields you want to add in the table. If you want to add relation with another table, then you have to add that table extension in the current project. If you want to add edit, then select the EDT fields from the selected fields property. It will might take time to add EDT in the selected fields.

Expand the dev_CustReportRDPDemoTmp table node and add the following fields in the table:

No.	Field name	Extended Data Type	Label
1	CustAccount	CustAccount
2	Name	Name
3	SalesOrderInvoiceCount	Integer	Sales order invoiced

Now next step is for report parameters. For this purpose, we use Data contract class. In current report we required parameters base on Extended Data Type CustAccount, FromDate and ToDate. At simplest our data contract class will be look like as follow.

To Add Contract Class in the project right click on Solution explorer tab and over select option and select New item. On the Code table select class and name it “dev_ CustReportRDPDemoContract”.

Now create code for parameter like below.

[DataMemberAttribute]

class dev_CustReportRDPDemoContract

{

    CustAccount                 objCustAccount;

    TransDate           FromDate, ToDate;

    [DataMemberAttribute('From date')]

    public TransDate ParmFromDate(FromDate _FromDate=FromDate)

    {

        FromDate = _FromDate;

        return FromDate;

    }

    [DataMemberAttribute('To date')]

    public TransDate ParmToDate(ToDate _ToDate=ToDate)

    {

        ToDate = _ToDate;

        return ToDate;

    }

    [DataMemberAttribute('Customer Account No.')]

    public CustAccount ParmCustAccount(CustAccount _CustAccount=objCustAccount)

    {

        objCustAccount = _CustAccount;

        return objCustAccount;

    }

}

Now we required to write some logic which you above mention class as its data contract and populate the custom temp table we build in pervious step. Now create a RDP class. Go to Classes and create a new class called “dev_CustReportRDPDemoDP" by right click on Solution explorer tab and over select option and select New item and selecting New Class. It is a best practice to suffix the RDP class name with DP.

Create a method and your logic like below.

[SrsReportParameterAttribute(classstr(dev_CustReportRDPDemoContract))]

class dev_CustReportRDPDemoDP extends SRSReportDataProviderBase

{

    dev_CustReportRDPDemoTmp        objdev_CustReportRDPDemoTmp;

    dev_CustReportRDPDemoContract   Contract;

    TransDate                       _FromDate,_ToDate;

    CustAccount                     objCustAccount;

    CustTable                       objcustTable;

    SalesTable                      objsalesTable;

   

    [SrsReportDataSetAttribute('dev_CustReportRDPDemoTmp')]

    public dev_CustReportRDPDemoTmp GetData()

    {

        select objdev_CustReportRDPDemoTmp;

        return objdev_CustReportRDPDemoTmp;

    }

    public void processReport()

    {

        Contract = this.parmDataContract();

        _FromDate = Contract.ParmFromDate();

        _ToDate = Contract.ParmToDate();

        objCustAccount=Contract.ParmCustAccount();

        super();

        delete_from objdev_CustReportRDPDemoTmp;

        objdev_CustReportRDPDemoTmp.clear();

        while select * from objsalesTable where objsalesTable.SalesStatus == SalesStatus::Invoiced

            && (!_FromDate || objsalesTable.ShippingDateRequested >= _FromDate)

            && (!_ToDate || objsalesTable.ShippingDateRequested <= _ToDate)

            join * from objcustTable where objcustTable.AccountNum==objsalesTable.CustAccount

            && (!objCustAccount || objcustTable.AccountNum==objCustAccount)

        {

            objdev_CustReportRDPDemoTmp.CustAccount = objcustTable.AccountNum;

            objdev_CustReportRDPDemoTmp.Name = objcustTable.name();

            objdev_CustReportRDPDemoTmp.SalesOrderInvoiceCount=any2Int(objsalesTable.RecId);

            objdev_CustReportRDPDemoTmp.insert();

        }

    }

}

At this step build the solution, never forget to check the project Synchronized database on build set to true. Right click Solution explorer and select property.

Now add new report in project. Right click on Solution explorer tab and over select option and select New item. On that select Reports Node and Select report and name it “dev_CustReportRDPDemoReport”.

Double click on Report in solution and open in designer screen. And right click on dataset and create new Data Set.

Now double click the report to open it. The description of the individual node is given below:

Datasets: Datasets retrieve data from RDP class. It acts as a bridge between D365 and the SSRS report. Only the fields added in the datasets can be used in a report.

Designs: It defines the layout of the report.

Images: It contains the images that you want to display in the SSRS report.

Data Methods: It contains the business logic which can then be used in the report.

Parameters: It is used to apply filtering to the data in a report. All the parameters defined in the data contract class are automatically added here when the RDP class is defined in the datasets.

- Now you will want to create a new Dataset by right clicking Datasets add dataset. Name it DsMain.

- Now select the DsMain dataset and open the properties window. Set the Data Source Type to Report Data Provider. Then select the Query field. An ellipse button appears. Click it to open a dialog box.

- This dialog box lists all the RDP classes present in the AOT. Select dev_CustReportRDPDemoDPand press Next.

Select the fields to be displayed in the report and press OK. Only the fields selected in this dialog box can be shown in the report.

There are two types of designs that can be created in a SSRS report:

1.       Auto design

2.      Precision Design

In this demo we will use Precision Design

Now right click the Designs node Add >> Precision Design. A new design is added. Rename it Design. It is recommended that you set the name of the Design to either ‘Design ‘or ‘Report‘.

Then select report print layout. Our target is A4 page. so set it from report properties. For that go to Report >> Report Properties.

Now design the report design as per your needs.

After that right click on report in solution explorer and click on deploy.

Now add Display menu item like below.

After the add new display menu Item update the its following properties.

Here there is one advancement, in AX 2012 there were a difficult to debug report. But in Dynamics 365 for operation its very simple. Just put break point and set report as starting object and run the project.

Now new browser window you find similar form.

 Select parameter. Click on ok.

• Twitter
• Facebook
• Google
• Tumblr
• Pinterest