query::insert_recordset x++

[SRSReportParameterAttribute(classstr(ALM_QtySaleAnalysisContract))]

class ALM_QtySaleAnalysisDp extends SRSReportDataProviderPreprocess

{

    ALM_QtySaleAnalysisContract         contract;

    ALM_QtySaleAnalysisTmp              qtySaleAnalysisTmp;

    ALM_QtySaleAnalysisStaging          qtySaleAnalysisStaging;

    TransDate                           fromDate, toDate;

    Str1260                             retailStoreIdMulti;

    Str1260                             itemGroupIdMulti;

    Str1260                             itemIdMulti;

    

    [SRSReportDataSetAttribute("ALM_QtySaleAnalysisTmp")]

    public ALM_QtySaleAnalysisTmp getALM_QtySaleAnalysisTmp()

    {

        qtySaleAnalysisTmp.setConnection(this.parmUserConnection());

        select qtySaleAnalysisTmp;

        return qtySaleAnalysisTmp;

    }

    public void processReport()

    {

        contract            =   this.parmDataContract();

        fromDate            =   contract.parmFromDate();

        toDate              =   contract.parmToDate();

        retailStoreIdMulti  =   contract.parmRetailStoreIdMulti();

        itemGroupIdMulti    =   contract.parmItemGroupIdMulti();

        itemIdMulti         =   contract.parmItemIdMulti();

        if(itemGroupIdMulti)

        {

            this.inserDataWithParm();

        }

        else

        {

            this.inserDataWithoutParm();

        }

    }

    public void insertIntoStaging()

    {

        RetailTransactionSalesTrans     retailTransactionSalesTrans;

        InventItemGroupItem             inventItemGroupItem;

        InventItemGroup                 inventItemGroup;

        InventTable                     inventTable;

        RetailStoreTable                retailStoreTable;

        RetailChannelTable              retailChannelTable;

        OMOperatingUnit                 omOperatingUnit;

        DirPartyTable                   dirPartyTable;

        ttsbegin;

        delete_from qtySaleAnalysisStaging;

        ttscommit;

        ttsBegin;

        insert_recordset qtySaleAnalysisStaging

        (

            ItemId,

            StoreCode,

            Qty,

            TransDate,

            CostAmount,

            NetAmount,

            SalesUnit,

            ItemGroupId,

            ItemGroupName,

            StoreName

        )

        select ItemId,Store,Qty,TransDate,CostAmount,NetAmount,Unit from retailTransactionSalesTrans

            where retailTransactionSalesTrans.TransDate >= fromdate && retailTransactionSalesTrans.TransDate <= toDate

            join inventTable where inventTable.ItemId == retailTransactionSalesTrans.itemId

            join ItemGroupId from inventItemGroupItem where inventItemGroupItem.ItemDataAreaId == inventTable.dataAreaId

            && inventItemGroupItem.ItemId == inventTable.ItemId

            join Name from inventItemGroup where inventItemGroup.DataAreaId == inventItemGroupItem.ItemGroupDataAreaId

            && inventItemGroup.ItemGroupId == inventItemGroupItem.ItemGroupId

            && retailTransactionSalesTrans.costAmount != 0

            join retailStoreTable where retailStoreTable.StoreNumber == retailTransactionSalesTrans.store

            join retailChannelTable where retailChannelTable.RecId == retailStoreTable.RecId

            join Name from omOperatingUnit where omOperatingUnit.RecId == retailChannelTable.OMOperatingUnitID;

        ttsCommit;

        while select forupdate qtySaleAnalysisStaging

        {

            ttsbegin;

            qtySaleAnalysisStaging.ConvertedQty = this.qtyToConvert(qtySaleAnalysisStaging.ItemId, qtySaleAnalysisStaging.Qty, qtySaleAnalysisStaging.SalesUnit);

            qtySaleAnalysisStaging.BaseUnit = this.baseUnit(qtySaleAnalysisStaging.ItemId);

            qtySaleAnalysisStaging.update();

            ttscommit;

        }

        /*ttsBegin;

        update_recordSet qtySaleAnalysisStaging

        setting StoreName = omOperatingUnit.Name

        join retailStoreTable where retailStoreTable.StoreNumber == qtySaleAnalysisStaging.StoreCode

        join retailChannelTable where retailChannelTable.RecId == retailStoreTable.RecId

        join omOperatingUnit where omOperatingUnit.RecId == retailChannelTable.OMOperatingUnitID;

        ttsCommit;*/

    }

    public void inserDataWithoutParm()

