SSRS (SQL Server Reporting Services) report via AX 2009

Requirement:  Create one SSRS report which print compared salary of an employee or group of an employee under the following criteria:

·         Report parameters/filters

o   Employee Id/Employee Name: user can select one or “ALL”

o   From date and to date.

o   Having Pay element code = “Basic” (Employee form {Payroll >> Payroll element})

o   Having Pay roll period  = “MonthlyEM” (Employee form {Payroll >> Payroll element})

Brief Steps:

1.                Created a table for fetching value in report.

2.                Created class for fetching values in created table and for creating lookup in report.

3.                Edited AOT’s web> web files> static files> proxies for passing value in visual studio.

4.                Created dynamics project (report) in visual studio.

Elaborated Steps:

[Note: Create the required object in one project area.]

1.  Created a table for fetching value in report.
Create a table in AOT with name “SalaryComparisonTable” having required field which you want to print in your report.

Field	Type(EDT, if available)
EmplId	String (EmplId)
Name	String(Name)
EffectiveDate	Date(TransDate)
OldSalary	Real (Amount)
NewSalary	Real (Amount)
IncrementAmount	Real (Amount)	IncrementAmount  = NewSalary- OldSalary

2.   Created class for fetching values in created table and for creating lookup in report

Class SalaryComparisonClass {   SalaryCompareTable                               globalSalaryCompareTable;  // table buffer }

static System.Data.DataTable getEmplFilter() {  str                                                      CompId;  str                                                      EmplIdTxt;  System.Data.DataTable                      dtCompanyName;  System.Data.DataRow                       drCompanyName;  System.Data.DataRowCollection        drcCompanyName;  System.Data.DataColumnCollection    dccCompanyName;  SysCompanyUserInfo                         sysCompanyUserInfo =  SysCompanyUserInfo::current();  UserName                                          userName;  InteropPermission                               ip = new  InteropPermission(InteropKind::ClrInterop);  Empltable                                           empltable;  ;     ip.assert();     dtCompanyName = new System.Data.DataTable();     CompId = curext();     EmplIdTxt = curuserid();     dccCompanyName =   dtCompanyName.get_Columns();     dccCompanyName.Add("EmplId");     dccCompanyName.Add("EmplName");     drcCompanyName =   dtCompanyName.get_Rows();     drCompanyName   =   dtCompanyName.NewRow();     drCompanyName.set_Item("EmplId",'ALL');     drCompanyName.set_Item("EmplName",'ALL');     drcCompanyName.Add(drCompanyName);     while select empltable     {      drCompanyName   =   dtCompanyName.NewRow();      drCompanyName.set_Item("EmplId", empltable.EmplId);                              drCompanyName.set_Item("EmplName",EmplTable::find(empltable.EmplId).name());            drcCompanyName.Add(drCompanyName);     }     Return dtCompanyName; }

static System.Data.DataTable getEmployeeSalaryCompareTable(EmplId       _emplId,                                                                                                 TransDate _fromDate,                                                                                                   TransDate   _toDate)                                                            {     System.Data.DataTable                       dtSalaryCompTable;     System.Data.DataRow                        drSalaryCompTable;     System.Data.DataRowCollection         drcSalaryCompTable;     System.Data.DataColumnCollection    dccSalaryCompTable;     InteropPermission                                ip = new InteropPermission(InteropKind::ClrInterop);     SalaryCompareTable                            salaryCompareTable;  // table buffer     SalaryComparisonClass                        salaryComparisonClass; // Class object         ;     ip.assert();     salaryComparisonClass = new SalaryCompareTable ();     dtSalaryCompTable = new System.Data.DataTable();         salaryComparisonClass.loadSalaryComparisonRecord(_emplId,_fromDate,_toDate); // insert record in salaryCompareTable              dccSalaryCompTable =   dtSalaryCompTable.get_Columns();     dccSalaryCompTable.Add("EmplId");     dccSalaryCompTable.Add("Name");     dccSalaryCompTable.Add("Effective Date");     dccSalaryCompTable.Add("Old Basic Salary");     dccSalaryCompTable.Add("New Basic Salary");     dccSalaryCompTable.Add("Increment Amount");         drcSalaryCompTable =   dtSalaryCompTable.get_Rows();     while Select salaryCompareTable order by EmplId asc     {         if(salaryCompareTable.NewBasicSalary != 0.0)         {             drSalaryCompTable   =   dtSalaryCompTable.NewRow();             drSalaryCompTable.set_Item("EmplId", salaryCompareTable.EmplId);             drSalaryCompTable.set_Item("Name", salaryCompareTable.Name);             drSalaryCompTable.set_Item("Effective Date",salaryCompareTable.EffectiveDate);             drSalaryCompTable.set_Item("Old Basic Salary", round(salaryCompareTable.OldBasicSalary, 0.01));             drSalaryCompTable.set_Item("New Basic Salary", round(salaryCompareTable.NewBasicSalary,0.01));             drSalaryCompTable.set_Item("Increment Amount", round(salaryCompareTable.IncrementAmount,0.01));             drcSalaryCompTable.Add(drSalaryCompTable);         }     }     return dtSalaryCompTable; }

public void loadSalaryComparisonRecord(EmplId             _emplId,                                                                 TransDate         _fromDate,                                                                 TransDate         _toDate)                                        {     SalaryComparisonClass                           salaryComparisonClass; // Class object     PYLEmplPayElementsTable                    emplPayElementsTable;     EmplTable                                                  emplTable;         if ( _emplId != 'ALL')     {         delete_from globalSalaryCompareTable;         ttsbegin;         select emplTable             where   emplTable.EmplId            == _emplId;                         {             while select emplPayElementsTable                 where   emplPayElementsTable.EmplId         == emplTable.EmplId                     &&  emplPayElementsTable.EffectiveDate  >= _fromDate                     &&  emplPayElementsTable.EffectiveDate  <= _toDate                     &&  emplPayElementsTable.PayElementCode == "Basic"                     &&  emplPayElementsTable.PayrollPeriod  == "MonthlyEM"             {                 globalSalaryCompareTable.EmplId                      = _emplId;                 globalSalaryCompareTable.Name                       = EmplTable::find(_emplId).name();                 globalSalaryCompareTable.EffectiveDate         = emplPayElementsTable.EffectiveDate;                 globalSalaryCompareTable.OldBasicSalary       = emplPayElementsTable.Amount;                 globalSalaryCompareTable.insert();                            }             this.loadNewBasicSalary(emplTable.EmplId);         }         ttscommit;     }     else if (_emplId == 'ALL')     {          delete_from globalSalaryCompareTable;          ttsbegin;          while select emplTable                  {             while select emplPayElementsTable                 where   emplPayElementsTable.EmplId         == emplTable.EmplId                     &&  emplPayElementsTable.EffectiveDate  >= _fromDate                     &&  emplPayElementsTable.EffectiveDate  <= _toDate                     &&  emplPayElementsTable.PayElementCode == "Basic"                     &&  emplPayElementsTable.PayrollPeriod  == "MonthlyEM"             {                 globalSalaryCompareTable.EmplId                = emplTable.EmplId;                 globalSalaryCompareTable.Name                  = EmplTable::find(emplTable.EmplId).name();                 globalSalaryCompareTable.EffectiveDate         = emplPayElementsTable.EffectiveDate;                 globalSalaryCompareTable.OldBasicSalary        = emplPayElementsTable.Amount;                 globalSalaryCompareTable.insert();                             }             this.loadNewBasicSalary(emplTable.EmplId);         }         ttscommit;     } }

//insert new basic salary public void loadNewBasicSalary(EmplId            _emplId) {     PYLEmplPayElementsTable              pylEmplPayElementsTable;     int                                                     totalRec, reportRec, old;     Amount                                            newBasic;     SalaryCompareTable                        localSalaryCompareTable;  // table buffer        Container                                         oldSalary;     ;     totalRec = 1;     reportRec = 2;     while select pylEmplPayElementsTable         where   pylEmplPayElementsTable.EmplId          == _emplId             &&  pylEmplPayElementsTable.PayElementCode  == "Basic"             &&  pylEmplPayElementsTable.PayrollPeriod   == "MonthlyEM"         {             oldSalary = conins(oldSalary,totalRec,pylEmplPayElementsTable.Amount);             totalRec++;         }     ttsbegin;     for(old = 1 ; old <= totalRec; old++)     {         while select forupdate localSalaryCompareTable             where localSalaryCompareTable.EmplId == _emplId             {                 if(localSalaryCompareTable.OldBasicSalary == conpeek(OldSalary,old))                 {                     if(reportRec <= totalRec)                     {                         localSalaryCompareTable.NewBasicSalary = conpeek(OldSalary,reportRec);                         newSalaryCompareTable.update();                         reportRec++;                     }                 }             }     }     ttscommit; }

3.       Edited AOT’s web> web files> static files> proxies for passing value in visual studio.

/class: SalaryComparisonClass     /method: SalaryComparisonClass.loadNewBasicSalary     /method: SalaryComparisonClass.loadSalaryComparisonRecord     /method: SalaryComparisonClass.GetEmplFilter     /method: SalaryComparisonClass.getEmployeeSalaryCompareTable

4.       Created dynamics project (report) in visual studio.
Step I: Open the visual studio 2008.

  

Step II: Create new project:

  

Step III: Select dynamics project (report):

                           

Step IV: Edit name of report 1 accordingly.

                          

Step V: Create the business logic i.e. data methods. 

                        

Data methods

using System;    using System.Collections.Generic;    using System.Security.Permissions;    using System.Data;    using Microsoft.Dynamics.Framework.Reports;    public partial class SalaryComparisonReport    {     // employee filter     [DataMethod(), AxSessionPermission(SecurityAction.Assert)]     public static System.Data.DataTable GetEmplFilter()     {         DataTable dtEmplLookup = new DataTable();         AxaptaWrapper axapta = SessionManager.GetSession();         dtEmplLookup = (DataTable)axapta.CallStaticClassMethod("SalaryComparisonClass", "getEmplFilter");         return dtEmplLookup;     }     // main data fetch     [DataMethod(), AxSessionPermission(SecurityAction.Assert)]     public static System.Data.DataTable getComaparedData(string _emplId, DateTime _fromDate, DateTime _todate)      {         DataTable dtSalaryTable = new DataTable();         AxaptaWrapper axapta = SessionManager.GetSession();         dtSalaryTable =  (DataTable)axapta.CallStaticClassMethod("SalaryComparisonClass", "getEmployeeSalaryCompareTable", _emplId, _fromDate, _todate);         return dtSalaryTable;      }    }

Step VI: Check each and every property of data sets and make sure the report is working fine in visual studio.

Step VII: Go to parameters section you will see the parameters, change and check each n every properties and set accordingly for each parameters

Step VIII: After data set and parameters section go to design section and create one precision design and you can change name of design accordingly.   

Step IX: Here you can design the report as per your requirements with the help of tools and data sets.

Step X: After designing the layout of your report click report to see you report at visual studio level.

After filling the required parameter you can see your report by clicking Report tab.

5.       Deployment of report.

•  Right click on report solution in visual studio click “save to AOD” option.
•  Launch AX 2009 application in your system.
•  Open that project area which you have created for the report.
•  Create an object for ReportLibreries.
•  Drag and drop your saved reportLibrary.
•  Right click to your library and select option deploy. 
• Create an output menuItems and fill properties accordingly.
• Place the output menuItems to the location where you want.