How to released items from excel through X++

//Excel Format:
//Item Number                Item Name                Search Name

static void ReleasedProductsCompanyWise_JOB(Args _args)
{
    SysExcelApplication application;
    SysExcelWorkbooks workbooks;
    SysExcelWorkbook workbook;
    SysExcelWorksheets worksheets;
    SysExcelWorksheet worksheet;
    SysExcelCells cells;
    COMVariantType type;
    FilenameOpen                    filename;
    str             itemnumber;
    int row = 1; // if the excel has the header
 
    EcoResProduct ecoResProduct;
    InventTable inventTable;
    InventTableModule inventTableModule;
    NumberSequenceTable numberSequenceTable;
    ItemId itemId;
    InventItemSetupSupplyType inventItemSetupSupplyType;

    EcoResStorageDimensionGroupProduct ecoResStorageDimensionGroupProduct;
    EcoResTrackingDimensionGroupProduct ecoResTrackingDimensionGroupProduct;
    EcoResStorageDimensionGroupItem ecoResStorageDimensionGroupItem;
    EcoResTrackingDimensionGroupItem ecoResTrackingDimensionGroupItem;
    ;
    application = SysExcelApplication::construct();
    workbooks = application.workbooks();
    filename  = "****.xlsx; // complete file path (You will find it in property window of excel)
 
    try
    {
        workbooks.open(filename);
    }
    catch (Exception::Error)
    {
        throw error("File not found");
    }
    workbook = workbooks.item(1);
    worksheets = workbook.worksheets();
    worksheet = worksheets.itemFromNum(1);
    cells = worksheet.cells();

    //Iterate through cells and get the values
    do
    {
        //Incrementing the row line to next Row
        row++;
 
        itemnumber = cells.item(row,1).value().bStr();  
 
     
        select firstOnly ecoResProduct where EcoResProduct.DisplayProductNumber ==  cells.item(row,1).value().bStr();  //itemnumber //Audio system
        if(!inventTable::exist(itemnumber))
        {    
             
        //while select * from ecoResProduct
        changecompany ('DAT')
        {
        ttsBegin;
        inventTable = null;
        inventTableModule = null;
        inventItemSetupSupplyType = null;
        ecoResStorageDimensionGroupProduct = null;
        ecoResTrackingDimensionGroupProduct = null;
        ecoResStorageDimensionGroupItem = null;
        ecoResTrackingDimensionGroupItem = null;

        numberSequenceTable = InventParameters::numRefItemId().numberSequenceTable();
        //get item id from:
        //1. Product number if number seq for item ID is not set up or manual or return blank value
        if (!numberSequenceTable.RecId || numberSequenceTable.Manual)
        {
        itemId = ecoResProduct.productNumber();
        }
        else //number sequence auto, get a number
        {
        itemId = NumberSeq::newGetNumFromId(numberSequenceTable.RecId).num();
        }

        inventTable.initValue();
        inventTable.initFromEcoResProduct(ecoResProduct);
        inventTable.ItemId = ItemId;
        inventTable.NameAlias = ecoResProduct.SearchName;
        inventTable.insert(true);

        // Create inventTableModules

        inventTableModule.initValue();
        inventTableModule.ItemId = inventTable.ItemId;
        inventTableModule.ModuleType = ModuleInventPurchSales::Invent;
        inventTableModule.insert();

        inventTableModule.initValue();
        inventTableModule.ItemId = inventTable.ItemId;
        inventTableModule.ModuleType = ModuleInventPurchSales::Purch;
        inventTableModule.insert();

        inventTableModule.initValue();
        inventTableModule.ItemId = inventTable.ItemId;
        inventTableModule.ModuleType = ModuleInventPurchSales::Sales;
        inventTableModule.insert();

        //Create inventItemLocation
        InventItemLocation::createDefault(inventTable.ItemId);

        // Creates a new item default order type for the product that is released.

        inventItemSetupSupplyType.initValue();
        inventItemSetupSupplyType.ItemId = inventTable.ItemId;
        inventItemSetupSupplyType.ItemDataAreaId = inventTable.DataAreaId;
        inventItemSetupSupplyType.insert();

        //create relationship tables to dimension groups.

        ecoResStorageDimensionGroupProduct = EcoResStorageDimensionGroupProduct::findByProduct(ecoResProduct.RecId);
        ecoResTrackingDimensionGroupProduct = EcoResTrackingDimensionGroupProduct::findByProduct(ecoResProduct.RecId);

        if (ecoResStorageDimensionGroupProduct.RecId)
        { // mandatory storage dimension group for the product
        ecoResStorageDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;
        ecoResStorageDimensionGroupItem.ItemId = inventTable.ItemId;
        ecoResStorageDimensionGroupItem.StorageDimensionGroup = ecoResStorageDimensionGroupProduct.StorageDimensionGroup;
        ecoResStorageDimensionGroupItem.insert();
        }

        if (ecoResTrackingDimensionGroupProduct.RecId)
        { // mandatory tracking dimension group for the product
        ecoResTrackingDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;
        ecoResTrackingDimensionGroupItem.ItemId = inventTable.ItemId;
        ecoResTrackingDimensionGroupItem.TrackingDimensionGroup = ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup;
        //ecoResTrackingDimensionGroupItem.insert();
        }
     
        ttsCommit;
        info(strfmt("Product successfully released to ‘DAT’ legal entity"));
            }
        }
        // Loads the next row into the variant type and validating that its is empty or not
        type = cells.item(row+1, 1).value().variantType();
        }

        while (type != COMVariantType::VT_EMPTY);
     
        // quits the application
        application.quit();
 

}

