Bill of Material Report(Drill down to the lowest level)

2012-11-08T20:01:00.003+03:00

A bill of material is a list of all components required to produce a finished product. Sometimes you can have an output of a bill of material as an input of another bill of material. Such line items are called WIP or Sub assembles.

In a production environment, One may need a listing of a bill of materials displaying only the raw materials and not sub assemblies. That means one should come up with a business logic to explode the various BOM to the lowest level displaying the raw materials.

In this Blog, I will outline the steps of creating such report.

Step one: Create a temp table to be used to store the data. Below find my temp table

Step two: Create a query on the AOT as shown below

Step three: Create the report data provide class that has the logic to drill down all levels of BOM and populate the temp table:our dataset in step one above. Below find the code.

/// <summary>

/// The <c>BOMAnalysisByRawMaterialDP</c> class is the report data provider class for the

/// <c>BOMAnalysisByRawMaterial</c> report.

/// </summary>PmfBomReportQryC

/// <remarks>

/// This is the report data provider class for the <c>BOMAnalysisByRawMaterialDP</c> SSRS report.

/// </remarks>

[

SRSReportQueryAttribute(querystr(PmfBomReportQry)),

SRSReportParameterAttribute(classstr(BOMAnalysisByRawMaterialContract))

]

public class BOMAnalysisByRawMaterialDP extends SRSReportDataProviderBase

{

ItemGroupId itemGroupId;

BOMExplodeTmp bomExplodeTmp;

BOMTable bomTable;

BOM bom;

BOMVersion bomVersion;

int bomLevel;

InventDim inventDim;

Qty quantity;

Query query;

QueryRun qr;

}

/// <summary>

/// Processes the report business logic.

/// </summary>

/// <remarks>

/// Provides the ability to write the report business logic. This method will be called by SSRS at

/// runtime. The method should compute data and populate the data tables that will be returned to SSRS.

/// </remarks>

[

SysEntryPointAttribute(false)

]

public void processReport()

{

BOMAnalysisByRawMaterialContract contract = this.parmDataContract() as BOMAnalysisByRawMaterialContract;

itemGroupId=contract.parmItemGroupId();

this.listBOM();

}

public void listBOM()

