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;  

}

Advertisements

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

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 = "" ,
                                                str costcenter = "",
                                                str department = "")
{
    // Dimensions
    #define.businessUnit("BusinessUnit")
    #define.costcenter("Costcenter")
    #define.department("Department")

    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 = "";
    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 = "";
            assetBookId = "";
            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
                                          && assetBook.BookId  == assetBookId;
            if(assetBook.RecId)
            {
                if(businessunit || costcenter || department)
                {
                    assetBook.DefaultDimension = this.getNewDefaultDimension(businessunit,costcenter,department);
                    assetBook.update();
                }
                else
                {
                    info(strFmt("Please check the values for AssetId:%1,BookId:%2",assetId,assetBookId));
                }
            }
            else
            {
                info(strFmt("Please check the values for AssetId:%1,BookId:%2",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("Filenameopen", "File Name");
    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');

}

Get User Roles,Duties,Privileges & there Permissions

Below is the code snippet to get the user Roles,Duties,Privileges & there Permissions

 


//To get the information of user, roles duties, Privilege  and access level
//NOTE: PLEASE UPDATE FILE NAME AND USER FILTER IN QUERY
static void Mzk_ExportUsersecurities_User(Args _args)
{
    SysExcelApplication         xlsApplication;
    SysExcelWorkBooks           xlsWorkBookCollection;
    SysExcelWorkBook            xlsWorkBook;
    SysExcelWorkSheets          xlsWorkSheetCollection;
    SysExcelWorkSheet           xlsWorkSheet;
    SysExcelRange               xlsRange;
    CustTable                   custTable;
    int                         row = 1;
    str                         fileName;

    SecurityTask                securityTask, securityTaskDuty, securityTaskPriv;
    SecuritySubTask             securitySubTask;
    SecurityRoleTaskGrant       securityRoleTaskGrant;
    SecurityRole                securityRole, securityRoleDuty,securityRolePriv, securityRoleM;
    SecurityTaskEntryPoint      SecurityTaskEntryPoint;
    SecurableObject             SecurableObject;
    SecurityUserRole            securityUserRole;
    UserInfo                    userInfo;

    SecurityTaskEntryPoint  taskEntryPoint;
    SecurityRole            role;
    SecurityRoleTaskGrant   taskGrant;
    SecuritySubTask         subTask;
    SecurityTask            privilege;

    str privName;
    str dutyName;
    str entrName;
    str accessLevel;

    boolean i = true,j = true;
    ;
    fileName = "UserName.CSV";    //Rename file
    xlsApplication           = SysExcelApplication::construct();
    xlsWorkBookCollection    = xlsApplication.workbooks();
    xlsWorkBook              = xlsWorkBookCollection.add();
    xlsWorkSheetCollection   = xlsWorkBook.worksheets();
    xlsWorkSheet             = xlsWorkSheetCollection.itemFromNum(1);
    xlsWorkSheet.cells().item(row,1).value("User Id");
    xlsWorkSheet.cells().item(row,2).value("User Role");
    xlsWorkSheet.cells().item(row,3).value("Duty");
    xlsWorkSheet.cells().item(row,4).value("Privilege AOT");
    xlsWorkSheet.cells().item(row,5).value("Privilege Description");
    xlsWorkSheet.cells().item(row,6).value("Entry Point");
    xlsWorkSheet.cells().item(row,7).value("Permission");

    row++;

    while select * from securityRole
            join securityUserRole
            where securityUserRole.User like "USERNAME"     //Enter user information here
            && securityUserRole.SecurityRole == securityRole.RecId
        {
        while select * from securityRoleTaskGrant
        join * from securityTask
            where securityRoleTaskGrant.SecurityRole == securityRole.RecId
                && securityTask.RecId == securityRoleTaskGrant.SecurityTask

        {
            if(securityTask.Type == SecurityTaskType::Duty)
            {
               while select SecurityTaskEntryPoint
                            join SecurableObject where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
                            join subTask where subTask.SecuritySubTask == SecurityTaskEntryPoint.SecurityTask
                            join taskGrant where taskGrant.SecurityTask == subTask.SecurityTask
                            join role where role.RecId == taskGrant.SecurityRole
                                &&  role.AotName like securityRole.AotName

                {
                    select privilege
                        where privilege.RecId == taskGrant.SecurityTask
                        && SecurityTaskType::Duty == privilege.Type;
                            dutyName = privilege.AotName;

                    select privilege
                        where privilege.RecId == subTask.SecuritySubTask
                        && SecurityTaskType::Privilege == privilege.Type;
                        privName = privilege.AotName;
                    {
                       //Information to Excel
                        xlsWorkSheet.cells().item(row,1).value(securityUserRole.User);         //User id
                        xlsWorkSheet.cells().item(row,2).value(securityRole.Name);  //Role Name
                        xlsWorkSheet.cells().item(row,3).value(dutyName); //DutyName
                        xlsWorkSheet.cells().item(row,4).value(privName);  //Privilege AOT Name
                        xlsWorkSheet.cells().item(row,5).value(privilege.Name);  //Privilege Description
                        xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name);  //Entry Points
                        xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup));  //Access Permission level
                        row++;
                    }
                }
            }
            else             //Role if contain Privilegs directly
            {
                while select * from SecurityTaskEntryPoint
                            join SecurableObject
                            where SecurableObject.RecId == SecurityTaskEntryPoint.EntryPoint
                            && SecurityTaskEntryPoint.SecurityTask == securityTask.RecId
                {
                    xlsWorkSheet.cells().item(row,1).value(securityUserRole.User);         //User id
                    xlsWorkSheet.cells().item(row,2).value(securityRole.Name);  //Role Name
                    xlsWorkSheet.cells().item(row,3).value(""); //DutyName
                    xlsWorkSheet.cells().item(row,4).value(securityTask.AotName);  //Privilege AOT Name
                    xlsWorkSheet.cells().item(row,5).value(securityTask.Name);  //Privilege Description
                    xlsWorkSheet.cells().item(row,6).value(SecurableObject.Name);  //Entry Points
                    xlsWorkSheet.cells().item(row,7).value(enum2str(SecurityTaskEntryPoint.PermissionGroup));  //Access Permission level
                    row++;
                }
            }
        }
    }
    if(WinApi::fileExists(fileName))
        WinApi::deleteFile(fileName);
    xlsWorkbook.saveAs(fileName);
    xlsApplication.visible(true);
    info(strFmt("File has been exported"));
}