How to release item into any specific legal entity in ax 2012 r3

static void ReleaseProducts_detailed(Args _args)
{
EcoResProduct ecoResProduct;
InventTable inventTable;
InventTableModule inventTableModule;
NumberSequenceTable numberSequenceTable;
ItemId itemId;
InventItemSetupSupplyType inventItemSetupSupplyType;

EcoResStorageDimensionGroupProduct ecoResStorageDimensionGroupProduct;
EcoResTrackingDimensionGroupProduct ecoResTrackingDimensionGroupProduct;
EcoResStorageDimensionGroupItem ecoResStorageDimensionGroupItem;
EcoResTrackingDimensionGroupItem ecoResTrackingDimensionGroupItem;
;

//select firstOnly ecoResProduct where EcoResProduct.DisplayProductNumber == "Test Product"; //Uncomment if you want to release only specific item to specific company
while select * from ecoResProduct
changecompany ('DAT')
{
ttsBegin;
inventTable = null;
inventTableModule = null;
inventItemSetupSupplyType = null;
ecoResStorageDimensionGroupProduct = null;
ecoResTrackingDimensionGroupProduct = null;
ecoResStorageDimensionGroupItem = null;
ecoResTrackingDimensionGroupItem = null;

numberSequenceTable = InventParameters::numRefItemId().numberSequenceTable();
//get item id from:
//1. Product number if number seq for item ID is not set up or manual or return blank value
if (!numberSequenceTable.RecId || numberSequenceTable.Manual)
{
itemId = ecoResProduct.productNumber();
}
else //number sequence auto, get a number
{
itemId = NumberSeq::newGetNumFromId(numberSequenceTable.RecId).num();
}

inventTable.initValue();
inventTable.initFromEcoResProduct(ecoResProduct);
inventTable.ItemId = ItemId;
inventTable.NameAlias = ecoResProduct.SearchName;
inventTable.insert(true);

// Create inventTableModules

inventTableModule.initValue();
inventTableModule.ItemId = inventTable.ItemId;
inventTableModule.ModuleType = ModuleInventPurchSales::Invent;
inventTableModule.insert();

inventTableModule.initValue();
inventTableModule.ItemId = inventTable.ItemId;
inventTableModule.ModuleType = ModuleInventPurchSales::Purch;
inventTableModule.insert();

inventTableModule.initValue();
inventTableModule.ItemId = inventTable.ItemId;
inventTableModule.ModuleType = ModuleInventPurchSales::Sales;
inventTableModule.insert();

//Create inventItemLocation
InventItemLocation::createDefault(inventTable.ItemId);

// Creates a new item default order type for the product that is released.

inventItemSetupSupplyType.initValue();
inventItemSetupSupplyType.ItemId = inventTable.ItemId;
inventItemSetupSupplyType.ItemDataAreaId = inventTable.DataAreaId;
inventItemSetupSupplyType.insert();

//create relationship tables to dimension groups.

ecoResStorageDimensionGroupProduct = EcoResStorageDimensionGroupProduct::findByProduct(ecoResProduct.RecId);
ecoResTrackingDimensionGroupProduct = EcoResTrackingDimensionGroupProduct::findByProduct(ecoResProduct.RecId);

if (ecoResStorageDimensionGroupProduct.RecId)
{ // mandatory storage dimension group for the product
ecoResStorageDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;
ecoResStorageDimensionGroupItem.ItemId = inventTable.ItemId;
ecoResStorageDimensionGroupItem.StorageDimensionGroup = ecoResStorageDimensionGroupProduct.StorageDimensionGroup;
ecoResStorageDimensionGroupItem.insert();
}

if (ecoResTrackingDimensionGroupProduct.RecId)
{ // mandatory tracking dimension group for the product
ecoResTrackingDimensionGroupItem.ItemDataAreaId = inventTable.DataAreaId;
ecoResTrackingDimensionGroupItem.ItemId = inventTable.ItemId;
ecoResTrackingDimensionGroupItem.TrackingDimensionGroup = ecoResTrackingDimensionGroupProduct.TrackingDimensionGroup;
ecoResTrackingDimensionGroupItem.insert();
}
ttsCommit;
info(strfmt("Product successfully released to ‘DAT’ legal entity"));
}
}

