Report

Print Query-Based SSRS via X++ example

Sti F. Cavendish AX 2012, Report, x++ , ,

code example : 

public void PrintPenarikanBarang(TECTradeInId tradeInCode)
{
    SrsReportRunController                  reportRunController;
    Map                                     queryContracts;
    MapEnumerator                           mapEnum;
    Query                                   query;
    QueryBuildRange                         range;

    SrsReportDataContract                   contract;
    TECTradeInPenarikanBarangReportContract     rdpContract;

    if(TECTradeInTable.TECTradeInCode)
    {
        reportRunController = new SrsReportRunController();
        reportRunController.parmReportName(ssrsReportStr(TECPenarikanBarangTradeIn ,Report));
        reportRunController.parmLoadFromSysLastValue(false);

        // Set printer settings (print to file, format, filename, etc).
        contract    = reportRunController.parmReportContract();

        rdpContract = contract.parmRdpContract() as TECTradeInPenarikanBarangReportContract;

        queryContracts = contract.parmQueryContracts();
        mapEnum = queryContracts.getEnumerator();
        while(mapEnum.moveNext())
        {
            query = mapEnum.currentValue();
            range = SysQuery::findOrCreateRange(query.dataSourceTable(tableNum(TECTradeInTable)),fieldNum(TECTradeInTable, TECTradeInCode));
            range.value(queryValue(tradeInCode));
        }

        reportRunController.runReport();

    }

}
Advertisements

Modify Query on Report DP Class example

Sti F. Cavendish AX 2012, Report, x++ , , 
[SysEntryPointAttribute]
public void processReport()
{
    //query class
    QueryRun            qr;
    Query               q;
    QueryBuildDataSource qbds;
    //table object
    InventSum           inventSum;
    WMSLocation         wmsLocation;
    RetailStoreTable    retailStoreTable;
    TIDLocType          tidLocType;
    //
    str                 headerName;

    contract = this.parmDataContract() as TIDStoreDisplayReportContract;
    parmShowQty = contract.parmShowQty();
    parmFilterItem = contract.parmStoreDisplayFilterItem();

    q = this.parmQuery();

    q.dataSourceTable(tableNum(WMSLocation)).addRange(fieldNum(WMSLocation,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
    switch(parmFilterItem)
    {
        case TIDStoreDisplayFilterItem::OnlyDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::Yes));
            headerName = "Only Display Item";
            break;
        case TIDStoreDisplayFilterItem::NoDisplay :
            qbds = q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::NoExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "No Display Item";
            break;
        case TIDStoreDisplayFilterItem::NotInDisplay :
            q.dataSourceTable(tableNum(TIDLocType)).addRange(fieldNum(TIDLocType,TIDIsDisplay)).value(SysQuery::value(NoYes::No));
            headerName = "Not in Display Item";
            break;
        case TIDStoreDisplayFilterItem::HaveDisplay :
            qbds =  q.dataSourceTable(tableNum(InventSum)).addDataSource(tableNum(TIDStoreHaveDisplayItem));
            qbds.addRange(fieldNum(TIDStoreHaveDisplayItem,InventLocationId)).value(TECComputerNameTable::find(xGlobal::computerName()).InventLocationId);
            qbds.joinMode(JoinMode::ExistsJoin);
            qbds.relations(false);
            qbds.addLink(fieldNum(InventSum,ItemId),fieldNum(TIDStoreHaveDisplayItem,ItemId));
            headerName = "Have Display Item";
            break;
        default :
            headerName = "Store Display Item";
            break;
    }

    // only display > 0
    q.dataSourceTable(tableNum(InventSum)).addRange(fieldNum(InventSum,AvailPhysical)).value(queryValue(">0"));

    qr = new QueryRun(q);

    while(qr.next())
    {
        inventSum   = qr.get(tableNum(inventSum));
        wmsLocation = qr.get(tableNum(wmsLocation));
        tidLocType  = qr.get(tableNum(TIDLocType));

        tempTable.clear();
        tempTable.headerName        = headerName;
        tempTable.ItemId            = inventSum.ItemId;
        tempTable.EcoResDescription = TIDItemDisplayCustom::getItemDescription(inventSum.ItemId);
        tempTable.EcoResProductName = TIDItemDisplayCustom::getItemName(inventSum.ItemId);
        tempTable.NameAlias         = TIDItemDisplayCustom::getItemModel(inventSum.ItemId);
        tempTable.AvailPhysical     = inventSum.AvailPhysical;
        select firstOnly retailStoreTable where retailStoreTable.inventLocation == wmsLocation.inventLocationId;
        tempTable.Store             = retailStoreTable.StoreNumber;
        tempTable.WMSLocationId     = wmsLocation.wMSLocationId;
        tempTable.IsDisplay         = tidLocType.TIDIsDisplay;
        tempTable.insert();

    }

}

Fixed Asset Projection Depreciation Expense example code

