Individual financial dimension value through X++

display DimensionValue displayLocDimension()
{ 
    VendTable                                          vendTable;
    DimensionAttributeValueSet             dimensionAttributeValueSet;
    DimensionAttributeValueSetItem      dimensionAttributeValueSetItem;
    DimensionAttributeValue                  dimensionAttributeValue;
    DimensionAttribute                          dimensionAttribute;
   VendTrans                                          vendtrans
    DimensionValue                               location;

      select * from vendTable
                           where vendTable.AccountNum == "XXXX"
            join RecId from dimensionAttributeValueSet
                          where dimensionAttributeValueSet.RecId == vendtrans.DefaultDimension
            join RecId, DisplayValue from dimensionAttributeValueSetItem
                          where dimensionAttributeValueSetItem.DimensionAttributeValueSet == dimensionAttributeValueSet.RecId
            join RecId from dimensionAttributeValue
                        where dimensionAttributeValue.RecId == dimensionAttributeValueSetItem.DimensionAttributeValue
            join RecId, Name from DimensionAttribute
                       where dimensionAttribute.RecId == dimensionAttributeValue.DimensionAttribute
                       && dimensionAttribute.Name == "LOCATION";
            {
                location = DimensionAttributeValueSetItem.DisplayValue;
            }
                return location;
  }

userwise security roles