Add page number in SSRS report in AX 2012 R3

For printing page number like: Page 1 of  2, you need to write expression in text box:

="Page "&Globals!PageNumber &" of "&Globals!TotalPages

Task done.

Add custom parameter in SSRS report through RDP class in AX 2012 R3

This is very simple step:
//*ABC is field name

1- Declare field in class declaration of Contract class.
Sample:
ABC                 abc;

2- Add method in Contract class.
Sample:
[DataMemberAttribute('ABC')]
public "ExtendedDataType name of the field" parmABC(ABC_abc = abc)
{
    abc= _abc;
    return abc;
}

3- Declare object in class declaration of DP class
Sample:
ABC            abc;


4- Pass reference for this parm method in DP class.
Sample:
before starting query(Select or while select statement), write below line of code-
abc= rdpContract.parmABC();
//In this line "rdpContract" is a variable of your contract class which you need to declare.

and then use this "abc" value in ur query (select or while select statement).

Task done.

Hiding parameter groups or Dynamics filters on SSRS report dialog at runtime

Recently I had a requirement to hide report parameters from report dialog based on the caller menu item. So here is how to do it. I will show you the example of production picking list report. Production picking list report in AX 2012 feature pack version has the following report parameters:

There are three parameter groups on the report dialog above which are as follows:

• Parameters
• If connected to purchase order
• View

And below is the classDeclaration of the contract class, as shown below:

The View group fields comes from InventDimViewContract class which is added as a parm method in ProdPickListContract class to show the options for all inventory dimensions.

Suppose, we want to hide all the parameter groups from report dialog and just want to display the dynamics filters on the report. You need to modify the UIBuilder class of your report. For production picking list report, the UI Builder class is ProdPickListUIBuilder. The build method needs to be modified and the code in Bold below can be used to hide the whole parameter group from SSRS report dialog:

public void build()