Creating multi-select lookup dialog for SSRS report parameter in MS Dynamics AX 2012

Hello All,
I just recently found a way to create a multi select lookup
dialog for SSRS report parameter using SysLookupMultiSelectGrid
class. I thought it might be helpfull for other developers
as well since I couldn’t find any other Blog or Post answering this issue.

I am just assuming that you know how to create a simple dialog
for reporting parameter. However, if you don’t know this link might be helpfull to you.

https://erpdax.wordpress.com/2014/07/11/10/

I will just write those methods which will create a multi select
dialog. So, here we go.
Step 1:
In your data contract class declaration method, define your
parameter with ‘List’. For example I want to create a multi select dialog for
customers in which I need Customer account to be selected when a user selects
any customer. SO, I will write

List accountNum;

In your DataMemberAttribue method type
the following code

[
DataMemberAttribute('AccuontNum'),
AifCollectionTypeAttribute('AccountNum', Types::String),
SysOperationLabelAttribute(literalstr("@SYS302"))
]
public List parmAccountNum(List _accountNum = accountNum)
{
  accountNum = _accountNum;
  return accountNum;
}

Your screen will look like this

1

Step 2:
Now that you have completed the contract class, let’s move on to
the UI Builder class. In your main lookup method write the following code.

public void lookup(FormStringControl _control)
{
  Query query = new Query(queryStr(CustTableSRS));

  container cnt;
  SysLookupMultiSelectGrid::lookup(query, _control, _control, cnt);
}

you may have to create 3 more methods to run your code without any
error. They are getFromDialog, initializeFields
and postRun. Here is the code for these methods. you have to change the contract class name with your
contract class
First create a new mothod for initializeFields and paste the following code

public void
initializeFields()
{
  custMultiSelectContract contract = this.dataContractObject();
}

Then create another method for getFromDialog

public void getFromDialog()
{
  custMultiSelectContract contract = this.dataContractObject();
  super();
}

and then another method for postRun

public void postRun()
{
  custMultiSelectContract contract = this.dataContractObject();
}

That’s it. You are now done. Run your report and enjoy.
Thanks