SSRS Report Data Provider class in Ax 2012

Description:-

There are multiple methods to develop SSRS reports in Microsoft Dynamics AX 2012. This tutorial will guide you in developing Report Data Provider (RDP) based SSRS reports.

RDP based SSRS Reports are used when complex business logic cannot be achieved using AOT query.

Pre-requisites

·         Microsoft Dynamics AX 2012

·         Visual studio 2012

·         SQL Server Reporting Services (SSRS) must be configured

·         Reporting services extensions must be installed in Dynamics AX

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. InMemory temporary table is used when the data set is small, while TempDB is normally used for larger datasets to improve performance.

Report Data Provider (RDP) Class declaration

If you are passing parameter through contract class.

[SRSReportParameterAttribute(classStr(ItemTransactionSummaryContract))]
class ItemTransactionSummaryDp extends SRSReportDataProviderBase
{
    ItemTransactionSummaryContract  contract;
    ItemTransactionSummary          ItemTransactionSummary;
    BaseDate                        _FromDate,_ToDate;
    NoYesId                         _IsDeamensionEnable;
}

Add a new method and name it getItemTransactionSummary. This method is mandatory because reporting services uses this method to get the table buffer containing the processed data.

The SRSReportDataSetAttribute attribute is used to indicate the temporary table name and also tells the reporting services to use this method to retrieve the processed data.

Write the following code in the method:

[SRSReportDataSetAttribute(tableStr("ItemTransactionSummary"))]
public ItemTransactionSummary getItemTransactionSummary()
{
    select * from ItemTransactionSummary;
    return ItemTransactionSummary;
}

Add a new method and name it processReport. This method contains the business logic and is called by reporting services to generate data.

This method will query item transaction details and fill the temporary table buffer. Write the following code in the method:

/// Processes the SQL Server Reporting Services report business logic
/// This method provides the ability to write the report business logic. This method will be called by
/// SSRS at runtime. The method should compute data and populate the data tables that will be returned
/// to SSRS.
[SysEntryPointAttribute]
public void processReport()
{
    InventTrans                         InventTrans;
    InventTransOrigin                   InventTransOrigin;
    
    //Itemid List Object Start
    List                    list1 = new List(Types::String);
    Query                   queryItemId;
    QueryRun                queryRunItemId;
    InventItemGroupItem     InventItemGroupItem;
    QueryBuildDataSource    qbdsItemId;
    ListIterator            ItemIdListIterator;
    //Itemid List Object end    

    contract            = this.parmDataContract() as ItemTransactionSummaryContract;
    _FromDate           = contract.ParmFromDate();
    _ToDate             = contract.ParmToDate();
    _IsDeamensionEnable = contract.parmDiamension();
    //list              = contract.parmItemGroup();
    list1               = contract.parmItemId();   
    
    //Itemid List start
    if(list1 != null)
    {
        ItemIdListIterator  = new ListIterator(list1);
        queryItemId         = new Query(queryStr(dev_ItemTransSumItemIdQry));
        qbdsItemId          = queryItemId.dataSourceTable(tableNum(InventItemGroupItem));

        while(ItemIdListIterator.more())
        {
            qbdsItemId.addRange(fieldNum(InventItemGroupItem, ItemId)).value(ItemIdListIterator.value());
            qbdsItemId.addRange(fieldNum(InventItemGroupItem, ItemDataAreaId)).value(curExt());
            qbdsItemId.addRange(fieldNum(InventItemGroupItem, ItemGroupDataAreaId)).value(curExt());
            ItemIdListIterator.next();
        }
        queryRunItemId = new QueryRun(queryItemId);
        while(queryRunItemId.next())
        {
            InventItemGroupItem = queryRunItemId.get(tableNum(InventItemGroupItem));

            //Get start
            InventTrans.clear();
            InventTransOrigin.clear();

            while select sum(Qty) from InventTrans
            order by InventTrans.DatePhysical
            group by InventTrans.DatePhysical,InventTrans.StatusReceipt,
                InventTrans.ItemId,InventTransOrigin.RecId,InventTransOrigin.ReferenceCategory,
                InventTransOrigin.ReferenceId,InventTransOrigin.ItemId,
                InventTransOrigin.InventTransId,InventTrans.inventDimId
     join InventTransOrigin
            where InventTrans.InventTransOrigin == InventTransOrigin.RecId
                && (InventTrans.StatusReceipt == StatusReceipt::Purchased ||
                    InventTrans.StatusReceipt == StatusReceipt::Received ||
                    InventTrans.StatusReceipt == StatusReceipt::Arrived ||
                    InventTrans.StatusReceipt == StatusReceipt::QuotationReceipt)
                && (InventTrans.StatusIssue != StatusIssue::Sold ||
                    InventTrans.StatusIssue != StatusIssue::Deducted ||
                    InventTrans.StatusIssue != StatusIssue::Picked ||
                    InventTrans.StatusIssue != StatusIssue::QuotationIssue)
                && InventTrans.DatePhysical != dateNull()
                && InventTransOrigin.ReferenceCategory != InventTransType::InventTransaction
            && InventTrans.DatePhysical >= _FromDate
            && InventTrans.DatePhysical <= _ToDate
            && InventTransOrigin.ItemId == InventItemGroupItem.ItemId
            {
   ItemTransactionSummary.clear();
   ItemTransactionSummary.ItemId         = InventTrans.ItemId;
   ItemTransactionSummary.Name           = EcoResProductTranslation::findByProductLanguage(EcoResProduct::findByDisplayProductNumber(InventTrans.ItemId).RecId,'en-in').Name;
   ItemTransactionSummary.ItemGroup      = inventItemGroupItem::findByItemIdLegalEntity(InventTrans.ItemId).ItemGroupId;
   ItemTransactionSummary.Configuration  = InventDim::find(InventTrans.InventDimId).configId;
   ItemTransactionSummary.InventColorId  = InventDim::find(InventTrans.InventDimId).InventColorId;
   ItemTransactionSummary.InventSizeId   = InventDim::find(InventTrans.InventDimId).InventSizeId;
   ItemTransactionSummary.InventStyleId  = InventDim::find(InventTrans.InventDimId).InventStyleId;
   ItemTransactionSummary.FromDate       = Fromdate;
   ItemTransactionSummary.ToDate         = Todate;
   ItemTransactionSummary.UnitId         = InventTableModule::find(InventTransOrigin.ItemId,ModuleInventPurchSales::Invent).UnitId;
   ItemTransactionSummary.IsDimensionEnable = _IsDeaEnable;
   if(ItemTransactionSummary.validateWrite())
   {
               ItemTransactionSummary.insert();
   }
     }
            //Get End
        }
    }
    //Itemid List end
}

