Creating new SSRS report in AX2012

New SSRS report  in AX2012

1 . Create a temp table with the required fields

2. Create a query with required datasource

3.  Create a contract class (Report filters )

class UBMGrowthNetSalesContract

{

    FromDate            fromDate,fromDate2;

    ToDate              toDate,toDate2;

    InventSiteId        inventSite;

    InventLocationId    inventLocation;

    ItemGroupId         itemGroupId;

 

}

[DataMemberAttribute("FromDate1"),SysOperationDisplayOrderAttribute("1")]

public FromDate parmFromDate(FromDate    _fromDate = fromDate)

{

  fromDate = _fromDate;

  return fromDate;

}

[DataMemberAttribute("FromDate2"),SysOperationDisplayOrderAttribute("3")]

public FromDate parmFromDate2(FromDate    _fromDate2 = fromDate2)

{

  fromDate2 = _fromDate2;

  return fromDate2;

}

[DataMemberAttribute("ToDate1"),SysOperationDisplayOrderAttribute("2")]

public ToDate parmToDate(ToDate    _toDate = toDate)

{

  toDate = _toDate;

  return toDate;

}

 

[DataMemberAttribute("ToDate2"),SysOperationDisplayOrderAttribute("4")]

public ToDate parmToDate2(ToDate    _toDate2 = toDate2)

{

  toDate2 = _toDate2;

  return toDate2;

}

[DataMemberAttribute("Item Group"),SysOperationDisplayOrderAttribute("5")]

public ItemGroupId parmItemGroup(ItemGroupId    _itemGroupId = itemGroupId)

{

  ;

  itemGroupId = _itemGroupId;

  return itemGroupId;

}

[DataMemberAttribute("Warehouse"),SysOperationDisplayOrderAttribute("7")]

public InventLocationId parmLocationId(InventLocationId    _inventLocation = inventLocation)

{

  inventLocation = _inventLocation;

  return inventLocation;

}

[DataMemberAttribute("Site"),SysOperationDisplayOrderAttribute("6")]

public InventSiteId parmSiteId(InventSiteId    _inventSite = inventSite)

{

  inventSite = _inventSite;

  return inventSite;

}

 

4 . Create an RDP class with the required bussiness logic

 

[ SRSReportQueryAttribute (querystr(UBMGrowthNetSales)),

SRSReportParameterAttribute(classstr(UBMGrowthNetSalesContract))

]

class UBMGrowthNetSalesRDP extends SRSReportDataProviderBase

{

  UBMGrowthNetSales uBMGrowthNetSales;

}

 

 

 

[SRSReportDataSetAttribute("UBMGrowthNetSales")]

public ubmGrowthNetSales getUBMGrowthNetSales()

{

    select * from uBMGrowthNetSales;

    return uBMGrowthNetSales;

}

 

 

 

[SysEntryPointAttribute(false)]

public void processReport()

