BILL of Material Report Dataset (Drill down to the lowest level)

Hi everyone,

I have a requirement to develop a custom Bill of material report in Dynamics AX 2012 that have the following capabilities.

Parameters: Item group(Done!).

Then the report should drill down and display bill of materials and sub level to the lowest level. Due to the level of complexity of the business logice required by such a report, I resorted to use a report data provider class to generate the data-set for the report.

Below find my code

public void start()

{

Query query = this.parmQuery();

//This method drills down the bom/WIP to the lowest level

void wipExplode( ItemId _wipItemId)

{

BOMTable wipBomTable;

BOMVersion wipBomVersion;

BOM wipBOM;

;

bomLevel =bomLevel +1;

//Declare query variables

while select firstOnly wipBomVersion where wipBomVersion.ItemId==_wipItemId

&& wipBomVersion.Active && wipBomVersion.Approved

//If there is a result type, then the wip has a bom.

// if (wipBomVersion.RecId)

{

while select wipBOM join wipBomVersion where wipBOM.BOMId==wipBomVersion.BOMId

{

if (wipBOM.ItemId like “W*” ){

wipExplode(wipBOM.ItemId);

}

else

{

this.popIntoBOMAnalysisByRMaterialTmp

(

parentBomID,parentItemId,

parentItemGroupId,parentInventDimId,

wipBOM.InventDimId,pmfBatchSize,

pmfFormulaUnit,bomLevel,

wipBOM.LineNum,wipBOM.ItemId,

wipBOM.BOMQty,wipBOM.BOMQtySerie,

wipBOM.UnitId,InventItemGroup::find(wipBomTable.ItemGroupId).Name

);

}

}

}

}

query.dataSourceNo(1).addRange(fieldnum(BOMTable, ItemGroupId)).value(queryRange(itemGroupId,itemGroupId));

query.dataSourceNo(1).addSortField(fieldnum(BOMTable, BOMId));

query.dataSourceNo(1).addSortField(fieldnum(BOMTable, RecId));

qr =new QueryRun(query);

while(qr.next())

{

bomTable=qr.get(tableNum(BOMTable));

bomVersion=qr.get(tableNum(BOMVersion));

bom=qr.get(tableNum(BOM));

inventDim=qr.get(tableNum(InventDim));

bomLevel =0;

parentBomID =bomVersion.BOMId;

parentItemGroupId =bomTable.ItemGroupId;

parentInventDimId =bomVersion.InventDimId;

parentItemId =bomVersion.ItemId;

pmfBatchSize =bomVersion.PmfBatchSize;

pmfFormulaUnit =bomVersion.pmfFormulaUnit();

if(bom.ItemId like “W*”){

//bomLevel++;

wipExplode(bom.ItemId);

}

else{

this.popIntoBOMAnalysisByRMaterialTmp

(

//Starts by populating the BOM header details

bomVersion.BOMId,bomVersion.ItemId,

bomTable.ItemGroupId,bomVersion.InventDimId,

bom.InventDimId,bomVersion.PmfBatchSize,

bomVersion.pmfFormulaUnit(),bomLevel,

bom.LineNum,bom.ItemId,

bom.BOMQty,bom.BOMQtySerie,

bom.UnitId,InventItemGroup::find(bomTable.ItemGroupId).Name

);

}

}

}

Logically to me, the code is logical but when I run the report, the AOS stops. The resultset also looks not realistic. the bill of material level is just incrementing and not maintain its respective level. The data also looks too much redundant.

Can anyone out there assist me point out what I’m doing wrong?

I appreciate alot.

Regards,

Isaac.