There are different ways to create SSRS report in MS Dynamics Ax 2012, mostly depending upon different type of data source used for creating report. There are several ways to retrieve data for reports. One of them is to use the Report Data Provider Class or RDP Class.

The SQL Reporting Services solution for Microsoft Dynamics AX 2012 includes a data source type called Report Data Provider (RDP) which can be used to build reports that have data from an X++ class as the source. Custom business logic to be rendered in reports using predefined X++ classes. Also, can bind parameter elements to Report Definition Language (RDL) expressions using the expression editor.

The following two classes are required to be able to set Report Data Provider as data source type on a report with parameters.

Report Data Provider (RDP) Class: A report data provider (RDP) class is an X++ class that is used to access and process data for a report. A RDP class processes the business logic based on a specified parameter and/or query and returns a dataset to the reporting services. Report Data Provider Class processes business logic based on a query and parameters defined in the data contract class, and then returns the tables as a dataset for the report. A RDP class is an appropriate data source type when the following conditions are met:

1. One cannot query directly for the data what is required to render on a report.

2. The data to be processed and displayed is from Microsoft Dynamics AX.

In order to create a RDP class in AX, one has to extend that class with

RSReportDataProviderBase. This informs AX that this class will be used by reporting services to process the data.

Two important attributes are used in RDP classes:

1. SRSReportQueryAttribute: If the RDP class uses an AOT query to process data, then SRSReportQueryAttribute specifies which AOT query will be used in this report.

2. SRSReportParameterAttribute: If the RDP class contains any parameters, defines the data contract class that will be used by this report to prompt for parameter values.

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.

Now, to pass on parameters so that a report parameter provides a way to choose report data. The parameters that an RDP class will reference are defined in a data contract class.

Data Contract Class: Data Contract Class defines the parameters in the report.

A data contract is an X++ class that has getters, setters and the DataContractAttribute attribute. One create the RDP and data contract classes in the MorphX code editor.

A data contract class has methods with the DataMemberAttribute attribute. The name that follows the attribute is the parameter name that displays in Visual Studio when one bind a report data set to the RDP class.

After processing the business logic it requires to hold the data in a table (temporary), so as to return the table to render in the report. 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 data set is small, like 1000 records.

• Use a TempDB temporary table for large data sets to improve performance.

To do this the following set of tasks will be needed:

1. Create a Shared Project

2. Create a Table

3. Create a Data Contract Class

4. Create a Report Data Provider (RDP) Class

5. Create a reporting project

6. Create a report

7. Build and Publish the Report

Pre-Requisites

To complete this example, the followings are required:

• Microsoft Dynamics AX with sample data

• Microsoft Visual Studio 2010

STEP 1 - Create a Shared Project

Creating a Shared Project help in moving the reports from Development and Testing Environment to the Live servers. For this example, a shared project is defined within the Microsoft Dynamics AX development environment (AOT – can be opened by Pressing CTRL+D while running the MS Dynamics Client Application, and having proper credential). The following procedure explains how to create a shared project.

To create a shared project

1. Open Microsoft Dynamics AX.

2. In the AOT, right-click the Queries node, and then click New Query.

3. Select Project Menu Item from View Menu Group or press CTRL+SHIFT+P.

4. Expand the Shared Folder

5. Right Click and add a New Project, name it as _SNMMrirReportData

STEP 2 – Create a Table

The table will hold data from the business logic class temporarily to render the data to the SSRS report. As the approximate count of row will not be more than 1000 so an InMemory Table Type could be used in this case.

To create a temporary Table

1. In the AOT, Expand the Data Dictionary node, then right-click on the Table node, and then click New Table.

2. Name the Table as _SNMMrirTmp

3. In the TableType put InMemory Value from the dropdown.

4. Add required Fields, by either creating as a new field or by dragging the Field name from tables which contains field (from where data is retrived in the business logic class)

STEP 3 – Create a Data Contract Class

The Data Contract Class will define the parameters, class for the parameters, and validation.

To create a Data Contract Class

1. In the AOT, right-click the Classes node, and then click New Class.

2. Name the Class as zz_SNMMrirContract. Please note that do not create name of any class with first character as _, as it will not be processed by C# compiler while making the Visual Studio Project.

3. Now create three methods, classDeclaration, parmFromDate, parmToDate, as shown below.

If you are new to the X++ development, please stick to the naming convention used in this example.

Listing of the Codes

1. classDeclaration:

class zz_SNMMrirContract

{

ToDate todate;

FromDate fromDate;

}

2. parmFromDate:

[

DataMemberAttribute('FromDate'),

SysOperationLabelAttribute('From Date :'),

SysOperationDisplayOrderAttribute('1')

]

