Interview Questions

Sunday, May 25, 2014

AX2012 Create SSRS Report using Data Provides Classes

Create the SSRS Report in AX2012 using Report Data Provider (RDP) functionality.

RDP implments the standard MVC(Model View Controller) design pattern.
In this post I am building one report which is used in the production packing list report for production module.

What is MVC?

  • Model-view-controller (MVC) is a pattern used to isolate business logic from the user interface.
  • Model: Responsible for retrieving data and for business logic, this can included queries, data methods, or other classes that are designed toretrieve data.
  • View: Responsible for the User Interface, this can also be thought of as the design for the report.
  • Controller: Orchestrates the flow between Model and View



For this Report I am creating the Query,Contract,Controller,DataProvider classes in below.
In this I used lot of my custom fields that we created and used in the functionality

I am extending this functionality with adding builder class to the contract and adding the logic to lookup the data for the contract parmmethods.

for this I am writing the class which extending the SysOperationAutomaticUIBuilder.


TmpTable- ProdPackingSlipDetailsTmp
Query Name-ProdPackList
Contract class-ProdPacklistContract
Controller class-ProdPackListController Extends SRSReportRunController
Data ProvideClass-ProdPacklistDP Extends SRSReportDataProvidePreProcess
SSRS Report-ProdPackList

Starting with Query-Build the Query as the following data sources with the fields and relations.



Create the TMP table as below fields and properties of the table




define Properties of the table mainly createTransactionID=Yes
















Create the Contract  Class first,
This class is used to create parm methods for the reports, So if you have any parameters that you want to pass to report then create parm methods for those as datamembers.

for ex.-

[    DataMemberAttribute('UseQuantity')]
public ProdBOMJournalQty parmUseQuantity(ProdBOMJournalQty _useQuantity = useQuantity)
{
    useQuantity = _useQuantity;
    return useQuantity;
}


In my report I am using Query and adding two parm fields.
I am adding the contract processing through my builder with the following attribute of my contract class.

[DataContractAttribute,
SysOperationContractProcessingAttribute(classstr(ProdPackListUIBuilder)) ]
public class ProdPacklistContract
{
SalesId salesId;
LineNum lineNum
}

[    DataMemberAttribute('SalesId')]
public SalesId parmSalesId(SalesId _salesId=salesId)
{
salesId=_salesId;
return salesId;
}

[ DataMemberAttribute('LineNum')]
public LineNum parmLineNum(LineNum _lineNum=LineNum)
{
 lineNum=_lineNum;
return lineNum;
}

Create Controller class which extends SrsReportRunController

/// <summary>
///    The <c>ProdPackListController</c> class is the controller class for <c>ProdPackList</c> SSRS report.
/// </summary>
public class ProdPackListController extends SrsReportRunController
{
}
Create the Main method where you have to define the reportname and design and this was the method which will calls the report to exectue...starting point of the report.

public static void main(Args _args)
{
    SrsReportRunController    controller = new  ProdPackListController  ();
    controller.parmReportName("ProdPackList.Report");
    controller.parmArgs(_args);
    controller.startOperation();
}



Override the method preRunModifyContract method


protected void preRunModifyContract()
{
    GNProdPacklistContract    contract;
    contract = this.parmReportContract().parmRdpContract() as GNProdPacklistContract;
}


if you want to handle the record count and no records then you can over ride this method and you can handle the no records warnings.
protected container preRunValidate()