static void userSecurityExcelDocumentnew(Args _args)
{
    SysExcelWorksheetHelper     worksheetHelper;
    SysExcelHelper              sysExcelHelper;
    SysExcelWorkSheet           xlsWorkSheet;
    dialogField                 dialogFilename;
    Dialog                      dialog;
    SecurityUserRole            _SecurityUserRole;
    SecurityRoleTaskGrant       _SecurityRoleTaskGrant;
    SecurityTask                _SecurityTask;
    SecurityRole                _SecurityRole;
    UserInfo                    _UserInfo;
    SysUserInfo                 _sysUserInfo;
    FilenameOpen                filename;
    str                         worksheetName;
    int                         row = 1;
    SysOperationProgress Progressbar = new SysOperationProgress();
    #Excel
    ;
    startLengthyOperation();
    dialog              =   new Dialog("Excel Upoad");
   dialogFilename      =   dialog.addField(extendedTypeStr(FilenameOpen));
   dialog.filenameLookupFilter(["@SYS28576",#XLS,"@SYS28576",#XLSX]);
   dialog.filenameLookupTitle("Export to Excel");
   dialog.caption("User Security details");
   dialogFilename.value(filename);
   if(!dialog.run())
   return;
   filename            =   dialogFilename.value();

    worksheetName = strfmt("%1", date2str(systemdateget(),321,DateDay::Digits2,DateSeparator::None,DateMonth::Digits2,DateSeparator::None,DateYear::Digits4, DateFlags::None));
    sysExcelHelper = SysExcelHelper::construct();
    sysExcelHelper.initialize();
    xlsWorkSheet = sysExcelHelper.addWorksheet(worksheetName);

    worksheetHelper = SysExcelWorksheetHelper::construct(xlsWorkSheet);

     worksheetHelper.addColumn(1, 'User Id', Types::String);
     worksheetHelper.addColumn(2, 'User Name', Types::String);
     worksheetHelper.addColumn(3, 'User Role', Types::String);
     worksheetHelper.addColumn(4, 'Department', Types::String);
     worksheetHelper.addColumn(5, 'Active', Types::String);
     worksheetHelper.addColumn(6, 'Legal Entity', Types::String);
     worksheetHelper.addColumn(7, 'Created Date Time', Types::Date);
     worksheetHelper.addColumn(8, 'Modified Date Time', Types::Date);

     worksheetHelper.setCellValue(1, row,'User Id');
     worksheetHelper.setCellValue(2, row,'User Name');
     worksheetHelper.setCellValue(3, row,'User Role');
     worksheetHelper.setCellValue(4, row,'Department');
     worksheetHelper.setCellValue(5, row,'Active');
     worksheetHelper.setCellValue(6, row,'Legal Entity');
     worksheetHelper.setCellValue(7, row,'Created Date Time');
     worksheetHelper.setCellValue(8, row,'Modified Date Time');

    row++;
    while select _UserInfo
    {
        select _sysUserInfo where _sysUserInfo.Id == _UserInfo.id;
        while select _SecurityUserRole where _SecurityUserRole.User==_UserInfo.id
        {
                while select _SecurityRole where _SecurityRole.RecId==_SecurityUserRole.SecurityRole
                {
                    worksheetHelper.setCellValue(1, row,_UserInfo.id);
                    worksheetHelper.setCellValue(2, row,_UserInfo.name);
                    worksheetHelper.setCellValue(3, row,_SecurityRole.name);
                    worksheetHelper.setCellValue(4, row,_sysUserInfo.departmentName());
                    worksheetHelper.setCellValue(5, row,_UserInfo.enable);
                    worksheetHelper.setCellValue(6, row,_UserInfo.company);
                    worksheetHelper.setCellValue(7, row,_sysUserInfo.createdDateTime);
                    worksheetHelper.setCellValue(8, row,_sysUserInfo.modifiedDateTime);

                row++;
                }
        }
    }

    if(WinApi::fileExists(fileName))
    WinApi::deleteFile(fileName);

    sysExcelHelper.save(filename);
    sysExcelHelper.close();

    info(strFmt("Created file: %1", fileName));
}

staging to target mapping is not done

Error : staging to target mapping is not done

Solution :
static void regenerateDMFTargetMapping(Args _args)
{
    DMFTargetXMLToEntityMap     dmfTargetXMLToEntityMap;
    DMFEntity                                   dmfEntity;

    // delete all target Mappings
    ttsBegin;
        delete_from dmfTargetXMLToEntityMap;
    ttsCommit;

    // Regenerate Target Mapping for all Entities
    while select dmfEntity
    {
        DMFTargetXMLToEntityMap::generateMapping(dmfEntity);
    } 
    info("All Done!");

}

Note : Above job will run for all entities. If you want run only particular one entity add where condition in Regenerate Target Mapping.

How to fix Suspended Values in Financial Dimensions lookup in Customer ,vendor....etc.

Forms >DimensionDefaultingLookup >Methods>init

  //Add below code++
        qbds =  queryBuildDataSource.addDataSource(tableNum(DimensionAttributeValue));
        qbds.relations(true);
        qbds.joinMode(JoinMode::InnerJoin);
        qbds.fetchMode(QueryFetchMode::One2One);
        qbds.addLink(dimAttrViewContract.parmKeyFieldId(),fieldNum(DimensionAttributeValue,EntityInstance));
        qbds.addRange(fieldNum(DimensionAttributeValue,DimensionAttribute)).value(strFmt("%1",dimAttr.RecId));
        qbds.addRange(fieldNum(DimensionAttributeValue,IsSuspended)).value(SysQuery::value(NoYes::No));

        ledger = Ledger::findByLegalEntity(CompanyInfo::find().RecId).RecId;

        qbdsLedger = qbds.addDataSource(tableNum(DimensionAttrValueLedgerOverride));
        qbdsLedger.joinMode(JoinMode::NoExistsJoin);
        qbdsLedger.fetchMode(QueryFetchMode::One2One);
        qbdsLedger.addLink(fieldNum(DimensionAttributeValue,RecId),fieldNum(DimensionAttrValueLedgerOverride,DimensionAttributeValue));
        qbdsLedger.addRange(fieldNum(DimensionAttrValueLedgerOverride,Ledger)).value(strFmt("%1",ledger));
        qbdsLedger.addRange(fieldNum(DimensionAttrValueLedgerOverride,IsSuspended)).value(SysQuery::value(NoYes::Yes));
        //Add below code--

Reference:

https://community.dynamics.com/ax/b/meritmatters/archive/2016/01/28/financial-dimensions-lookup

Powershell Commands

below link is very useful for export, import ...etc powershell commands

http://axbloggerblog.blogspot.in/p/powershell-commands.html

How to run a Report through X++ code and save to PDF format

static void autoSave_PDF_SSRSSalesInoive(Args _args)
{
    Dialog                                    dialog;
    DialogField                          fromDate, toDate, invoiceId, folderPath;
    CustInvoiceJour                  custInvoiceJourLocal;
    FilePath                              filePath, slash = @"\";
    Filename                            fileName;
    Args                                   args = new Args();
    boolean                             folderexists,fileExists;
    str                                      idstr;
    int                 poss;
    SrsReportRunController              ssrsController = new SrsReportRunController();
    InvoiceContract                           salesConfirmContract = new InvoiceContract();
    SRSPrintDestinationSettings      printerSettings;  
    ;
    dialog = new Dialog("Sales Invoices save to PDF");
    fromDate = dialog.addField(extendedTypeStr(TransDate), "Select From Date");
    toDate = dialog.addField(extendedTypeStr(TransDate), "Select To Date");
    if(dialog.run())
    {
        if(curUserId() == "userid")
            filePath = @"C:\Users\userid\Desktop\Invoices\" + strFmt("%1%2%3",fromDate.value(), " To ", toDate.value()) + slash;
        else
            filePath = @"C:\JB\" + strFmt("%1%2%3",fromDate.value(), " To ", toDate.value()) + slash;
        while(poss < strLen(filePath))
        {
            poss = strFind(filePath, "/", poss, strLen(filePath));
            filePath = strPoke(filePath, "-", poss);
            if(!poss)
                break;
        }
        folderexists = WinAPI::folderExists(filePath);
        if(!folderexists)
            WinAPI::createDirectoryPath(filePath);
         while select custInvoiceJourLocal where custInvoiceJourLocal.InvoiceDate >=     fromDate.value() && custInvoiceJourLocal.InvoiceDate <= toDate.value()
              {
            fileName = filePath + strFmt("%1%2%3%4%5",custInvoiceJourLocal.PrintMgmtSiteId + "_",custInvoiceJourLocal.InvoiceId, "_" , custInvoiceJourLocal.InvoiceDate, ".pdf");
            while(poss < strLen(fileName))
            {
                poss = strFind(fileName, "/", poss, strLen(fileName));
                fileName = strPoke(fileName, "-", poss);
                if(!poss)
                    break;
            }
            fileExists = System.IO.File::Exists(fileName);
            if(!fileExists)
            {
                ssrsController.parmReportName(ssrsReportStr(SalesInvoice, Report));
                   //define how we want to execute the report (right now or batch style)
                ssrsController.parmExecutionMode(SysOperationExecutionMode::Synchronous);
                //hide the report dialog
                ssrsController.parmShowDialog(false);

                //we need to populate the required parms for the current sales order controller
                salesConfirmContract.parmInvoiceId(custInvoiceJourLocal.InvoiceId);
                salesConfirmContract.parmInvoiceDate(custInvoiceJourLocal.InvoiceDate);
                salesConfirmContract.parmSalesId(custInvoiceJourLocal.SalesId);
 
                //link the contract to the controller so we know how to run the dp
                ssrsController.parmReportContract().parmRdpContract(salesConfirmContract);

                //link the printer settings to the controller
                printerSettings = ssrsController.parmReportContract().parmPrintSettings();
                //print to pdf and always overwrite if the file exists
                printerSettings.printMediumType(SRSPrintMediumType::File);
                printerSettings.fileFormat(SRSReportFileFormat::PDF);
                printerSettings.overwriteFile(true);
                printerSettings.fileName(fileName);

                //run & save the report
                ssrsController.startOperation();
               info(strFmt("File Saved at %1", fileName));
            }
        }
    }
}

How to delete Duplicate Records through X++ in Dynamics Ax

static void DupDeleteRecords(Args _args)
{
VendTable       vendTable ;
int                   c1,c2;
Set fieldset = new set(types::Integer);

// create dictindex from unique index

DictIndex dictIndex = new dictIndex (tablenum(VendTable  ), indexnum(VendTable  ,AccountNumIdx));

;

select count(RecId) from vendTable  ;

c1=vendTable .RecId;

// these are fields from index add them to set

fieldset.add(fieldnum(VendTable  ,AccountNum));

ReleaseUpdateDB::indexAllowDup(dictIndex);

// set allow duplicates

ReleaseUpdateDB::deleteDuplicatesUsingIds(tablenum(VendTable  ),0,fieldset);

//re enable duplicates

ReleaseUpdateDB::indexAllowDup(dictIndex);

select count(RecId) from vendTable ;

c2=vendTable .RecId;

if(c1!=c2)
info(“Duplicate Deletion done”);
else
info(“No Duplicates found”);

}

force DB Synchronize :

static void forceDbSynchronize1(Args _args)
{
Dictionary dict;
int idx, lastIdx, totalTables;
TableId tableId;
Application application;
SysOperationProgress progress;
StackBase errorStack;
ErrorTxt errorTxt;
;

application = new Application();
dict = new Dictionary();
totalTables = dict.tableCnt();
progress = new SysOperationProgress();
progress.setTotal(totalTables);
progress.setCaption(“@SYS90206″);
errorStack = new StackBase(Types::String);

lastIdx = 0;
try
{
for (idx = lastIdx+1; idx <= totalTables; idx++)
{
tableId = dict.tableCnt2Id(idx);
progress.setText(dict.tableName(tableId));

lastIdx = idx;
application.dbSynchronize(tableId, false, true, false);
progress.incCount();
}
}
catch (Exception::Error)
{
errorTxt = strFmt("Error in table '%1' (%2)", tableId, dict.tableName(tableId));
errorStack.push(errorTxt);
retry;
}

setPrefix("@SYS86407");
errorTxt = errorStack.pop();
while (errorTxt)
{
error(errorTxt);
errorTxt = errorStack.pop();
}
}