public FromDate parmFromDate(FromDate _fromDate = fromDate)

{

fromDate = _fromDate; return fromDate;

}

3. parmToDate:

[

DataMemberAttribute('ToDate'),

SysOperationLabelAttribute('To Date :'),

SysOperationDisplayOrderAttribute('2')

]

public ToDate parmToDate(ToDate _toDate = todate)

{

todate = _toDate; return todate;

}

STEP 4 – Create a Report Data Provider (RDP) Class

The Report Data Provider (RDP) Class will get the parameters and process the data with the help of a business logic class. The result of the class will be stored into a temporary table (_SNMMrirTmp).

To create a Report Data Provider (RDP) Class

1. In the AOT, right-click the Classes node, and then click New Class.

2. Name the Class as zz_SNMMrirDP.

3. Now create four methods, classDeclaration, get_SNMMrirTmp, getReportParameters, processReport as shown below. Please note the methods getReportParameters and processReport as mandatory and having specific role to render the data for SSRS, so please include these two methods and name should be as mentioned.

Listing of the Codes

1. classDeclaration:

[

SRSReportParameterAttribute(classstr(zz_SNMMrirContract))

]

public class zz_SNMMrirDP extends

SRSReportDataProviderBase/*SrsReportDataProviderPreProcess*/

{

_SNMMrirTmp SNMMrirTmp;

FromDate fromDate;

ToDate toDate;

}

2. get_SNMMrirTmp:

[

SRSReportDataSetAttribute(tablestr(_SNMMrirTmp))

]

public _SNMMrirTmp get_SNMMrirTmp()

{

select SNMMrirTmp; return SNMMrirTmp;

}

3. getReportParameters:

/// <summary>

/// Copies the report parameters from a data contract to the member variables.

/// </summary> private void getReportParameters()