{

FormBuildGroupControl grp;

grp = this.dialog().curFormGroup();

grp.frameType();

grp.columns(2);

if (this.controller().parmArgs().menuitemName() == #yourMenuItemName)

     {

           grp.visible(false);

     }

super ();

}

Below is the output, after hiding the whole parameter group:

To hide the dynamic filters from SSRS report dialog based on caller menu item, you need to override the following method in your controller class and return false from this method based on your condition:

showQueryValues

Override this method in your controller class and write the following code:

public boolean showQueryValues(str parameterName)

{

If (this.parmArgs().menuItemName() == menuItemOutputStr(#YourMenuItemName)

{

return false;

}

else

{

return true;

}

}

You will see the following output when the report is run:

Only the Printer and Print destination options will be shown which is displayed by default on the reports.

Enjoy reporting.

How to pass the default value to parameter in SSRS report in AX 2012?

Issue: If you want to pass default value as parameter in SSRS report, then how we can get the solution?

Solution: 
modify the data contract by overriding the SysReportRunController.preRunModifyContract() method. Assign the default values to start date and end date parameters there.

Using Complex Expressions

Complex expressions can contain multiple built-in references, operators, and function calls, and appear on the design surface as <<Expr>>. To see or change the expression text, you must open the Expression dialog box or type directly in the Properties pane. The following table lists typical ways you can use a complex expression to display or organize data or change report appearance, including the property to set, the dialog box you typically use to set it, and the value for the property. You can type an expression directly into a dialog box, on the design surface, or in the Properties pane.

Functionality	Property, Context, and Dialog Box	Property Value
Calculate aggregate values for a dataset.	Value property for a placeholder inside of a text box. Use Placeholder Properties Dialog Box, General.	=First(Fields!Sales.Value,"DataSet1")
Concatenate text and expressions in the same text box.	Value for a placeholder inside of a text box that is placed in a page header or page footer. Use Placeholder Properties Dialog Box, General.	="This report began processing at " & Globals!ExecutionTime
Calculate an aggregate value for a dataset in a different scope.	Value for a placeholder inside of a text box that is placed in a tablix group. Use Placeholder Properties Dialog Box, General.	=Max(Fields!Total.Value,"DataSet2)
Format data in a text box depending on value.	Color for a placeholder inside of a text box in the details row for a tablix. Use Text Box Properties Dialog Box, Font.	=IIF(Fields!TotalDue.Value < 10000,"Red","Black")
Calculate a value once to refer to throughout the report.	Value for a report variable. Use Report Properties Dialog Box, Variables.	=Variables!MyCalculation.Value
Include specific values for more than one field from a dataset.	Filter equation for a group in a tablix. Use Tablix Properties Dialog Box, Filters.	For data type, select Boolean. =IIF(InStr(Fields!Subcat.Value,"Shorts")=0 AND (Fields!Size.Value="M" OR Fields!Size.Value="S"),TRUE, FALSE) = TRUE
Hide a text box on the design surface, that can be toggled by the user using a Boolean parameter named Show.	Hiddenproperty on a text box. Use Text Box Properties Dialog Box, Visibility.	=Not Parameters! Show<boolean parameter> .Value
Specify dynamic page header or footer content.	Value for a placeholder inside of a text box that is placed in the page header or footer.	="Page " & Globals!PageNumber & " of " & Globals!TotalPages
Specify a data source dynamically by using a parameter.	Connection string on the Data source. Use Data Source Properties Dialog Box, General.	="Data Source=" & Parameters!ServerName.Value & ";initial catalog=AdventureWorks2012"
Identify all the values for a multivalue parameter chosen by the user.	Value for a placeholder inside of a text box. Use Tablix Properties Dialog Box, Filters.	=Join(Parameters!MyMultivalueParameter.Value,", ")
Specify page breaks for every 20 rows in a tablix with no other groups.	Group expression for a group in a tablix. Use Group Properties Dialog Box, Page Breaks. Select the option Between each instance of a group.	=Ceiling(RowNumber(Nothing)/20)
Specify conditional visibility based on a parameter.	Hidden property for a tablix. Use Tablix Properties Dialog Box, Visibility.	=Not Parameters!< boolean parameter >.Value
Specify a date formatted for a specific culture.	Value for a placeholder inside of a text box in a data region. Use Textbox Properties Dialog Box, General.	=Fields!OrderDate.Value.ToString(System.Globalization.CultureInfo.CreateSpecificCulture("de-DE"))
Concatenate a string and a number formatted as a percentage to two decimal places.	Value for a placeholder inside of a text box in a data region. Use Textbox Properties Dialog Box, General.	="Growth Percent: " & Format(Fields!Growth.Value,"p2")