    {

        Query                           qr = new Query();

        QueryRun                        qRun;

        QueryBuildDataSource            qbds;

        Map                             fieldMapping;

        QueryBuildFieldList             fldList_RetailTransactionSalesTrans;

        RetailTransactionSalesTrans     retailTransactionSalesTrans;

        InventItemGroupItem             inventItemGroupItem;

        InventItemGroup                 inventItemGroup;

        InventTable                     inventTable;

        RetailStoreTable                retailStoreTable;

        RetailChannelTable              retailChannelTable;

        OMOperatingUnit                 omOperatingUnit;

        DirPartyTable                   dirPartyTable;

        ttsbegin;

        delete_from qtySaleAnalysisTmp;

        ttscommit;

        qbds = qr.addDataSource(tableNum(RetailTransactionSalesTrans));

        if(fromDate && toDate)

        {

            qbds.addRange(fieldNum(RetailTransactionSalesTrans,TransDate)).value(queryRange(fromDate, toDate));

        }

        if(retailStoreIdMulti)

        {

            qbds.addRange(fieldNum(RetailTransactionSalesTrans,Store)).value(retailStoreIdMulti);

        }

        if(itemIdMulti)

        {

            qbds.addRange(fieldNum(RetailTransactionSalesTrans,itemId)).value(itemIdMulti);

        }

        qbds.addRange(fieldNum(RetailTransactionSalesTrans,CostAmount)).value(SysQuery::valueNot(0));

        qbds.fields().clearFieldList();

        fldList_RetailTransactionSalesTrans = qbds.fields();

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, ItemId));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, Store));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, Qty));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, TransDate));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, CostAmount));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, NetAmount));

        fldList_RetailTransactionSalesTrans.addField(fieldNum(RetailTransactionSalesTrans, Unit));

        fldList_RetailTransactionSalesTrans.dynamic(QueryFieldListDynamic::No);

        fieldMapping = new Map(Types::AnyType, Types::Container);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ItemId), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, ItemId)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, StoreCode), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, Store)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, Qty), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, Qty)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, TransDate), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, TransDate)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, CostAmount), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, CostAmount)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, NetAmount), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, NetAmount)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, SalesUnit), [qbds.uniqueId(), fieldStr(RetailTransactionSalesTrans, Unit)]);

        query::insert_recordset(qtySaleAnalysisTmp, fieldMapping, qr);

        /*ttsBegin;

        update_recordSet qtySaleAnalysisTmp

            setting ConvertedQty = this.qtyToConvert(qtySaleAnalysisTmp.ItemId, qtySaleAnalysisTmp.Qty, qtySaleAnalysisTmp.SalesUnit)

            where qtySaleAnalysisTmp.ItemId;

        ttsCommit;*/

        while select forupdate qtySaleAnalysisTmp

        {

            ttsbegin;

            qtySaleAnalysisTmp.ConvertedQty = this.qtyToConvert(qtySaleAnalysisTmp.ItemId, qtySaleAnalysisTmp.Qty, qtySaleAnalysisTmp.SalesUnit);

            qtySaleAnalysisTmp.BaseUnit = this.baseUnit(qtySaleAnalysisTmp.ItemId);

            qtySaleAnalysisTmp.update();

            ttscommit;

        }

        ttsBegin;

        update_recordSet qtySaleAnalysisTmp

            setting StoreName = DirPartyTable.Name

            join retailStoreTable  where retailStoreTable.StoreNumber == qtySaleAnalysisTmp.StoreCode

            join retailChannelTable where retailChannelTable.RecId == retailStoreTable.RecId

            join dirPartyTable where retailChannelTable.OMOperatingUnitID == dirPartyTable.RecId;

        ttsCommit;

        ttsBegin;

        update_recordSet qtySaleAnalysisTmp

            setting ItemGroupId = inventItemGroupItem.ItemGroupId,

                    ItemGroupName = inventItemGroupItem.ItemGroupId+" - "+inventItemGroup.Name

            join inventTable where inventTable.ItemId == qtySaleAnalysisTmp.itemId

            join ItemGroupId from inventItemGroupItem where inventItemGroupItem.ItemDataAreaId == inventTable.dataAreaId

            && inventItemGroupItem.ItemId == inventTable.ItemId

            join Name from inventItemGroup where inventItemGroup.DataAreaId == inventItemGroupItem.ItemGroupDataAreaId

            && inventItemGroup.ItemGroupId == inventItemGroupItem.ItemGroupId;

        ttsCommit;

        select firstonly forupdate qtySaleAnalysisTmp;

        qtySaleAnalysisTmp.CompanyLogo = FormLetter::companyLogo();

        ttsBegin;

        qtySaleAnalysisTmp.update();

        ttsCommit;

    }

    public void inserDataWithParm()

    {

        Query                   qr = new Query();

        QueryRun                qRun;

        QueryBuildDataSource    qbds;

        Map                     fieldMapping;

        QueryBuildFieldList     fldList_ALM_QtySaleAnalysisStaging;

        InventItemGroupItem     inventItemGroupItem;

        InventItemGroup         inventItemGroup;

        InventTable             inventTable;

        RetailStoreTable        retailStoreTable;

        RetailChannelTable      retailChannelTable;

        OMOperatingUnit         omOperatingUnit;

        DirPartyTable           dirPartyTable;

    

        ttsbegin;

        delete_from qtySaleAnalysisTmp;

        ttscommit;

    

        this.insertIntoStaging();

        qbds = qr.addDataSource(tableNum(ALM_QtySaleAnalysisStaging));

        if(retailStoreIdMulti)

        {

            qbds.addRange(fieldNum(ALM_QtySaleAnalysisStaging,StoreCode)).value(retailStoreIdMulti);

        }

        if(itemIdMulti)

        {

            qbds.addRange(fieldNum(ALM_QtySaleAnalysisStaging,itemId)).value(itemIdMulti);

        }

        if(itemGroupIdMulti)

        {

            qbds.addRange(fieldNum(ALM_QtySaleAnalysisStaging,ItemGroupId)).value(itemGroupIdMulti);

        }

        qbds.fields().clearFieldList();

        fldList_ALM_QtySaleAnalysisStaging = qbds.fields();

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, ItemId));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, ItemGroupId));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, ItemGroupName));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, StoreCode));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, StoreName));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, Qty));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, TransDate));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, CostAmount));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, NetAmount));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, SalesUnit));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, ConvertedQty));

        fldList_ALM_QtySaleAnalysisStaging.addField(fieldNum(ALM_QtySaleAnalysisStaging, BaseUnit));

        fldList_ALM_QtySaleAnalysisStaging.dynamic(QueryFieldListDynamic::No);

    

        fieldMapping = new Map(Types::AnyType, Types::Container);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ItemId), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, ItemId)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ItemGroupId), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, ItemGroupId)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ItemGroupName), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, ItemGroupName)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, StoreCode), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, StoreCode)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, StoreName), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, StoreName)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, Qty), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, Qty)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, TransDate), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, TransDate)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, CostAmount), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, CostAmount)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, NetAmount), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, NetAmount)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, SalesUnit), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, SalesUnit)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, ConvertedQty), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, ConvertedQty)]);

        fieldMapping.insert(fieldStr(ALM_QtySaleAnalysisTmp, BaseUnit), [qbds.uniqueId(), fieldStr(ALM_QtySaleAnalysisStaging, BaseUnit)]);

    

        query::insert_recordset(qtySaleAnalysisTmp, fieldMapping, qr);

        ttsBegin;

        update_recordSet qtySaleAnalysisTmp

            setting ItemGroupName = inventItemGroupItem.ItemGroupId+" - "+inventItemGroup.Name

            join inventTable where inventTable.ItemId == qtySaleAnalysisTmp.itemId

            join ItemGroupId from inventItemGroupItem where inventItemGroupItem.ItemDataAreaId == inventTable.dataAreaId

            && inventItemGroupItem.ItemId == inventTable.ItemId

            join Name from inventItemGroup where inventItemGroup.DataAreaId == inventItemGroupItem.ItemGroupDataAreaId

            && inventItemGroup.ItemGroupId == inventItemGroupItem.ItemGroupId;

        ttsCommit;

    

        ttsBegin;

        select firstonly forupdate qtySaleAnalysisTmp;

        qtySaleAnalysisTmp.CompanyLogo = FormLetter::companyLogo();

        qtySaleAnalysisTmp.update();

        ttsCommit;

    }

    public Qty qtyToConvert(ItemId _itemId, Qty _qty, UnitIDBase _fromUnit)

    {

        Qty                 qty;

        InventTable         inventTable;

        InventTableModule   inventTableModule;

        select firstonly inventTableModule

        where inventTableModule.ModuleType == ModuleInventPurchSales::Invent

        && inventTableModule.ItemId == _itemId;

        if(inventTableModule.UnitId)

        {

            qty =   UnitOfMeasureConverter::convert(_qty,

                    UnitOfMeasure::unitOfMeasureIdBySymbol(_fromUnit),

                    UnitOfMeasure::unitOfMeasureIdBySymbol(inventTableModule.UnitId),

                    NoYes::No,

                    InventTable::itemProduct(_itemId),

                    NoYes::No);

        }

        else

        {

            qty = _qty;

        }

        return qty;

    }

    public UnitIDBase baseUnit(ItemId _itemId)

    {

        UnitIDBase          baseUnit;

        InventTable         inventTable;

        InventTableModule   inventTableModule;

        select firstonly inventTableModule

        where inventTableModule.ModuleType == ModuleInventPurchSales::Invent

        && inventTableModule.ItemId == _itemId;

                        

        return inventTableModule.UnitId;

    }

}

SSRS Serial numbers

 Serial number

=RowNumber(Nothing)

serial number inside group values

=RowNumber("yourGroupName")

serial number on group level

=RunningValue(CountDistinct("yourDSName"),Count,"yourDSName")

=CountDistinct("yourGroupingColumnName")