{

zz_SNMMrirContract contract = this.parmDataContract(); if (contract)

{

todate = contract.parmToDate(); fromdate = contract.parmFromDate();

}

4. processReport: [SysEntryPointAttribute] public void processReport()

{

VendPackingSlipJour vendPackingSlipJour;

VendPackingSlipTrans vendPackingSlipTrans;

InventDim inventDim;

Query query = this.parmQuery(); QueryRun queryRun; str strPSlpNo;

this.getReportParameters();

query.dataSourceTable(tableNum(VendPackingSlipJour)).addRange(fieldNum(VendP ackingSlipJour,DeliveryDate)).value(queryRange(fromdate,todate));

query.dataSourceTable(tableNum(VendPackingSlipJour)).addSortField(fieldNum(Ve ndPackingSlipJour,PackingSlipId));

//query.dataSourceTable(tableNum(VendPackingSlipJour)).addRange(fieldNum(Ven dPackingSlipJour,PackingSlipId)).value(packingslipid); queryRun = new QueryRun(this.parmQuery());

strPSlpNo="XXXXX"; while (queryRun.next())

{

vendPackingSlipJour = queryRun.get(tablenum(VendPackingSlipJour)); vendPackingSlipTrans = queryRun.get(tablenum(VendPackingSlipTrans)); inventDim = queryRun.get(tablenum(InventDim));

if(vendPackingSlipJour.PackingSlipId==strPSlpNo)

{

SNMMrirTmp.SameMrir = 1;

}

else

{

SNMMrirTmp.SameMrir = 0; strPSlpNo=vendPackingSlipJour.PackingSlipId;

}

SNMMrirTmp.AcceptedQty_IN = vendPackingSlipTrans.AcceptedQty_IN;

SNMMrirTmp.DeliveryDate = vendPackingSlipJour.DeliveryDate;

SNMMrirTmp.OrderAccount = vendPackingSlipJour.OrderAccount;

SNMMrirTmp.VendorName =

VendTable::find(vendPackingSlipJour.OrderAccount).name();

SNMMrirTmp.InventColorId= inventDim.InventColorId;

SNMMrirTmp.InventQty = vendPackingSlipTrans.InventQty;

SNMMrirTmp.InventSiteId = inventDim.InventSiteId;

SNMMrirTmp.InventSizeId = inventDim.InventSizeId;

SNMMrirTmp.InventStyleId = inventDim.InventStyleId;

SNMMrirTmp.ItemId = vendPackingSlipTrans.ItemId;

SNMMrirTmp.ItemDescription = strFmt("%1 - %2 - %3",

InventTable::find(vendPackingSlipTrans.ItemId).itemDescriptionOrName(), inventDim.InventSizeId, inventDim.InventStyleId);

SNMMrirTmp.PackingSlipId = vendPackingSlipJour.PackingSlipId;

SNMMrirTmp.PurchUnit = vendPackingSlipTrans.PurchUnit;

SNMMrirTmp.Qty = vendPackingSlipTrans.Qty;

SNMMrirTmp.Tri_AccountDate = vendPackingSlipJour.Tri_AccountDate;

SNMMrirTmp.Tri_Accounts = vendPackingSlipJour.Tri_Accounts;

SNMMrirTmp.Tri_AccountsReject = vendPackingSlipJour.Tri_AccountsReject;

SNMMrirTmp.Tri_AccountsRejectText = vendPackingSlipJour.Tri_AccountsRejectText;

SNMMrirTmp.Tri_BillToPassQty = vendPackingSlipTrans.Tri_BillToPassQty;

SNMMrirTmp.Tri_Deviation = vendPackingSlipTrans.Tri_Deviation;

SNMMrirTmp.Tri_DueDate = vendPackingSlipJour.Tri_DueDate;

SNMMrirTmp.Tri_IsInvoiced = vendPackingSlipJour.Tri_IsInvoiced; SNMMrirTmp.Tri_IsRejection = vendPackingSlipJour.Tri_IsRejection;

SNMMrirTmp.Tri_JournalNameId = vendPackingSlipJour.Tri_JournalNameId;

SNMMrirTmp.Tri_ModvateDate = vendPackingSlipJour.Tri_ModvateDate;

SNMMrirTmp.Tri_ModvateReceived = vendPackingSlipJour.Tri_ModvateReceived;

SNMMrirTmp.Tri_ModvateStatus = vendPackingSlipJour.Tri_ModvateStatus;

SNMMrirTmp.Tri_QuantityChallan = vendPackingSlipTrans.Tri_QuantityChallan;

SNMMrirTmp.Tri_RefJournalBook = vendPackingSlipJour.Tri_RefJournalBook;

SNMMrirTmp.Tri_Rejection = vendPackingSlipTrans.Tri_Rejection;

SNMMrirTmp.Tri_RejectionQty = vendPackingSlipTrans.Tri_RejectionQty;

SNMMrirTmp.Tri_Remarks = vendPackingSlipJour.Tri_Remarks;

SNMMrirTmp.Tri_RmaNo = vendPackingSlipTrans.Tri_RmaNo;

SNMMrirTmp.Tri_StoreSubmittedDate = vendPackingSlipJour.Tri_StoreSubmittedDate;

SNMMrirTmp.Tri_Submitted = vendPackingSlipJour.Tri_Submitted;

SNMMrirTmp.Tri_VendBillAmount = vendPackingSlipJour.Tri_VendBillAmount;

SNMMrirTmp.Tri_VendorBillNumber = vendPackingSlipJour.Tri_VendorBillNumber;

SNMMrirTmp.Trident_TruckNumber = vendPackingSlipJour.Trident_TruckNumber;

SNMMrirTmp.ValueMST = vendPackingSlipTrans.ValueMST; SNMMrirTmp.insert();

}

Please note that strPSlpNo variable is used to supress printing of some data field for a repeating row. For this other methods are also available.

STEP 5 – Create a Report Project

To create a Report, run Microsoft Visual Studio 2010. Then create a reporting project, you can choose from two project templates: Visual Basic and Visual C#. Most popular is Visual C# template.

To create a reporting project

1. Open Microsoft Visual Studio 2010.

2. On the File menu, point to New, and then click Project. The New Project dialog box displays.

3. In the Project Types pane, click the Visual C# node, and in the Templates pane, click Dynamics AX Reporting Project.

4. In the Name box, type _SNMMrirReportDateToDate, and in the Location box, type a location.

5. Click OK.

A reporting project contains a model by default. A model is a file with the .moxl file name extension.

STEP 6 - Creating a Report

After creation of reporting project, ready to create the report. The following procedure explains how to create the report.

To create a report

1. In Model Editor, right-click the Report1 node, and then click Rename.

2. Type _SNMMrirReportDateToDate as the name.

3. Expand the _SNMMrirReportDateToDate node if it is not already expanded.

4. Right-click the Datasets node, and then click Add Dataset.

5. Select the node for the data set.

6. In the Properties window, specify the following values.

Property	Value
Data Source	Dynamics AX
Data Source Type	Report Data Provider
Default Layout	Table
Name	zz_SNMMrirDP
Query	Click the ellipsis button (…). A dialog box displays where you can select a query that is defined in the AOT and identify the fields that you want to use. Select the zz_SNMMrirDP class, and then select the all fields by selecting the All check box.