{
    // This report is only containing dynamic filters and via testing it's been determined
    // that on warm box it performs under 10 seconds with a 500 records and under 10 minutes
    // with 50000 records. The rest of the contract parameters just define layout and UI, so
    // no additional filtering on those needs to be done in this method.
    // The granularity of the query is determined by the join of ProdJournalBOM and ProdBOM tables.
    #define.ErrorLimit(50000)
    #define.WarningLimit(500)

    container               validateResult;
    Query                   firstQuery = this.getFirstQuery();
    int                     rowCount = QueryRun::getQueryRowCount(firstQuery, #ErrorLimit + 1);

    if (rowCount > #ErrorLimit)
    {
        validateResult = [SrsReportPreRunState::Error];
    }
    else if (rowCount > #WarningLimit)
    {
        validateResult = [SrsReportPreRunState::Warning];
    }
    else if(rowCount <=0)
    {
       // validateResult = [SrsReportPreRunState::Error];
         throw error("No records available for the Selected criteria");
    }
    else
    {
        validateResult = super();
    }

    return validateResult;
}


if you want to pass the values for the report before promting to user for input you can override this method
prePromptModifyContract 

In my case I am overriding to pass the value and ranges for my query before showing the dialog to user.


/// <summary>
///    Sets query ranges based on the caller.
/// </summary>
/// <exception cref="M:Exception::Error">
///    The method is called from a invalid path.
/// </exception>
protected void prePromptModifyContract()
{
    QueryBuildRange         queryBuildRangeSerial;
    QueryBuildRange         queryBuildRangeProd,qbrTransStatus;

    QueryBuildDataSource    queryBuildDataSource,queryBuildDataSource1,queryBuildDataSource2;
    ProdTable               localProdTable;
    Query                   query;
    // get the report query
    query                   = this.parmReportContract().parmQueryContracts().lookup(this.getFirstQueryContractKey());

    queryBuildDataSource    = SysQuery::findOrCreateDataSource(query, tableNum(ProdTable));
    queryBuildDataSource1   = SysQuery::findOrCreateDataSource(query, tableNum(InventDim));
    queryBuildDataSource2   = SysQuery::findOrCreateDataSource(query, tableNum(InventTrans));

    queryBuildRangeProd     = SysQuery::findOrCreateRange(queryBuildDataSource, fieldNum(ProdTable,ProdId));
    queryBuildRangeSerial   = SysQuery::findOrCreateRange(queryBuildDataSource1, fieldNum(InventDim,inventSerialId));
    qbrTransStatus          = SysQuery::findOrCreateRange(queryBuildDataSource2, fieldNum(InventTrans,StatusReceipt));
    qbrTransStatus.value(queryValue(StatusReceipt::Received));

    if (this.parmArgs().dataset() ==  tableNum(ProdTable))
    {
        localProdTable = this.parmArgs().record();
        queryBuildRangeProd.value(queryValue(localProdTable.ProdId));
        this.parmShowDialog(true);
    }
    else if ((this.parmArgs().menuItemName() == menuitemOutputStr(ProdPacklist)) && (this.parmArgs().dataset() ==0))
    {
        this.parmShowDialog(true);
    }
    else
    {
        throw error(strFmt("Packing list can only printed from Production",funcName()));
    }

}


Create DataProvider class which extends SrsReportDataProviderPreProcess
In My report I am using the Barcode setup functionality for printing the serial numbers aswell.


[
    SRSReportQueryAttribute(queryStr(ProdPackList)),
    SRSReportParameterAttribute(classStr(ProdPacklistContract))
]
class ProdPacklistDP extends SrsReportDataProviderPreProcess
{

    boolean                     showQuery;
 
    boolean                     firstPage;
    ProdTable                   prodTable;
    ProdId                      prodId;

    CompanyInfo                 companyInfo;

    ProdBOM                     prodBOM;
    InventDim                   inventDim;

   ProdPackingSlipDetailsTmp prodPackingSlipDetailsTmp;
    BarcodeSetup                barCodeSetup;
    BarcodeSetupId  barcodeSetupId;
    Barcode         barcode;
}


Create the method which return the tmp table object

[
    SRSReportDataSetAttribute(tableStr('ProdPackingSlipDetailsTmp'))
]
public ProdPackingSlipDetailsTmp getProdPacklistDetailsTmp()
{
    select prodPackingSlipDetailsTmp;
    return prodPackingSlipDetailsTmp;
}

override the method ProcessReport where you will write the business logic to fill into thetmp table

/// <summary>
///    Processes the report business logic.
/// </summary>
/// <remarks>
///    Calls the sub methods to insert data into the temporary table.
/// </remarks>
[SysEntryPointAttribute(false)]
public void processReport()
{
    QueryRun                queryRun;
     ProdPacklistContract contract       = this.parmDataContract() as  ProdPacklistContract ;
    // Set the userconnection to use on table.
    // This is required to ensure that createdTransactionId of inserted record is different than default 
           transaction.
    prodPackingSlipDetailsTmp.setConnection(this.parmUserConnection());
    this.init();
    this.setupBarcode();
    queryRun                        = new QueryRun(this.parmQuery());
    while (queryRun.next())
    {
        prodTable       = queryRun.get(tableNum(ProdTable));
        inventDim       = queryRun.get(tableNum(InventDim));
        prodId          = prodTable.ProdId;
        if(prodTable.InventRefType==InventRefType::Sales)
        {
           this.insertHeader();
           this.insertDetails(prodId,inventDim.inventSerialId);
        }
    }
}


private void init()
{
    companyInfo = CompanyInfo::find();
    firstPage   = true;
}





//BP Deviation documented
protected BarCodeString barcode(str _SerialNumber)
{
    str jobId = strupr(_SerialNumber);

    if (barcodeSetup.validateBarcode(jobId))
    {
        barcode.string(true, jobId);
        barcode.encode();
    }
    else
    {
        throw(error(strfmt("@SYS41409", barcode.barcodeType(), jobId)));
    }
    return barcode.barcodeStr();
}


/// <summary>
/// Initialize barcode settings.
/// </summary>
protected void setupBarcode()
{
    barcodeSetupId = JmgParameters::find().getBarcodeSetupId();
    barcodeSetup = BarcodeSetup::find(barcodeSetupId);
    barcode = barcodeSetup.barcode();
}

// assigns the data into header information first

private void insertHeader()
{
    prodPackingSlipDetailsTmp.clear();
    prodPackingSlipDetailsTmp.initValue();
    prodPackingSlipDetailsTmp.SalesId=prodTable.InventRefId;
    prodPackingSlipDetailsTmp.SerialNumber=inventDim.inventSerialId;


    prodPackingSlipDetailsTmp.ProdItemId                = prodTable.ItemId;
    prodPackingSlipDetailsTmp.Description               = prodTable.Name;
    prodPackingSlipDetailsTmp.Customer                  = SalesTable::find(prodPackingSlipDetailsTmp.SalesId).CustAccount;


    prodPackingSlipDetailsTmp.initValue();
    prodPackingSlipDetailsTmp.barcodeSetupId=barCodeSetup.barcodeSetupId;
    prodPackingSlipDetailsTmp.barcodeType=barCodeSetup.barcodeType;
    prodPackingSlipDetailsTmp.fontName=barCodeSetup.fontName;
    prodPackingSlipDetailsTmp.fontSize=barCodeSetup.fontSize;
    prodPackingSlipDetailsTmp.maximumLength=barCodeSetup.maximumLength;
    prodPackingSlipDetailsTmp.minimumLength=barCodeSetup.minimumLength;
    prodPackingSlipDetailsTmp.SerialNumberBarCode=this.barcode(inventDim.inventSerialId);
}


private void insertDetails(ProdId _prodId,InventSerialId _inventSerialId)
{
    SMAServiceObjectTable smaServiceObjectTable;
    SMAServiceBOMTable    smaServiceBOMTable;
    ProdBOM               prodBOMTable;

    while select prodBOMTable order by InventTransId asc
        where prodBOMTable.ProdId==_prodId
    {
        if(InventTable::Find(prodBOMTable.ItemId).PrintItemProduction)
        {
            prodPackingSlipDetailsTmp.ItemId=prodBOMTable.ItemId;
            prodPackingSlipDetailsTmp.Qty=prodBOMTable.BOMQty;
            prodPackingSlipDetailsTmp.Name=prodBOMTable.itemName();
            if(prodBOMTable.SerialnoControlled())
            {
                select TemplateBOMId,ServiceObjectId from smaServiceObjectTable
                    where smaServiceObjectTable.InventSerialId==_inventSerialId
                    && smaServiceObjectTable.ReferenceId==_prodId
                    && smaServiceObjectTable.ReferenceCategory==InventTransType::Production
                join InventSerialId from smaServiceBOMTable where
                    smaServiceBOMTable.ItemId==prodBOMTable.ItemId
                    && smaServiceBOMTable.ObjectId==smaServiceObjectTable.ServiceObjectId
                    && smaServiceBOMTable.ServiceBOMId==smaServiceObjectTable.TemplateBOMId;

                prodPackingSlipDetailsTmp.SerialNo=smaServiceBOMTable.InventSerialId;
            }
            prodPackingSlipDetailsTmp.SalesFormNotes=FormLetterRemarks::find(companyInfo.LanguageId,FormTextType::ProductionPackingList).Txt;
            prodPackingSlipDetailsTmp.insert();
            prodPackingSlipDetailsTmp.SerialNo="";
        }
    }
}

Builder class

public class ProdPackListUIBuilder extends SysOperationAutomaticUIBuilder
        {
            DialogField dialogSalesId;
            DialogField dialogSalesLine;
            SalesId  salesId;
            LineNum  lineNum;
            ProdPacklistContract prodPacklistContract;
            ProdPackListController packListcontroller;
            QueryRun queryRun;
            Query baseQuery;
            ProdTable prodTable;
        }
       public void build()
        {
            FormBuildGroupControl grp;
       
            Dialog      dialogLocal = this.dialog();
            ;
            prodPacklistContract = this.dataContractObject();
            if(this.validateCaller())
            {
                dialogLocal.addGroup("Sales");
                this.addDialogField(methodStr(ProdPacklistContract,parmSalesId), prodPacklistContract);
                this.addDialogField(methodStr(ProdPacklistContract,parmSalesLine), prodPacklistContract);
            }
       }

        public SysOperationController controller()
        {
            SysOperationController ret;
            ret = super();
            packListcontroller=ret;
            return ret;
        }

        private str getFirstQuery()
        {
              // this will just return the first contract key in the Map
            Map queryContracts =packListcontroller.parmReportContract().parmQueryContracts();
            MapEnumerator mapEnum;
            str firstQueryKey;
            if(queryContracts)
            {
                mapEnum = queryContracts.getEnumerator();
                if(mapEnum.moveNext())
                {
                    firstQueryKey = mapEnum.currentKey();
                }
            }
            return firstQueryKey;
        }
        public void getFromDialog()
        {
            prodPacklistContract = this.dataContractObject();
            if(this.validateCaller())
            {
                salesId=dialogSalesId.value();
                LineNum=dialogSalesLine.value();
                this.validateData(salesId,LineNum);
            }
            super();
        }

        public void initializeFields()
        {
            prodPacklistContract = this.dataContractObject();
        }

        private void initquery()
        {
baseQuery=packListcontroller.parmReportContract().parmQueryContracts().lookup(this.getFirstQuery());
            QueryRun=new QueryRun(baseQuery);
            while (queryRun.next())
            {
                prodTable=queryRun.get(tableNum(ProdTable));
                if(prodTable)
                    break;
            }
        }
        public void lookupSalesId(FormStringControl _control)
        {
            Query query = new Query();
            SysTableLookup sysTablelookup;
            sysTablelookup =SysTableLookup::newParameters(tableNum(SalesTable),_control);
            sysTablelookup.addLookupfield(fieldNum(SalesTable,SalesId));
            sysTablelookup.addLookupfield(fieldnum(SalesTable,CustAccount));
            sysTablelookup.addLookupMethod(tableMethodStr(SalesTable,customerName));
       
            query.addDataSource(tableNum(SalesTable));
            if(ProdTable.InventRefType==InventRefType::Sales)
            {
                query.dataSourceTable(tableNum(SalesTable)).addRange(fieldNum(SalesTable, SalesId)).value(queryValue(ProdTable.InventRefId));
            }
            sysTablelookup.parmQuery(query);
            sysTablelookup.performFormLookup();
        }
        public void lookupSalesLine(FormStringControl _control)
        {
            Query query = new Query();
            SysTableLookup sysTablelookup;
            sysTablelookup =SysTableLookup::newParameters(tableNum(SalesLine),_control);
            sysTablelookup.addLookupfield(fieldnum(SalesLine,LineNum));
            sysTablelookup.addLookupfield(fieldnum(SalesLine,Itemid));
            sysTablelookup.addLookupfield(fieldNum(SalesLine,SalesId));
            query.addDataSource(tableNum(SalesLine));
            query.dataSourceTable(tableNum(SalesLine)).addRange(fieldNum(SalesLine, SalesId)).value(dialogSalesId.value());
            query.dataSourceTable(tableNum(SalesLine)).addRange(fieldNum(SalesLine, ItemId)).value(queryValue(prodTable.ItemId));
            if(prodTable.InventRefType==InventRefType::Sales)
            {
                query.dataSourceTable(tableNum(SalesLine)).addRange(fieldNum(SalesLine, InventtransId)).value(queryValue(prodTable.InventRefTransId));
            }
       
            sysTablelookup.parmQuery(query);
            sysTablelookup.performFormLookup();
        }

//Override the postbuild function to bind the lookups for the fields.
        public void postBuild()
        {
            super();
            if(this.validateCaller())
            {
                prodPacklistContract=this.dataContractObject();
                this.initquery();
                // From binding info, get the dialog field for racecode attribute and add button
                dialogSalesId = this.bindInfo().getDialogField(
                                     this.dataContractObject(),
                                     methodStr(ProdPacklistContract,parmSalesId));
                if (dialogSalesId)
                {
                    dialogSalesId.lookupButton(2);
                }
                dialogSalesId.value(" ");
       
                // register override method for lookup cust Group
                dialogSalesId.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(ProdPackListUIBuilder, lookupSalesId), this);
                // register override method for modified
                dialogSalesId.registerOverrideMethod(methodStr(FormStringControl, modified), methodStr(ProdPackListUIBuilder, SalesIdModified), this);
       
                //binding info for customer drop down
                dialogSalesLine = this.bindInfo().getDialogField(this.dataContractObject(),
                                     methodStr(ProdPacklistContract,parmSalesLine));
                dialogSalesLine.value(" ");
                // register override method for lookup customer
                dialogSalesLine.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(ProdPackListUIBuilder, lookupSalesLine), this);
       
                if (dialogSalesLine)
                {
                    dialogSalesLine.lookupButton(2);
                }
                if(ProdTable.InventRefType==InventRefType::Sales)
                {
                    dialogSalesId.value(prodTable.InventRefId);
                    dialogSalesLine.value(SalesLine::findInventTransId(prodTable.InventRefTransId).LineNum);
                    prodPacklistContract.parmSalesId(prodTable.InventRefId);
                    prodPacklistContract.parmSalesLine(SalesLine::findInventTransId(prodTable.InventRefTransId).LineNum);
                    dialogSalesId.enabled(false);
                    dialogSalesLine.enabled(false);
                }
                if(prodTable.RecId==0)
                {
                    throw error(strFmt("please select valid production order"));
                }
            }
            else
            {
                    prodPacklistContract.parmSalesId("");
           }
        }
// if the salesId is modified we have to load the sales lines 
        public boolean SalesIdModified(FormStringControl _control)
        {
            dialogSalesId.value(_control.valueStr());
            dialogSalesLine.value('');
            return true;
        }
        private boolean validateCaller()
        {
            if((packListcontroller.parmArgs().menuItemName() == menuitemOutputStr(ProdPacklist)) && (packListcontroller.parmArgs().dataset() ==0))
                return false;
            else
                return true;
        }

        private void validateData(SalesId _salesId,LineNum _lineNum)
        {
            boolean ret=true;
            SalesLine salesLine;
            if(this.validateCaller())
            {
                    if(_salesId=="")
                        throw error(strFmt("this production is not linked to sales %1",prodTable.ProdId));
       
                    if(_lineNum==0)
                        throw error(strFmt("this production is not linked to sales %1 ",_salesId,prodTable.ProdId));
                    if(ret)
                    {
                        salesLine= SalesLine::find(_salesId,_lineNum);
                        if(salesLine && salesline.ItemId != prodTable.ItemId)
                        {
                            throw error(strFmt("this production is not linked to sales %1 ,%2",salesLine.ItemId,prodTable.ItemId));
                        }
                    }
            }
        }
     
 This was the new functionality for the adding the lookups for the fields using builder class.



After creation of all the classes above mentioned then we have to create the report,
Open .netDevelopment enviorment and create project and select Microsoft Dynamics AX from the Left pane and select the ReportModel Project from the list of project and give your project name and click ok.

















select the project and right click and click add and select report.














It will create the new report as follow.Right click on report and select properties and give the name as ProdPackList.













Select DataSets and right click and click AddDataset and provide the DatasetName and select the dataset that you created and right click and properties.








click on Query it will opens the dialog with the DataProvider classes,in that select the DataProvider class that we built and click next. It will opens the fields selection window where we will select the list of fields that required to show in the report
















based on your requirements you can select or deselect the fields and click on. Now the dataset with the fields are ready for the report.
Now you can drag and drop that dataset on to the designs node, It will generate auomatic design for those fields.
If you want to design your own format then create new precision Design by right click on the designnode and  add precisionDesign and change that design name as we mentioned in the controller class main method(Report).

I Created the Precision design as follows.









This is how my report looks like above.

No comments:

Post a Comment