{

query=this.parmQuery();

//Excecution of the business logic to drill down to the lowest level of BOM starts here

query.dataSourceNo(1).addRange(fieldNum(BOMTable,ItemGroupId)).value(SysQuery::value(itemGroupId));

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

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

qr =new QueryRun(query);

while (qr.next()){

bomLevel=0;

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

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

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

if(bom.ItemId like "W*"){

this.wipExplode(bom.ItemId,bom.BOMQtySerie);

}

else{

this.insertBOMExplodeTmp(bomTable.BOMId,bomVersion.ItemId,bom.BOMQty,bom.ItemId,bom.ItemId,bomLevel,

InventDim::find(bomVersion.InventDimId).configId,

InventDim::find(bomVersion.InventDimId).InventSizeId,

InventDim::find(bomVersion.InventDimId).InventColorId,

InventTable::find(bomVersion.ItemId).itemName(bomVersion.inventDim()),

InventDim::find(bom.InventDimId).configId,

InventDim::find(bom.InventDimId).InventSizeId,

InventDim::find(bom.InventDimId).InventColorId,

InventTable::find(bom.ItemId).itemName(bom.inventDim())

);

}

//Method to explode or drill down the BOM to the next lower level if it is available

public void wipExplode(ItemId _wipItemId,Qty _qtySerie)

{

BOMVersion wipBomVersion;

BOM wipBOM;

bomLevel ++;

//check and select the active and approved wip BOM version

select firstOnly wipBomVersion where wipBomVersion.ItemId==_wipItemId && wipBomVersion.Approved && wipBomVersion.Active;

if (wipBomVersion.RecId)

{

//select the bom line for the select bom version above

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

if (wipBOM.ItemId like "W*") {

// bomLevel++;

this.wipExplode(wipBOM.ItemId,wipBOM.BOMQtySerie);

}

else{

quantity=wipBOM.BOMQty/wipBOM.BOMQtySerie * _qtySerie;

this.insertBOMExplodeTmp(bomVersion.BOMId,bomVersion.ItemId,quantity,

wipBOM.ItemId,wipBomVersion.ItemId,bomLevel,

InventDim::find(bomVersion.InventDimId).configId,

InventDim::find(bomVersion.InventDimId).InventSizeId,

InventDim::find(bomVersion.InventDimId).InventColorId,

InventTable::find(bomVersion.ItemId).itemName(bomVersion.inventDim()),

InventDim::find(wipBOM.InventDimId).configId,

InventDim::find(wipBOM.InventDimId).InventSizeId,

InventDim::find(wipBOM.InventDimId).InventColorId,

InventTable::find(wipBOM.ItemId).itemName(wipBOM.inventDim())

);

}

/// <summary>

/// T

/// Inserts records into the <c>BOMExplodeTmp</c> table.

/// </summary>

public void insertBOMExplodeTmp(

BOMId _bomId,

ItemId _pBomItemId,

BOMQty _bomQty,

ItemId _bomItemId,

BOMId _refItemId,

int _bomLevel,

EcoResItemConfigurationName _pConfigId,

EcoResItemColorName _pInventColorId,

EcoResItemSizeName _pInventSizeId,

ItemNameDisplay _pItemName,

EcoResItemConfigurationName _ConfigId,

EcoResItemColorName _InventColorId,

EcoResItemSizeName _InventSizeId,

ItemNameDisplay _ItemName)

{

ttsbegin;

bomExplodeTmp.BOMId =_bomId;

bomExplodeTmp.PBOMItemId =_pBomItemId;

bomExplodeTmp.BOMQty =_bomQty;

bomExplodeTmp.BOMItemId =_bomItemId;

bomExplodeTmp.RefItemId =_refItemId;

bomExplodeTmp.BomLevel =_bomLevel;

bomExplodeTmp.PconfigId =_pConfigId;

bomExplodeTmp.PInventSizeId =_pInventColorId;

bomExplodeTmp.PInventColorId =_pInventSizeId;

bomExplodeTmp.PItemName =_pItemName;

bomExplodeTmp.configId =_ConfigId;

bomExplodeTmp.InventSizeId =_InventColorId;

bomExplodeTmp.InventColorId =_InventSizeId;

bomExplodeTmp.ItemName =_ItemName;

bomExplodeTmp.insert();

ttsCommit;

}

/// <summary>

/// Gets records in the <c>BOMExplodeTmp</c> table.

/// </summary>

/// <returns>

/// The records in the <c>BOMExplodeTmp</c> table.

/// </returns>

[

SRSReportDataSetAttribute(tablestr(BOMExplodeTmp))

]

public BOMExplodeTmp getBOMExplodeTmp ()f

{

select bomExplodeTmp;

return bomExplodeTmp;

}

Wow! We now have the report dataset. The next step is to create a report listing report using Business Intelligence development studio.

Step Four: Create report. Open the .net development environment(Visual studio 2010) and create a project and select Microsoft Dynamics AX from the left pane and select ReportModel project from the list of projects, provide a project name and click OK.

When the project opens, right click the project node, click add and select report. This will create a new report. Right click on the report and select properties and give it your preferred report name.
Right click the Dataset node and select addDataset.
Go to data set properties, On the data source type node, Select Report Data Provider.
Next go to the query property and select the Data provider class that we created above. This will open the field selection window where you may select all the fields.
Now drag the data set to the design node, It will create an auto design.
Lastly specify the layout and style template for the auto design,
If you need to create a design with precise specification, you can use precision account.

Happy Daxing!!!!!

Save the SSRS report to PDF/HTML through code in dynamics ax 2012.

2012-10-04T09:22:00.001+03:00

SrsReportRun srsReportRun;

    srsReportRun = new SrsReportRun("InventTruckTransactionReport.PrecisionDesign1");

    srsReportRun.init();
    srsReportRun.reportCaption("InventTruckTransactionReport.PrecisionDesign1");
    srsReportRun.reportParameter("TruckTransDS_JournalId").value("000161_070");
    srsReportRun.showDialog(false);

    // Print to a file named ReportExample in HTML/PDF format.
    srsReportRun.printDestinationSettings().printMediumType(SRSPrintMediumType::File);
    srsReportRun.printDestinationSettings().fileFormat(SRSReportFileFormat::PDF);
    srsReportRun.printDestinationSettings().overwriteFile(true);
    srsReportRun.printDestinationSettings().fileName(@"C:\InventTruckTransactionReport.pdf");

    if( srsReportRun )
    {
        srsReportRun.executeReport();
    }

How to print the SSRS report in dynamics ax 2012 from code.

2012-10-04T09:20:00.003+03:00

    SrsReportRun srsReportRun;

    // initiate the report.
    srsReportRun = new SrsReportRun ("InventTruckTransactionReport.PrecisionDesign1");
    srsReportRun.init();
    srsReportRun.reportCaption("InventTruckTransactionReport.PrecisionDesign1");
    // set parameters name, value.
    srsReportRun.reportParameter("TruckTransDS_JournalId").value("000161_070");
    // suppress the dialog
    srsReportRun.showDialog(false);

    if( srsReportRun )
    {
        // run the report
        srsReportRun.executeReport();
    }

bits here and there ...: How to deploy all AX2012 report

2012-09-27T15:40:00.001+03:00

bits here and there ...: How to deploy all AX2012 report: There're 3 different ways to deploy Dynamics AX2012 reports: Through AOT AOT > SSRS Reports > Reports > right click on report > Deploy ...

AX2012 Methods on Queries and Forms Cannot Compile...

2012-09-21T11:20:00.001+03:00

Classes, tables, and enums are types in the X++ language. X++ types can be compiled to CIL. The queries and forms in the AOT are not X++ types, and they cannot be compiled to CIL. This means there are a few minor cases where calls from CIL to X++ p-code can cause an exception.

Suppose a method that is running as CIL calls a method on a query. The query method calls the ClassFactory system class of Microsoft Dynamics AX. This case causes an exception to be thrown. The problem is that the CIL session does not have access to all the system classes, yet the query method makes a call to a system class.

X++ Functions that are Not Supported in CIL

X++ language functions that run strings of dynamically written X++ code are not supported when compiled to CIL. The following X++ functions are not supported when compiled to CIL:

evalbuf
runbuf

Dynamics AX 2012 - Report labels does not show up in report

2012-09-21T10:37:00.001+03:00

Sometimes the report labels do not show in reports or the report shows label IDs like Labels!@SYS2123 instead of label values.

There are two reasons to this type of issues:

You have moved your AX service account from AX domain account from AX domain admin group

You have changed your SSRS service account password.

Solutions:

1. From the Start menu, point to All Programs, click the SQL Server folder, click theConfiguration Tools folder, and then click Reporting Services Configuration Manager.
2. In Reporting Services Configuration Manager click Connect and then click Service Account.
3. Set the password and then click Apply. The account and password should be the same as the Microsoft Dynamics AX proxy account.
4. System will ask you to backup your encrytion key, and then will some settings on the backhand.
5. Now Click the ServerName/MSSQLSERVER on the left top of the reporting server and then click Stop. Then click Start to restart the server. Always check with the SQL administrator and make sure no other users are connected to the server before you restart the server.

Now try opening your report again, your issue will be resolved.

Developing in Microsoft Dynamics AX 2012

Bill of Material Report(Drill down to the lowest level)

Save the SSRS report to PDF/HTML through code in dynamics ax 2012.

How to print the SSRS report in dynamics ax 2012 from code.

bits here and there ...: How to deploy all AX2012 report

AX2012 Methods on Queries and Forms Cannot Compile...

Dynamics AX 2012 - Report labels does not show up in report