{

    QueryRun                                      queryRun1,queryRun2,queryRun3;

    Query                                             query1,query2,tableQuery,transQuery1,transQuery2;

    UBMGrowthNetSalesContract       ubmGrowthNetSalesContract;

    QueryBuildDataSource            queryBDSTable,qbdsInventTable,qbdsTable,qbdsTrans,qbdsTrans1,qbdsinventDim1,qbdsInventDim2;

    QueryBuildDataSource                  qbdsItemGroup;

    QueryBuildRange                          qbrItem;

    TransDate                                      _toDate,_fromDate,_toDate2,_fromDate2;

    ItemId                                             item,_itemId;

    InventSiteId                                    inventSite;

    InventLocationId                             inventLocationId;

    InventTable                                    inventTable;

    InventTrans                                   inventTrans1,inventTrans2;

    LineAmount                                   lineAmt,lineAmt1;

    InventDim                                      inventDim1,inventDim2;

    InventItemGroupItem                     itemGroupTable;

    ItemGroupId                                  itemGroup;

 

    ;

  //breakpoint;

    ubmGrowthNetSalesContract = this.parmDataContract() as UBMGrowthNetSalesContract;

 

    _toDate             = ubmGrowthNetSalesContract.parmToDate();

    _fromDate           = ubmGrowthNetSalesContract.parmFromDate();

    _toDate2            = ubmGrowthNetSalesContract.parmToDate2();

    _fromDate2          = ubmGrowthNetSalesContract.parmFromDate2();

    inventSite          = ubmGrowthNetSalesContract.parmSiteId();

    inventLocationId    = ubmGrowthNetSalesContract.parmLocationId();

    itemGroup           = ubmGrowthNetSalesContract.parmItemGroup();

 

    query1 = this.parmQuery();

    queryBDSTable = query1.dataSourceTable(tableNum(InventTable));

    qbrItem = queryBDSTable.findRange(fieldNum(InventTable,ItemId));

    item = qbrItem.value();

 

    tableQuery = new Query();

 

    qbdsInventTable = tableQuery.addDataSource(tableNum(Inventtable));

    qbdsInventTable.addRange(fieldNum(inventTable,ItemId)).value(item);

 

    qbdsItemGroup   = qbdsInventTable.addDataSource(tableNum(InventItemGroupItem));

    qbdsItemGroup.joinMode(JoinMode::ExistsJoin);

    qbdsItemGroup.addLink(fieldNum(InventTable,ItemId),fieldNum(InventItemGroupItem,ItemId));

       qbdsItemGroup.addRange(fieldNum(InventItemGroupItem,ItemGroupId)).value(queryValue(itemGroup));

 

    queryRun1 = new QueryRun(tableQuery);

    while(queryRun1.next())

    {

        inventTable = queryRun1.get(tableNum(inventTable));

 

        ubmGrowthNetSales.ItemId        = inventTable.ItemId;

        ubmGrowthNetSales.ItemName      = inventTable.itemName();

        ubmGrowthNetSales.FromDate      = _fromDate;

        ubmGrowthNetSales.FromDate2     = _fromDate2;

        ubmGrowthNetSales.ToDate        = _toDate;

        ubmGrowthNetSales.ToDate2       = _toDate2;

        //Lineamt value calculation

        transQuery1 = new Query();

        qbdsTrans = transQuery1.addDataSource(tableNum(inventTrans));

        qbdsTrans.addRange(fieldNum(Inventtrans,ItemId)).value(inventTable.ItemId);

        qbdsTrans.addRange(fieldNum(inventTrans,DatePhysical)).value(queryRange(_fromDate,_toDate));

 

        qbdsinventDim1 = qbdsTrans.addDataSource(tableNum(InventDim));

        qbdsinventDim1.joinMode(JoinMode::ExistsJoin);

        qbdsinventDim1.addLink(fieldNum(InventTrans,InventDimId),fieldNum(InventDim,inventDimId));

        qbdsinventDim1.addRange(fieldNum(InventDim,inventLocationId)).value(inventLocationId);

        qbdsinventDim1.addRange(fieldNum(InventDim,inventSiteId)).value(inventSite);

 

 

        queryRun2 = new QueryRun(transQuery1);

        while(queryRun2.next())

        {

            inventTrans1 = queryRun2.get(tableNum(InventTrans));

            lineAmt += inventTrans1.lineAmount();

        }

        ubmGrowthNetSales.NetSales1 =  lineAmt;

 

         // End of - Lineamt value calculation

 

        // LineAmt1 value calculation

        transQuery2 = new Query();

        qbdsTrans1 = transQuery2.addDataSource(tableNum(inventTrans));

        qbdsTrans1.addRange(fieldNum(Inventtrans,ItemId)).value(inventTable.ItemId);

        qbdsTrans1.addRange(fieldNum(inventTrans,DatePhysical)).value(queryRange(_fromDate2,_toDate2));

 

 

        qbdsinventDim2 = qbdsTrans1.addDataSource(tableNum(InventDim));

        qbdsinventDim2.joinMode(JoinMode::ExistsJoin);

        qbdsinventDim2.addLink(fieldNum(InventTrans,InventDimId),fieldNum(InventDim,inventDimId));

        qbdsinventDim2.addRange(fieldNum(InventDim,inventLocationId)).value(inventLocationId);

        qbdsinventDim2.addRange(fieldNum(InventDim,inventSiteId)).value(inventSite);

 

 

        queryRun3 = new QueryRun(transQuery2);

        while(queryRun3.next())

        {

            inventTrans2 = queryRun3.get(tableNum(InventTrans));

            lineAmt1 += inventTrans2.lineAmount();

        }

        ubmGrowthNetSales.NetSales2 =  lineAmt1;

 

        // End of - Lineamt1 value calculation

 

        if (lineAmt)

        {

            ubmGrowthNetSales.GrowthPercentage = ((lineAmt1 - lineAmt) / lineAmt) * 100;

 

        }

        lineAmt  =0;

        lineAmt1 =0;

        ubmGrowthNetSales.insert();

        ubmGrowthNetSales.clear();

 

    }

}

 

 

5.   Create required design format .

6.   Add to AOT

7 .  Build and deploy the report