If you are using query based data provider class

Processes business logic based on parameters and a query, and then returns the tables as a dataset for the report. Let me explain all the steps mentioned above with an example.

Step 1: Create a new query and add the datasource as Table.

[SRSReportQueryAttribute (querystr(AOTQueryName)), 
SRSReportParameterAttribute(classstr(ItemTransactionSummaryContract))]
class ItemTransactionSummaryDp extends SRSReportDataProviderBase 
{

}

Add a new method and name it processReport. This method contains the business logic and is called by reporting services to generate data.

[SysEntryPointAttribute(false)] 
public void processReport() 
{ 
    QueryRun       queryRun; 
    Query        query; 
    CustTable       custTable; 
    ItemTransactionSummaryContract  Contract; 
    AccountNum       accountNum; 
    QueryBuildDataSource    queryBuildDataSource; 
    QueryBuildRange     queryBuildRange;

    query = this.parmQuery();

    Contract = this.parmDataContract() as ItemTransactionSummaryContract; 
    accountNum = Contract.parmAccountNum();

    // Add parameters to the query. 
    queryBuildDataSource = query.dataSourceTable(tablenum(CustTable));

    if(accountNum) 
    { 
        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(accountNum); 
    }
    queryRun = new QueryRun(query);
    while(queryRun.next()) 
    { 
        custTable = queryRun.get(tableNum(CustTable)); 
  
 ItemTransactionSummary.AccountNum  = custTable.AccountNum; 
 ItemTransactionSummary.Blocked   = custTable.Blocked; 
 ItemTransactionSummary.PriceGroup  = custTable.PriceGroup; 
 ItemTransactionSummary.Currency  = custTable.Currency; 
 ItemTransactionSummary.insert();
    } 
}

Example for Query and contract class parameter based Data Provider class in Ax 2012
Use a Report Data Provider Class in a Report

Author : Umesh Patel

Related Posts