How to add multiple report design under Print management in AX 2012

Below are the steps to be followed :

Step1. Create new Design for report under visual studio
Step2. Add code to method:
\Data Dictionary\Tables\PrintMgmtReportFormat\Methods\populate
Add code  before TTSCOMMIT:
addOther(PrintMgmtDocumentType::PurchaseOrderInvoice, ssrsReportStr(VendInvoiceDocument, MyReport), ssrsReportStr(VendInvoiceDocument, MyReport), #NoCountryRegionId);
Step 3. Choose you new format under:
AP -> setup -> form setup -> Print management -> Vendor invoice -> report format to VendInvoiceDocument.MyReport
Step 4.
New Report design can be executed from use Print management from Inquiry journal forms or during posting by selecting Print management destination.
Step 5. (Optional)
In case: the report still keep original design, add this code to class VendInvoiceDocumentController in method outputReport or Main:
//original menu item or your new menu item
if(args.menuItemName() == menuitemOutputStr(MyReportMenuItem))
{
formLetterReport.getCurrentPrintSetting().parmReportFormatName(ssrsReportStr( VendInvoiceDocument,MyReport));
}
Advertisements

Update Dimensions for the Item,Customer and Vendor

Some times you may get a strange requirement from client to update few dimensions on Default Dimensions :

Below code snippet is used to update the required dimensions without disturbing the other dimensions


//Bellow example will remove the BU,Dept and CC without distrubing the other dimensions
static void ItemDimUpdate(Args _args)
{
    InventTable inventTable,inventTableselect;
    container dimValue;
    DimensionDefault DimensionDefault,DimensionDefault1;
    str BussinessUnit,CostCenter,Department,ItemGroup,Project;
    int i = 0;

    DimensionAttributeValueSetStorage       dimStorage;
    CustTable                               custTable;
    Counter                                 c;
    

    ttsBegin;
    while select * from inventTableselect
    {
         i = 0;
         BussinessUnit = "";
         CostCenter = "";
         Department = "";
         ItemGroup = "";
         Project = "";
         dim = new Struct();
         dimValue = connull();

        //Getting values from the Old Dimension
        dimStorage = DimensionAttributeValueSetStorage::find(inventTableselect.DefaultDimension);
        for (c=1 ; c<= dimStorage.elements() ; c++)
        {
            switch (DimensionAttribute::find(dimStorage.getAttributeByIndex(c)).Name)
            {
                case 'BussinessUnit':
                    BussinessUnit = dimStorage.getDisplayValueByIndex(c);
                break;
                case 'CostCenter':
                    CostCenter = dimStorage.getDisplayValueByIndex(c);
                break;
                case 'Department':
                    Department = dimStorage.getDisplayValueByIndex(c);
                break;
                case 'ItemGroup':
                    ItemGroup = dimStorage.getDisplayValueByIndex(c);
                break;
                case 'Project':
                    Project = dimStorage.getDisplayValueByIndex(c);
                break;
            }
        }

       //Creating the New Dimension
       //Just keep the required dimensions and Comment the other dimensions

       // if(BussinessUnit)
       //     dim.add('BusinessUnit', BussinessUnit);
       // if(CostCenter)
       //     dim.add('Costcenter', CostCenter);
       // if(Department)
       //     dim.add('Department', Department);
        if(ItemGroup)
            dim.add('ItemGroup', ItemGroup);
        if(Project)
            dim.add('Project', Project);

        dimValue += dim.fields();

      //  if(BussinessUnit)
      //  {
      //      i++;
      //      dimValue += dim.fieldName(i);
      //      dimValue += dim.valueIndex(i);
      //  }
      //  if(CostCenter)
      //  {
      //      i++;
      //      dimValue += dim.fieldName(i);
      //      dimValue += dim.valueIndex(i);
      //  }
      //  if(Department)
      //  {
      //      i++;
      //      dimValue += dim.fieldName(i);
      //      dimValue += dim.valueIndex(i);
      //  }
        if(ItemGroup)
        {
            i++;
            dimValue += dim.fieldName(i);
            dimValue += dim.valueIndex(i);
        }
        if(Project)
        {
            i++;
            dimValue += dim.fieldName(i);
            dimValue += dim.valueIndex(i);
        }

        //Below code will get the new dimension
        DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(dimValue);

        inventTable = inventTable::find(inventTableselect.ItemId,true);
        inventTable.DefaultDimension = DimensionDefault;

        inventTable.update();
    }
    ttsCommit;

    info("Updation completed");
}

 

Below Code snippet is used to update the Item,Customer and Vendor :


static void ItemDimUpdate(Args _args)
{
    InventTable inventTable;
    Struct dim = new Struct();
    container dimValue;
    DimensionDefault DimensionDefault;
    ;  

    dim.add('BusinessUnit', '002');
    dim.add('CostCenter', '007');
    dim.add('Department', '022');
    dim.add('ItemGroup', 'Audio Test');
    dim.add('Project', '000002');  

    dimValue += dim.fields();
    dimValue += dim.fieldName(1);
    dimValue += dim.valueIndex(1);
    dimValue += dim.fieldName(2);
    dimValue += dim.valueIndex(2);
    dimValue += dim.fieldName(3);
    dimValue += dim.valueIndex(3);
    dimValue += dim.fieldName(4);
    dimValue += dim.valueIndex(4);
    dimValue += dim.fieldName(5);
    dimValue += dim.valueIndex(5);  

    ttsBegin;  

    DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(dimValue);
    inventTable = inventTable::find("A0001",true);
    inventTable.DefaultDimension = DimensionDefault;
    inventTable.update();  

    ttsCommit;  

}

Steps to export SSRS data into multiple Excel Sheets

1. Add Parent Group to the existing group
To add a parent group, select the “Details” row under Row Groups and then right click and select Add Group and then click on Parent Group. You can refer to the below image.

Vendreport1.png

 

Once you click on Parent Group it will open a new window, there you have to provide a Group By field. In our case I want to divide data based on VendAcount, so I have to choose VendAcount data field and then click OK. You can refer to the below image.

VendAccount2.png

As you can see from the below image a new dummy row group has been created. SSRS has automatically added a new column in the Tablix for this newly created row group.

VendAccount2

2. Add Page Break

You need to create a page break between each instance of VendAcount, so right click on the recently created row group and click on Group Properties. You can refer to the below image.

VendAccount2.png

Once you click on Group Properties, a new Group Properties window will open. Click on the Page Breaks tab and check “Between each instance of a group” check box and click OK. It will give a page break between each instance of VendAcount, so when you export the report into Excel each instance of VendAcount will be separated into different worksheets. You can refer to the below image.

VendAccount2

3. Delete Dummy RowGroup Column

We don’t need the first column from the Tablix, this column was created automatically when we created the dummy row group, so delete the first column.

To delete the first column, right click on the first column then click on Delete Columns. We have to keep the group, so choose the second radio button Delete columns only and then click OK. You can refer to the below image.

VendAccount2.png

Let’s preview and export the report into Excel. As you can see from the below image, data has been divided into four sheets because we have four different Product Categories. If you notice in Excel the sheet names are Sheet1, Sheet2, Sheet3 and Sheet4. This is because if neither an initial page name, nor page names related to page breaks are given then the worksheet tabs will have the default names Sheet1, Sheet2, and so forth, but we need meaningful sheet names.

VendAccount2.png

4. Name the worksheets according to group value

You can dynamically assign sheet names. To assign sheets name select the Details Row under Row Groups and go to the properties by pressing the F4 key, it will open a Properties window. Look for “Group” in the properties window and expand it so you can see “PageName”. Assign the data field which will be used for sheet names. In our example since we used Vend Account data field in the grouping, I will use the same data field VendAccount. Now this will assign the VendAccount as the worksheet name.

VendAccount2.png

5. Report Preview

We have made all necessary changes, let’s preview the report. As you can see from the below image when we exported the report into Excel each Vend Account was exported into different sheets and this time each sheet name is assigned dynamically.

VendAccount2.png

 

 

Wanna add dimensions to your own form\Add dimensions to existing form

Hi Floks, Today I just want to give you a small code snippets to add Dimensions to your new form or adding them into Ax existing form:

Step 1:

Add a tab/group to your form, let us consider tab\group name is Dimensions

Step 2:

Give appropriate properties to you tab\group in the form Like: Auto Declaration, Data source, Columns, Hide If Empty

Step 3:

Add below lines of code into init() of your form after the super()

dimensionDefaultingController = DimensionDefaultingController::constructInTabWithValues(true,true, true,0,this, Dimensions, "@SYS138487");//Use it for Tab
dimensionDefaultingController = DimensionDefaultingController::constructInGroupWithValues(true,true, true,0,this, Dimensions, "@SYS138487");//Use it for Group
dimensionDefaultingController.parmAttributeValueSetDataSource(prodTable_ds, fieldStr(ProdTable, DefaultDimension));
dimensionDefaultingController.pageActivated();

 

Disable financial dimensions in AX 2012 through code

Disabling particular financial dimension based on record or on particular form quite tricky in AX 2012.

Here is an example to achieve disabling Business unit financial dimension on Customers form in AX 2012.

Process to disable Business Unit financial dimension on Customers form.
1. Create a new parm method in ‘DimensionDefaultingControllerBase’ class as shown below


void parmEditableDimensionAttributeSetId(RefRecId _editableDimensionAttributeSetId  = editableDimensionAttributeSetId)
{
    editableDimensionAttributeSetId = _editableDimensionAttributeSetId;
}

2. Create a new method in ‘CustTable’ form as shown below


private void setDimensionAttributeSetStorage()
{
    DimensionAttribute              dimAttr;
    DimensionAttributeSetItem       dimAttrSetItem;
    DimensionEnumeration            dimensionSetId =  DimensionCache::getDimensionAttributeSetForLedger();
    DimensionAttributeSetStorage    dimensionAttributeSetStorage;
    ;

    dimensionAttributeSetStorage = new DimensionAttributeSetStorage();

    while select * from dimAttr
            order by Name
            where dimAttr.Type != DimensionAttributeType::MainAccount
        join RecId from dimAttrSetItem
            where dimAttrSetItem.DimensionAttribute == dimAttr.RecId &&
                dimAttrSetItem.DimensionAttributeSet == dimensionSetId
    {
        if (dimAttr.Name != 'BusinessUnit') // Except BusinessUnit rest should enable
            dimensionAttributeSetStorage.addItem(dimAttr.RecId, dimAttr.HashKey,NoYes::Yes);
    }

      dimensionDefaultingController.parmEditableDimensionAttributeSetId(dimensionAttributeSetStorage.save());
}

3. Add below highlighted code in ‘init’ method of ‘CustTable’ form as shown below
Calling newly created method before dimensionDefaultingController.pageActivated();


dimensionDefaultingController = DimensionDefaultingController::constructInTabWithValues(true, true, true, 0, this, tabFinancialDimensions, "@SYS138487");
    dimensionDefaultingController.parmAttributeValueSetDataSource(custTable_ds, fieldStr(CustTable, DefaultDimension));
    // Added by Shankar on 25/11/2013 DisableFinDimensions - Begin
    this.setDimensionAttributeSetStorage();
    // Added by Shankar on 25/11/2013 DisableFinDimensions - End
    dimensionDefaultingController.pageActivated();

4. Now open Customers form & check financial dimensions tab it shows as below.
Can see Business Unit financial dimension disabled, highlighted one.

download

Summarized values in AX form

Users sometimes want to see both individual transactions in a grid of a Dynamics AX form, and some summarized values, such as the total amount or the number of lines (often above or below the grid). Iterating through the whole datasource and getting values one by one isn’t efficient, especially if the query returns many rows. A much better solution is taking the query, modifying it to use an aggregation function (such as SUM() or COUNT()) and sending a single, efficient request to database.

My example assumes that I have a form showing customer invoice lines and I want to calculate the total amount of all lines fulfilling current filters (and show it in a separate control).


public void updateTotal()
{
    // Copy the query
    Query query = new Query(CustInvoiceTrans_ds.queryRun().query());

    QueryBuildDataSource qbds = query.dataSourceTable(tableNum(CustInvoiceTrans));
    QueryRun qr;
    CustInvoiceTrans summedTrans;

    // Sum LineAmountMst
    qbds.addSelectionField(fieldNum(CustInvoiceTrans, LineAmountMst), SelectionField::Sum);

    qr = new QueryRun(query);
    // Run the query
    qr.next();

    // Get the data
    summedTrans = qr.get(tableNum(CustInvoiceTrans));

    // Set the new sum to the control
    Total.realValue(summedTrans.LineAmountMST);
}

The first statement is extremely important, because it defines which query you want to use. I take CustInvoiceTrans_ds.queryRun().query(), because I want to respect filters defined by users. If it wasn’t the case, I would use CustInvoiceTrans_ds.query(). Both scenarios are valid; the choice depends on your functional requirements. It’s also worth noting that I modified a copy of the query. If I modified the query used by the datasource, I would actually get the summed result in my grid, which wouldn’t make sense. Then I just have to call the method every time when the datasource query executes. public void


executeQuery()
{
super();
element.updateTotal();
}
<pre>

Ref:http://dev.goshoom.net/en/2015/11/summarized-values-in-ax-form/

 

Summarization

Import Dimensions into AX 2012

Its quite common for developer to get the requirement for Importing Default Dimensions into AX 2012,

Below you an find the Dimensions import for Worker in Ax 2012:

Class_WorkerDimImport

Below is the code snippet for the dimensions import for Fixed Assets:

class Mzk_FixedAssetDimensionImport
{
}

Below method will get the dimension recId for the dimensions which we have passed:

private DimensionDefault getNewDefaultDimension(str businessUnit = &amp;quot;&amp;quot; ,
                                                str costcenter = &amp;quot;&amp;quot;,
                                                str department = &amp;quot;&amp;quot;)
{
    // Dimensions
    #define.businessUnit(&amp;quot;BusinessUnit&amp;quot;)
    #define.costcenter(&amp;quot;Costcenter&amp;quot;)
    #define.department(&amp;quot;Department&amp;quot;)

    Struct                                struct = new Struct();
    container                             ledgerDimension;
    DimensionDefault                      DimensionDefault;
    int i = 0;

    if(businessUnit)
            struct.add(#businessUnit,businessUnit);
    if(costcenter)
        struct.add(#costcenter,costcenter);
    if(department)
        struct.add(#department,department);

    ledgerDimension += struct.fields();

    if(businessUnit)
    {
        i++;
        ledgerDimension += struct.fieldName(i);
        ledgerDimension += struct.valueIndex(i);
    }
    if(costcenter)
    {
        i++;
        ledgerDimension += struct.fieldName(i);
        ledgerDimension += struct.valueIndex(i);
    }
    if(department)
    {
        i++;
        ledgerDimension += struct.fieldName(i);
        ledgerDimension += struct.valueIndex(i);
    }

    DimensionDefault = AxdDimensionUtil::getDimensionAttributeValueSetId(ledgerDimension);

    return DimensionDefault;

}

Below method isto import Dimensions from Excel sheet:

private boolean importFromExcel(Filename inputFile)
{
    SysExcelApplication         application;
    SysExcelWorkbooks           workbooks;
    SysExcelWorkbook            workbook;
    SysExcelWorksheets          worksheets;
    SysExcelWorksheet           worksheet;
    SysExcelCells               cells;
    AssetBookId                 assetBookId;
    AssetId                     assetId = &amp;quot;&amp;quot;;
    AssetBook                   assetBook;
    int row = 1;
    COMVariantType              type;
    str costcenter,department,businessunit;
    int i;

    try
    {
        application = SysExcelApplication::construct();
        workbooks = application.workbooks();

        try
        {
            workbooks.open(inputFile,0,true);
        }
        catch (Exception::Error)
        {
            throw Exception::Error;
        }

        workbook = workbooks.item(1);
        worksheets = workbook.worksheets();
        worksheet = worksheets.itemFromNum(1);
        cells = worksheet.cells();

        type = cells.item(row + 1, 1).value().variantType();

        while (type != COMVariantType::VT_EMPTY)
        {
            row++;
            assetId = &amp;quot;&amp;quot;;
            assetBookId = &amp;quot;&amp;quot;;
            assetId = cells.item(row,1).value().bStr();
            assetBookId = cells.item(row,2).value().bStr();
            businessunit= cells.item(row,3).value().bStr();
            costcenter = (cells.item(row,4).value().bStr());
            department = (cells.item(row,5).value().bstr());

            ttsBegin;
            select forUpdate assetBook
                        where assetBook.AssetId == assetId
                                          &amp;amp;&amp;amp; assetBook.BookId  == assetBookId;
            if(assetBook.RecId)
            {
                if(businessunit || costcenter || department)
                {
                    assetBook.DefaultDimension = this.getNewDefaultDimension(businessunit,costcenter,department);
                    assetBook.update();
                }
                else
                {
                    info(strFmt(&amp;quot;Please check the values for AssetId:%1,BookId:%2&amp;quot;,assetId,assetBookId));
                }
            }
            else
            {
                info(strFmt(&amp;quot;Please check the values for AssetId:%1,BookId:%2&amp;quot;,assetId,assetBookId));
            }
            ttsCommit;

            type = cells.item(row + 1, 1).value().variantType();
        }

        application.quit();

        return true;
    }
    catch
    {
        return false;
    }
}

Main method code starts executing from here

public static void main(Args _args)
{
    Dialog                  dialog;
    DialogField             dialogFileName;
    SysOperationProgress    simpleProgress;
    Filename                filename;
    Container               filterCriteria;
    FileIOPermission        permission;
    Mzk_FixedAssetDimensionImport         Dimimport = new Mzk_FixedAssetDimensionImport();
    #File
    #avifiles

    dialog = new Dialog('Select Excel File');
    dialogFileName = dialog.addField(&amp;quot;Filenameopen&amp;quot;, &amp;quot;File Name&amp;quot;);
    filterCriteria = ['*.xls'];
    filterCriteria = dialog.filenameLookupFilter(filterCriteria);
    dialog.run();

    if (dialog.run())
    {
        filename = dialogFileName.value();
        if(!filename)
            info('Filename must be filled');
        else
           Dimimport.importFromExcel(filename);
    }
    info('Done');

}