Langsung ke konten utama

How to Create SSRS Report with Report Data Provider (RDP) Class in MS Dynamics Ax 2012

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.

5.    Add a Precision Design, by right clicking on the Design node.

6.    Name the Design as reportMrirRegisterDateToDate.


7.    Design a SSRS Report.


8.    Test the Report designed.



9.    Deploy the report so that can be called from the Ax Client.

Komentar

Postingan populer dari blog ini

Format Label Tom Jerry (MS Word)

Setelah beberapa lama aku tidak membuat label undangan, tiba-tiba kemarin aku diminta untuk membuat label undangan Wisuda dengan menggunakan label merek Tom & Jerry (TJ). Harusnya sih menjadi tugas sekretaris panitia tapi karena yang bersangkutan ada tugas di luar kantor maka aku yang membereskannya. Mula-mula aku membuat format labelnya dengan cara manual, tapi untung ada pak Mulyadi (staf tata usaha) yang menyarankan aku untuk mengunduh format label yang telah ada di Internet. Mulailah aku browsing di google dan menemukan format label berbagai ukuran dari website Tom & Jerry di alamat berikut  http://tjlabels.com/en/download.html  (yang link ini agak rempong krn pake masukin nomor captcha-captcha-an). Sedangkan kalau yang link ini langsung pilih file ga pake nomor captcha-captcha-an ( http://tjlabels.com/assets/download/ ). Ternyata juga ada banyak berbagai blog yang menyediakan  link download seperti ini, tapi aku mem- posting  artikel ini adalah untuk diri sendiri, ya

Solved : Update on a valid time state table is not allowed without specifying a ValidTimeStateUpdateMode

How To Solve Error :  Cannot edit a record in Position hierarchies (xyz). Update on a valid time state table is not allowed without specifying a ValidTimeStateUpdateMode. select forUpdate * from xyz where xyz.recid== _RecId ; try { ttsBegin; _Value= “Abcd”; xyz.Name = _Value; xyz.validTimeStateUpdateMode(ValidTimeStateUpdate::Correction); xyz.ValidFrom =today(); xyz.ValidTo=dateMax(); xyz.update(); ttsCommit; } catch { ttsAbort; }

Cara membuat Harddisk External menjadi Bootable USB untuk install Windows

Untuk install windows dari harddisk external, silahkan siapkan hal hal berikut ini gan : Harddisk External File Image (ISO) dari Windows yang mau digunakan Laptop/ Komputer   Oke, kita masuk ke  LANGKAH PERTAMA :   Setelah kita persiapkan hal hal yang ada diatas, ada baiknya kalo antivirus / Windows Defender nya itu matiin dulu (recommended) bagi harddisk nya terdapat banyak crack. Karena crack itu bersifat virus dan pasti bakalan langsung keapus sama antivirusnya. Pastiin kalo harddisk ente itu ada space minimal 5 – 10 Gb untuk penempatan File Image Windows yang akan dipakai. Recommended 10Gb Abis harddisk nya di colokkk silahkan buka  Control Panel  >  Administrative Tools    Lalu buka  Computer Management Kalo udah kebuka, pilih menu  Disk Management  yang ada di toolbar kiri. Contoh dibawah ini   Karena ane make harddisk yang merknya Toshiba, maka yang akan kedetek itu  Wibi Toshiba  pada bagian  Disk 1 Langung  Kli