Sti F. Cavendish AX 2012, Report, x++ , , 
private void runWithAssetGroupId(NoYes parmendOfYearOnly,AssetGroupId assetGroupId)
{
    TmpDateSum      tmpDateSum;
    AssetTable      assetTable;
    AssetBook       assetBook;
    AssetBookTable  assetBookTable;
    //main account
    AssetLedgerAccounts  assetLedgerAccounts;
    MainAccountNum       mainAccountNum;


    while select assetTable
    join assetBook
    join assetBookTable
    where
        assetTable.AssetGroup                   == assetGroupId
    &&  assetTable.AssetId                      == assetBook.AssetId
    &&  assetBook.BookId                        == assetBookTable.BookId
    &&  assetBook.Depreciation                  == NoYes::Yes
    &&  assetBookTable.CurrentOperationsTax     == CurrentOperationsTax
    &&  assetBook.Status                        == AssetStatus::Open
    {

        lineTMP.clear();
        assetLedgerAccounts = AssetLedgerAccounts::find(assetBook.BookId,AssetParameters::find().PostingProfile,enum2int(AssetTransType::Depreciation),TableGroupAll::GroupId,assetTable.AssetGroup);
        mainAccountNum =  MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.OffsetLedgerDimension).MainAccount).MainAccountId;

        //Method calculation provided by AX
        tmpDateSum.setTmpData(assetBook.initDepreciationProfile());
        //set header line
        lineTMP.AssetGroupId = assetTable.AssetGroup;
        lineTMP.AssetId      = assetTable.AssetId;
        lineTMP.MainAccount  = mainAccountNum;
        lineTMP.rGrossFa     = assetBook.AcquisitionPrice;

        //cut off date
        while select tmpDateSum
        where
            tmpDateSum.TmpNumerals01 != 0
        &&  tmpDateSum.TransDate <= endYearDate
        {
            lineTMP.Period       = tmpDateSum.TransDate;
            lineTMP.rAccDepr     = tmpDateSum.TmpNumerals01;
            lineTMP.rDeprExp     = tmpDateSum.Total;
            lineTMP.rNetBookvalue = tmpDateSum.TmpNumerals02;
            lineTMP.insert();
        }

    }


}

print report via x++ AX 2012

Sti F. Cavendish AX 2012, Report, x++ , , 
SrsReportRunController          controller = new SrsReportRunController();
SysUserLicenseCountRDPContract  rdpContract = new SysUserLicenseCountRDPContract();
SRSPrintDestinationSettings     settings;
 
// Define report and report design to use
controller.parmReportName(ssrsReportStr(SysUserLicenseCountReport, Report));
// Use execution mode appropriate to your situation
controller.parmExecutionMode(SysOperationExecutionMode::ScheduledBatch);
// Suppress report dialog
controller.parmShowDialog(false);
 
// Explicitly provide all required parameters
rdpContract.parmReportStateDate(systemDateGet());
controller.parmReportContract().parmRdpContract(rdpContract);
 
// Change print settings as needed
settings = controller.parmReportContract().parmPrintSettings();
settings.printMediumType(SRSPrintMediumType::File);
settings.fileFormat(SRSReportFileFormat::Excel);
settings.fileName(@'\\share\UserLicenseCount.xlsx');
 
// Execute the report
controller.startOperation();

add mainAccount to LedgerJournalReport

Sti F. Cavendish AX 2012, Report, x++ , ,

add this code below to LedgerJournalDP\InsertLedgerJournalTmp

 if(_accountOffsetAccount ==  AccountOffsetaccount::Account)
    {
        switch(_ledgerJournalTrans.AccountType)
        {
            case LedgerJournalACType::Ledger :
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId;
                    break;
            case LedgerJournalACType::Bank :
                    select * from dimAttrValueCombo
                    join Name from bankAccountTable
                    where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue
                    && dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name);
                    break;
            case LedgerJournalACType::Vend :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    VendLedgerAccounts   = VendLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::Cust :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    CustLedgerAccounts = CustLedgerAccounts::find(_ledgerJournalTrans.PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::FixedAssets :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId;
                    assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,_ledgerJournalTrans.PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId;
                    break;
        }
    }
    else
    {
        switch(_ledgerJournalTrans.AccountType)
        {
            case LedgerJournalACType::Ledger :
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(_ledgerJournalTrans.LedgerDimension).MainAccount).MainAccountId;
                    break;
            case LedgerJournalACType::Bank :
                    select * from dimAttrValueCombo
                    join Name from bankAccountTable
                    where bankAccountTable.AccountID == dimAttrValueCombo.DisplayValue
                    && dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    ledgerJournalTmp.TIDMainAccountId = MainAccount::find(DimensionAttributeValueCombination::find(bankAccountTable.LedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue, bankAccountTable.Name);
                    break;
            case LedgerJournalACType::Vend :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    VendLedgerAccounts   = VendLedgerAccounts::find(VendParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(VendLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,VendTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::Cust :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    CustLedgerAccounts = CustLedgerAccounts::find(CustParameters::find().PostingProfile,TableGroupAll::GroupId,VendTable::find(dimAttrValueCombo.DisplayValue).VendGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(CustLedgerAccounts.SummaryLedgerDimension).MainAccount).MainAccountId;
                    ledgerJournalTmp.AccountName = strFmt("%1 : %2",dimAttrValueCombo.DisplayValue,CustTable::find(dimAttrValueCombo.DisplayValue).name());
                    break;
            case LedgerJournalACType::FixedAssets :
                    select firstOnly  dimAttrValueCombo  where dimAttrValueCombo.RecId == _ledgerJournalTrans.LedgerDimension;
                    selecT firstOnly ledgerJournalTrans_asset where ledgerJournalTrans_asset.RefRecId == _ledgerJournalTrans.RecId;
                    assetLedgerAccounts = AssetLedgerAccounts::find(ledgerJournalTrans_asset.BookId,AssetParameters::find().PostingProfile,enum2int(ledgerJournalTrans_asset.TransType),TableGroupAll::GroupId,AssetTable::find(dimAttrValueCombo.DisplayValue).AssetGroup);
                    ledgerJournalTmp.TIDMainAccountId =  MainAccount::find(DimensionAttributeValueCombination::find(assetLedgerAccounts.LedgerDimension).MainAccount).MainAccountId;
                    break;
        }
    }