give extra space in between date digit for cheque printing in SSRS report

static void Job12(Args _args)
{

 
      Transdate dat;
      Str s1,s2,s3,s4,s5,s6,s7,s8;
      str  nh;
   
      ;
   
     dat = mkdate(1,5,2018);
 
       nh = stralpha(date2str(dat,123,2,2,2,2,4));
   
     s1 = substr(nh,1,1);
     s2 = substr(nh,2,1);
     s3 = substr(nh,3,1);
     s4 = substr(nh,4,1);
     s5 = substr(nh,5,1);
     s6 = substr(nh,6,1);
     s7 = substr(nh,7,1);
     s8 = substr(nh,8,1);

   
     info(strfmt("%1  %2  %3  %4  %5  %6  %7  %8  %9",nh,s1,s2,s3,s4,s5,s6,s7,s8));
}

 // Use Display Method for 8 Square Box which contains String Controls

   // and in those 8 display methods apply  below concept for respective controls

    SysDate DateVal;

    str     DateStrVal;

    int     DateLength;

    str     DayDigit1, DayDigit2;

    str     MonthDigit1, MonthDigit2;

    str     YearDigit1, YearDigit2, YearDigit3, YearDigit4;

    ;

    DateVal = today();

    DateStrval = date2str(DateVal,   // DateValue

                         123,        // Date Month Year Sequence

                         2,          // Display Day Format in 2 digits

                         1,          // Separator1

                         2,          // Display Month Format in 2 Digits

                         1,          // Separator2

                         4);         // Display Year Format in 4 digits

    info(DateStrVal);   

    DateLength = strLen(DateStrval);   // Length Of Date  

    info(int2Str(DateLength));   

    DayDigit1 = substr(DateStrval, 1, 1); // Getting First Digit of Date

    info(DayDigit1);

    DayDigit2 = substr(DateStrval, 2, 1); // Getting Second Digit of Date

    info(DayDigit2);

    MonthDigit1 = substr(DateStrval, 4, 1); // Getting First Digit of Month

    info(MonthDigit1);

    MonthDigit2 = substr(DateStrval, 5, 1); // Getting Second Digit of Month

    info(MonthDigit2);

    YearDigit1 = substr(DateStrval, 7, 1); // Getting First Digit of Year

    info(YearDigit1);

    YearDigit2 = substr(DateStrval, 8, 1); // Getting Second Digit of Year

    info(YearDigit2);

    YearDigit3 = substr(DateStrval, 9, 1); // Getting Second Digit of Year

    info(YearDigit3);

    YearDigit4 = substr(DateStrval, 10, 1); // Getting Second Digit of Year

    info(YearDigit4);

How to Setup Date Format on AX SSRS Report

Hi

If you want to setup a different Format Date on a SSRS report, you can follow the below options:

1- Modify date format for all date textbox present in the SSRS Report using the Format function, like =Format(Fields!TransDate.Value, "dd/MM/yyyy")

2- If you want follow the User Timezone, for all date textbox you can use ConvertUtcToAxUserTimeZoneForUser method, like =Microsoft.Dynamics.Framework.Reports.DataMethodUtility.ConvertUtcToAxUserTimeZoneForUser(Parameters!AX_CompanyName.Value, Parameters!AX_UserContext.Value, =Fields!TransDate.Value, "d", Parameters!AX_RenderingCulture.Value)

Enjoy!

CurrencyExchangeHelper class in Dynamics AX 2012 [X+

CurrencyExchangeHelper class in Dynamics AX 2012 [X++]

Friends,

In Microsoft Dynamics AX 2012, the currency and exchange rate framework has been enhanced to share information across multiple legal entities.

There is a new class by name CurrencyExchangeHelper that has been introduced in AX 2012 to support this.

This class will help you to do some calculations between currencies. Some important methods to use:

calculateTransactionToAccounting

Example : This method will convert the transaction currency in to accounting currency defined in ledger Table.

static void SR_CEH_Example1(Args _args)

{

    CurrencyExchangeHelper currencyExchangeHelper;

    CurrencyCode transCurrency = ‘EUR’;

    AmountCur amountCur = 500.00;

    AmountMst amountMST;

   

    currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::current(), systemDateGet());

    amountMST = currencyExchangeHelper.calculateTransactionToAccounting(transCurrency, amountCur ,true);

    info(strFmt(‘%1’,amountMST));

}

Result :

calculateAccountingToTransaction

This method calculates the transaction currency amount from an accounting currency given.

static void SR_CEH_Example2(Args _args)

{

    CurrencyExchangeHelper currencyExchangeHelper;

    CurrencyCode transCurrency = ‘EUR’;

    AmountCur amountCur;

    AmountMst amountMST = 500.00;

   

    currencyExchangeHelper = CurrencyExchangeHelper::newExchangeDate(Ledger::primaryLedger(CompanyInfo::findDataArea("DUM").RecId), systemDateGet());

    amountCur = currencyExchangeHelper.calculateAccountingToTransaction(transCurrency, amountMST ,true);

    info(strFmt(‘%1’,amountcur));

}

Result :

While searching through, I found that there are  parmExchangeRate1 and parmExchangeRate2  methods that to calculate based on the exchange rates that have been provided. Please refer to the below example which calculates the misc charges [markup amount] based on the exchange rates defined.

Class Name : Markup >> calcMarkupAmount

Exploring more…will post soon!

Happy Dax6ng,

AX SSRS Report times out after 10 minutes (Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)

AX SSRS Report times out after 10 minutes (Error: A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond)

Sometimes when running a report that is going to return a large data set you may get the following error message after 10 minutes:

"A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond"

Whenever you receive this error the first thing you should look at is your DP class and can performance be improved by rewriting some of your code. If it cant be or this is a default ax report sometimes your only option is to increase the default timeout period.

In order to increase the timeout for an AX SSRS report you need to do the following:

1.) Create a new client config(or copy an existing one.) This is the same config that could be used as your .axc file that is used to access different environments/aos. However this will have larger timeout values in it so only use this on the server mentioned in step 4. You do not need your user's applications config to have these settings.

2.) Update the binding node of "QueryServiceEndPoint" and change the sendTimeout property from "00:10:00" (10 minutes) to whatever timeout value you like in hh:mm:ss format. You can also update any other timeout setting as well if you like. (Just search for 00:10:00 within the document and replace it with your new time)

3.) Save the new config as  "Microsoft.Dynamics.AX.ReportConfiguration.axc" (https://technet.microsoft.com/en-us/library/hh389774.aspx)

4.) Copy the new file Microsoft.Dynamics.AX.ReportConfiguration.axc into the following folder on your sql/ssrs server (inside of the os/windows file system)

C:\Program Files\Microsoft SQL Server\MSRS11.<AOS>\Reporting Services\ReportServer\bin

Note: This is for a sql2012 instance. If you are running a different version of ms sql please refer to the following TechNet article to find the location of your report server bin folder https://technet.microsoft.com/en-us/library/hh389774.aspx if you need help locating it.

5.) Run the report and the report should no longer time out. You do not need to restart the aos, sql or ssrs services in order for the changes to take place once the file is in this folder it should now respect your new timeout settings


If this does not fix your issue please refer to the following sites which can also help you on time out issues with ssrs reports

TechNet: Tips to help prevent long-running reports from timing out [AX 2012]

You can also change the timeout settings on ssrs via the following method:

Global timeout setting: Go to site settings > General > Report Timeout > do not timeout report.
Report timeout setting:  Go to the report > Manage > Processing options > Report time out> do not timeout report.


There are also some timeout settings if you connect to the reporting service via sql management studio. Right click on your server node and go to properties > Advanced.


Update: Also see AX SSRS - Regular Processing (SRSReportDataProviderBase) VS PreProcessing (SrsReportDataProviderPreProcess) (fixes timeout issues on reporting)


Update 2: After applying the Microsoft.axc file to the ssrs server you may run into a security issue (Microsoft Dynamics AX 2012 Reporting extensions: Error System.Security.Permissions.EnvironmentPermission while running report)

To fix this go to the SSRS installation folder and modify rssrvpolicy.config Report_Expressions_Default_Permissions node from "Execution" to "FullTrust" (https://blogs.msdn.microsoft.com/axsupport/2012/02/02/microsoft-dynamics-ax-2012-reporting-extensions-error-system-security-permissions-environmentpermission-while-running-report/) 

Microsoft Dynamics AX 2012 SSRS REPORT ERROR: a connection attempt failed because the connected party did not properly respond after a period of time

AX SSRS - Regular Processing (SRSReportDataProviderBase) VS PreProcessing (SrsReportDataProviderPreProcess) (fixes timeout issues on reporting)

Microsoft Dynamics AX 2012 SSRS REPORT ERROR: a connection attempt failed because the connected party did not properly respond after a period of time

Sometimes in AX we need to create reports that exceed the default run-time limits defined in AX (default is 10minutes) because of the amount of data we are either calculating or returning. As discussed in one of my other posts (AX SSRS Report times out after 10 minutes) one of the ways to overcome this is to change the config files of the client/ssrs server but another way to overcome this limit and improve the speed in which a report can be ran is changing your data provider to extend the class 'SrsReportDataProviderPreProcess' instead of the regular type of 'SRSReportDataProviderBase'

I found this great blog entry over at msdn (click here) with this nice graphic shown below that explains the differences between the two. But long story short the difference is we are utilizing the aos to generate the data then handing the results to ssrs rather than handing the logic to ssrs and letting it generate the data. By doing this we can bypass the time out that is defined in ssrs because the data is already generated before we hand it off and we can improve runtime because it is now generating the data on the aos server.


Regular processing:



Pre-processing:

In order to switch from the regular process we need to make the following changes:

1. On the table that is returned as a dataset to the report make the following changes
   1. Created by - Yes
   2. CreatedTransactionId - Yes
   3. TableType - Regular
2. Change the DP class so it extends SrsReportDataProviderPreProcess
3. Within the DP class.processReport() method add the following code reportData.setConnection(this.parmUserConnection()); where reportData is the table that is returned as a dataset within your report
4. Execute a incremental cil
5. Open the SSRS report and hit refresh on the dataset. At this point you should see the field 'createdTransactionId' added to the available field.
6. Deploy report
7. Security Note: For your security object it is good to note that when using this preprocessing method you need to add the DPClass.processReport() method to the Server methods node in the privilege associated with the report

A table, Extended Data Type, Base Enum or class called ExtBankTable already exists. Import of Table aborted.

A table, Extended Data Type, Base Enum or class called ExtBankTable already exists. Import of Table aborted.

Resolved :
Maybe the thing is that IMPORT should be done wihout IDs.So I try delete the cache files for my client AX system environment.
Cache files – close the client and then delete files .AUC files in cache directory:
* C:\Documents and Settings\[USERNAME]\Local Settings\Application Data for Win Xp or Server 2003.
* C:\Users\[USERNAME]\AppData\Local for Vista/Win7.   [such as : ax_{19F9525D-A1C5-4858-97D9-7863F8FE814A}.auc]

Date functions in AX 2009 and 2012

Date functions in AX 2009and2012

Hi....

These are some of the functions,from where we can get the day or month or year from the date...
Here is the below example....

static void date_Functions(Args _args)
{
    Transdate    d;
    ;
   
    d = today();
   
    info(strfmt("Date - %1",d));
   
    //Gets the month for the given date...
    info(strfmt("Month - %1",mthofYr(d)));
   
    //Gets the month name from the given date...
    info(strfmt("Month Name - %1",mthname(mthofYr(d))));
   
    //Gets the day for the given date...
    info(strfmt("Day - %1",dayOfMth(d)));
   
    //Gets the day name from the given date...
    info(strfmt("Day Name - %1",dayname(dayOfMth(d))));
   
    //Gets the year for the given date...
    info(strfmt("Year - %1",year(d)));
   
    //Gets the current weekday number from the date...
    info(strfmt("Weekday number - %1",dayOfwk(d)));
   
    //Gets the day of the year from the given date...
    info(strfmt("Day of year - %1",dayOfyr(d)));
   
    //Gets the week of the year from the given date...
    info(strfmt("Week of the year - %1",wkofyr(d)));
}

SSRS Contract Class in AX 2012

[
    DataContractAttribute,
    SysOperationContractProcessingAttribute(classstr(GOD_RevenueUIBuilder))
]
class GOD_RevenueContract
{
    FromDate    fromDate;
    ToDate      toDate;
    List        verticalDimensionList;
}


[DataMemberAttribute("fromDate")]
public FromDate parmFromDate(FromDate _fromDate = fromDate)
{
    fromDate = _fromDate;
    return fromDate;
}


[DataMemberAttribute("toDate")]
public ToDate parmToDate(ToDate _toDate = toDate)
{
    toDate = _toDate;
    return toDate;
}


[
    DataMemberAttribute("verticalDimensionList"),
    AifCollectionTypeAttribute("verticalDimensionList", Types::String),
    SysOperationLabelAttribute(literalStr("@SYS62571"))
]
public List parmverticalDimensionList(List _verticalDimensionList = verticalDimensionList)
{
    verticalDimensionList = _verticalDimensionList;

    return verticalDimensionList;
}





/////////////////////////////////////////////////////////////////////////////////////////////////////
[
    SRSReportParameterAttribute(classStr(GOD_RevenueContract))
]
class GOD_RevenueDP extends SRSReportDataProviderBase//SrsReportDataProviderPreProcess//  //
{
    GOD_RevenueContract     contract;
    GOD_RevenueTmp          tempTable;
    ListIterator            dimListIterator;
    FromDate                fromDate;
    ToDate                  toDate;
    #GOD_DefaultDimension
}


[
    SRSReportDataSetAttribute(tableStr(GOD_RevenueTmp))
]
public GOD_RevenueTmp getTmpTable()
{
    select * from tempTable;

    return tempTable;
}

[SysEntryPointAttribute]
public void processReport()
{
    CustInvoiceJour         custInvoiceJour;
    MainAccount             mainAccount;
    GOD_IntegrationPostingProfile       postingProfile;
    DimensionValue                      dimvalue;
    int                                 dimExists;
    TaxTrans                            taxTrans;
    boolean                 isTaxInserted = false;
    container               vertDims = conNull();
    GOD_GeneralJournalSubledgerVoucher  generalJournalEntry;
    contract = this.parmDataContract() as GOD_RevenueContract;
    fromDate        =   contract.parmFromDate();
    toDate          =   contract.parmToDate();

    if(contract.parmverticalDimensionList())
    {
        dimListIterator = new ListIterator(contract.parmverticalDimensionList());

        while(dimListIterator.more())
        {
            vertDims += dimListIterator.value();

            dimListIterator.next();
        }
    }

    while select LedgerVoucher,InvoiceId,InvoiceDate  from custInvoiceJour
        where custInvoiceJour.InvoiceDate >= fromDate &&
              custInvoiceJour.InvoiceDate <= toDate// && custInvoiceJour.InvoiceId == 'IMTM-000001'
    {
        generalJournalEntry.clear();
        isTaxInserted = false;
        while select Voucher,LedgerDimension,MainAccount,AccountingCurrencyAmount from generalJournalEntry
                            where generalJournalEntry.Voucher == custInvoiceJour.LedgerVoucher
        {
            mainAccount = MainAccount::find(generalJournalEntry.MainAccount);
            select firstonly postingProfile where postingProfile.MainAccountId == mainAccount.MainAccountId;
            if(postingProfile)// || generalJournalEntry.PostingType == LedgerPostingType::ServiceTax_IN)
            {
                dimvalue = conPeek(GOD_getDimAttrFromLedgerDimension(#Vertical,generalJournalEntry.LedgerDimension),1);
                dimExists = conFind(vertDims,dimvalue);
                if(dimExists || !vertDims)
                {
                    tempTable.InvoiceId                 = custInvoiceJour.InvoiceId;
                    tempTable.InvoiceDate               = custInvoiceJour.InvoiceDate;
                    tempTable.DiscAmount                = custInvoiceJour.CashDisc;
                    tempTable.MainAccount               = mainAccount.Name;
                    tempTable.Vertical                  = dimvalue;
                    tempTable.EventName                 = conPeek(GOD_getDimAttrFromLedgerDimension(#Event,generalJournalEntry.LedgerDimension),2);
                    tempTable.AccountingCurrencyAmount  =  generalJournalEntry.AccountingCurrencyAmount;
                    if(isTaxInserted == false)
                    {
                        select sum(TaxAmount) from taxTrans where taxTrans.Voucher == generalJournalEntry.Voucher
                                                                    && taxTrans.GOD_isSBT == NoYes::No;
                                tempTable.STAmount              = taxTrans.TaxAmount;
                        taxTrans.clear();
                        select sum(TaxAmount) from taxTrans where taxTrans.Voucher == generalJournalEntry.Voucher
                                                                && taxTrans.GOD_isSBT == NoYes::Yes;
                            tempTable.SBAmount              = taxTrans.TaxAmount;
                        isTaxInserted = true;
                    }
                    else
                    {
                        tempTable.STAmount = 0;
                        tempTable.SBAmount = 0;
                    }
                    tempTable.insert();
                }
            }
        }
    }
}


///////////////////////


class GOD_RevenueUIBuilder extends SrsReportDataContractUIBuilder
{
    #GOD_DefaultDimension

    DialogField                 dialogfromDate,dialogtoDate,dialogDimName;
    GOD_RevenueContract       contract;
}
public void build()
{
    contract            = this.dataContractObject();
    dialogfromDate      = this.addDialogField(methodStr(GOD_RevenueContract, parmFromDate),contract);
    dialogtoDate        = this.addDialogField(methodStr(GOD_RevenueContract, parmToDate),contract);
    dialogDimName       = this.addDialogField(methodStr(GOD_RevenueContract, parmverticalDimensionList),contract);
    dialogDimName.value("");
    //dialogproductSubGroup.value("");
}

private void dimNameValueLookUp(FormStringControl dimNameLookUp)
{
    Query                   query = new Query();
    container   conDim;
    DimensionAttributeDirCategory   dimAttributeDirCategory;
    DimensionAttribute      dimensionAttribute;
    ;

    select firstonly Type, RecId from dimensionAttribute where dimensionAttribute.Name == #Vertical;
    if(dimensionAttribute.Type == DimensionAttributeType::CustomList)
    {
        select firstonly DirCategory from dimAttributeDirCategory where dimAttributeDirCategory.DimensionAttribute == dimensionAttribute.RecId;

        query.addDataSource(tableNum(DimensionFinancialTag)).

        addRange(fieldNum(DimensionFinancialTag, FinancialTagCategory)).

        value(queryValue(dimAttributeDirCategory.DirCategory));

        SysLookupMultiSelectGrid::lookup(query,dimNameLookUp,dimNameLookUp,conDim);
    }
}



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


public void postBuild()
{
    super();
    dialogfromDate              = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(GOD_RevenueContract, parmFromDate));
    dialogtoDate                = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(GOD_RevenueContract, parmToDate));
    dialogDimName                = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(GOD_RevenueContract, parmverticalDimensionList));

    dialogDimName.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(GOD_RevenueUIBuilder,dimNameValueLookUp), this);
    if (dialogDimName)
    {
        dialogDimName.lookupButton(2);
    }
}


public void postRun()
{
    //super();
}




////


[DataContractAttribute,
SysOperationContractProcessingAttribute(classstr(GOD_ServiceTaxPayUIBuilder))]

class GOD_ServiceTaxPayContract_New
{
    DimensionValue      vertical;
    yearBase            fromYear;
}

[DataMemberAttribute("year"),
SysOperationLabelAttribute(literalStr("@IMT197")),
SysOperationHelpTextAttribute(literalStr("@SYS107162")),
SysOperationDisplayOrderAttribute('2')]

public YearBase parmFromYear(YearBase _fromYear = fromYear)
{
    fromYear = _fromYear;
    return fromYear;
}

[DataMemberAttribute("DimensionValue"),
SysOperationLabelAttribute(literalStr("@SYS62571")),
SysOperationHelpTextAttribute(literalStr("@SYS62571")),
SysOperationDisplayOrderAttribute('1')]

public DimensionValue parmVertical(DimensionValue _vertical = vertical)
{
    vertical = _vertical;
    return vertical;
}


//

//////////////////////////////////////////////////////////
[  SRSReportParameterAttribute(classstr(GOD_ServiceTaxPayContract))]

public class GOD_ServiceTaxPayDP extends SRSReportDataProviderBase//SrsReportDataProviderPreProcess//
{
    #define.ServiceTax("Serv. Tax")
    #define.VAT("VAT")
    #GOD_DefaultDimension
    GOD_ServiceTaxPayTmp                    god_ServiceTaxTmp,god_ServiceTaxTmpCust;
    CustInvoiceJour                         custInvoiceJour;
    TaxTrans_IN                             taxTrans;
    MonthsOfYear                            MonthsOfYear;
    TransDate                               fromDate,toDate,curDate;
    Amount                                  preClosingBal;
    DimensionValue                          vertical;
    GeneralJournalAccountEntry              generalJournalAccountEntry;
    YearBase                                fromYear;
}

private Amount getAdditionsAmt(TransDate    _transdate)

{

    TransDate               fromd,tod;

    TaxTrans_IN             taxTransInOpening;

    ;

    fromd   = mkDate(1,mthOfYr(_transdate),year(_transdate));

    tod     = nextMth(fromd);

    select sum(TaxAmount) from taxTransInOpening

        where taxTransInOpening.Source == TaxModuleType::Purch ||

              taxTransInOpening.Source == TaxModuleType::Voucher &&

              taxTransInOpening.TransDate >= fromd &&

              taxTransInOpening.TransDate <= tod;

    return taxTransInOpening.TaxAmount;

}

public boolean getDimensionCombinationValues(GeneralJournalAccountEntry gjAccEntry)

{

    DimensionAttributeValueCombination  dimAttrValueComb;

    DimensionStorage        dimensionStorage;

    DimensionStorageSegment segment;

    int                     segmentCount, segmentIndex;

    int                     hierarchyCount, hierarchyIndex;

    str                     segmentName;

    SysDim                  segmentValue;

    ;

    dimAttrValueComb = DimensionAttributeValueCombination::find(gjAccEntry.LedgerDimension);

    dimensionStorage = DimensionStorage::findById(gjAccEntry.LedgerDimension);

    if (dimensionStorage == null)

    {

        throw error("@SYS83964");

    }

    hierarchyCount = dimensionStorage.hierarchyCount();

    for(hierarchyIndex = 1; hierarchyIndex <= hierarchyCount; hierarchyIndex++)

    {

        segmentCount = dimensionStorage.segmentCountForHierarchy(hierarchyIndex);

        for (segmentIndex = 1; segmentIndex <= segmentCount; segmentIndex++)

        {

            segment = dimensionStorage.getSegmentForHierarchy(hierarchyIndex, segmentIndex);

            if (segment.parmDimensionAttributeValueId() != 0)

            {

                segmentName          = DimensionAttribute::find(DimensionAttributeValue::find(segment.parmDimensionAttributeValueId()).DimensionAttribute).Name;

                segmentValue        = segment.parmDisplayValue();

                if(segmentName == #Vertical && segmentValue == vertical)

                {

                    return true;

                }

                else

                {

                    return false;

                }

            }

        }

    }

    return false;

}

private int getMonthNum(MonthsOfYear _month)

{

    switch(_month)

    {

        case MonthsOfYear::April :

            return 1;

        case MonthsOfYear::May :

            return 2;

        case MonthsOfYear::June :

            return 3;

        case MonthsOfYear::July :

            return 4;

        case MonthsOfYear::August :

            return 5;

        case MonthsOfYear::September :

            return 6;

        case MonthsOfYear::October :

            return 7;

        case MonthsOfYear::November :

            return 8;

        case MonthsOfYear::December :

            return 9;

        case MonthsOfYear::January :

            return 10;

        case MonthsOfYear::February :

            return 11;

        case MonthsOfYear::March :

            return 12;

    }

    return 0;

}

private Amount getOpeningBalance(TransDate    _asOnDate)

{

    TaxTrans_IN                 taxTransInOpening;

    GeneralJournalAccountEntry  generalJournalAccEntry;

    GeneralJournalEntry         generalJournalEntry;

    MainAccount                 mainAccnt;

    Amount                      openingBal;

    while select TaxAmount from taxTransInOpening

        where taxTransInOpening.TransDate < _asOnDate

    join SubledgerVoucher, RecId from generalJournalEntry

        where generalJournalEntry.SubledgerVoucher == taxTransInOpening.Voucher

    join MainAccount, PostingType from generalJournalAccEntry

        where generalJournalAccEntry.GeneralJournalEntry == generalJournalEntry.RecId &&

              generalJournalAccEntry.PostingType == LedgerPostingType::ServiceTax_IN

    join MainAccountId from mainAccnt

        where mainAccnt.RecId == generalJournalAccEntry.MainAccount &&

              mainAccnt.MainAccountId == "2001314"

    {

        if(this.getDimensionCombinationValues(generalJournalAccEntry))

        {

            openingBal += taxTransInOpening.TaxAmount;

        }

    }

    return  openingBal;

}

[SRSReportDataSetAttribute(tablestr(GOD_ServiceTaxPayTmp))]

public GOD_ServiceTaxPayTmp getServiceTaxPayTmpDetails()

{

    select  god_ServiceTaxTmp;

    return  god_ServiceTaxTmp;

}

public void insertFromCustTrans()

{

    TransDate                               currentDate;

    CustTrans                               custTrans;

    DimensionAttributeValueSetItem          dimensionAttributeValueSetItem;

    DimensionAttributeValue                 dimensionAttributeValue;

    DimensionAttribute                      dimensionAttribute;

    TransDate                               fd,td;

    while select forUpdate god_ServiceTaxTmpCust

    {

        if(god_ServiceTaxTmpCust.MonthNumber != 1)

        {

            god_ServiceTaxTmpCust.OpeningBal = preClosingBal;

        }

        if(god_ServiceTaxTmpCust.MonthNumber <= 9)

        {

            fd = mkDate(1,god_ServiceTaxTmpCust.MonthNumber + 3,fromYear);

        }

        else

        {

            fd = mkDate(1,god_ServiceTaxTmpCust.MonthNumber - 9,fromYear + 1);

        }

        td  = endmth(fd);

        select sum(SourceBaseAmountCur) from taxTrans

            where taxTrans.Source == TaxModuleType::FreeTxtInvoice &&

                  taxTrans.TransDate >= fd &&

                  taxTrans.TransDate <= td

        join custTrans

            where taxTrans.Voucher == custTrans.Voucher

        join dimensionAttributeValueSetItem

            where dimensionAttributeValueSetItem.DimensionAttributeValueSet == custTrans.DefaultDimension &&

                dimensionAttributeValueSetItem.DisplayValue == vertical

        join dimensionAttributeValue

            where dimensionAttributeValue.RecId == dimensionAttributeValueSetItem.DimensionAttributeValue

        join dimensionAttribute

            where dimensionAttribute.RecId == dimensionAttributeValue.DimensionAttribute &&

                  dimensionAttribute.Name == #Vertical;

        god_ServiceTaxTmpCust.TaxableAdvance    = abs(taxTrans.SourceBaseAmountCur);

        select sum(SourceBaseAmountCur) from taxTrans

            where taxTrans.Source == TaxModuleType::Voucher &&

                  taxTrans.TransDate >= fd &&

                  taxTrans.TransDate <= td

        join custTrans

            where taxTrans.Voucher == custTrans.Voucher

        join dimensionAttributeValueSetItem

            where dimensionAttributeValueSetItem.DimensionAttributeValueSet == custTrans.DefaultDimension &&

                dimensionAttributeValueSetItem.DisplayValue == vertical

        join dimensionAttributeValue

            where dimensionAttributeValue.RecId == dimensionAttributeValueSetItem.DimensionAttributeValue

        join dimensionAttribute

            where dimensionAttribute.RecId == dimensionAttributeValue.DimensionAttribute &&

                  dimensionAttribute.Name == #Vertical;

        god_ServiceTaxTmpCust.TaxableIncome     = abs(taxTrans.SourceBaseAmountCur);

        select sum(TaxAmount) from taxTrans

            where taxTrans.TaxPeriod == #VAT &&

                  taxTrans.TransDate >= fd &&

                  taxTrans.TransDate <= td

        join custTrans

            where taxTrans.Voucher == custTrans.Voucher

        join dimensionAttributeValueSetItem

            where dimensionAttributeValueSetItem.DimensionAttributeValueSet == custTrans.DefaultDimension &&

                dimensionAttributeValueSetItem.DisplayValue == vertical

        join dimensionAttributeValue

            where dimensionAttributeValue.RecId == dimensionAttributeValueSetItem.DimensionAttributeValue

        join dimensionAttribute

            where dimensionAttribute.RecId == dimensionAttributeValue.DimensionAttribute &&

                  dimensionAttribute.Name == #Vertical;

        god_ServiceTaxTmpCust.VAT               = abs(taxTrans.TaxAmount);

       select sum(TaxAmount) from taxTrans

            where taxTrans.TaxPeriod == #ServiceTax &&

                  taxTrans.TransDate >= fd &&

                  taxTrans.TransDate <= td

        join DefaultDimension, Voucher from custTrans

            where taxTrans.Voucher == custTrans.Voucher

        join DimensionAttributeValue, DisplayValue from dimensionAttributeValueSetItem

            where dimensionAttributeValueSetItem.DimensionAttributeValueSet == custTrans.DefaultDimension &&

                dimensionAttributeValueSetItem.DisplayValue == vertical

        join DimensionAttribute from dimensionAttributeValue

            where dimensionAttributeValue.RecId == dimensionAttributeValueSetItem.DimensionAttributeValue

        join Name from dimensionAttribute

            where dimensionAttribute.RecId == dimensionAttributeValue.DimensionAttribute &&

                  dimensionAttribute.Name == #Vertical;

        god_ServiceTaxTmpCust.ServiceTax            = abs(taxTrans.TaxAmount);

        god_ServiceTaxTmpCust.Total                 = god_ServiceTaxTmpCust.VAT + god_ServiceTaxTmpCust.ServiceTax;

        this.insertFromVendTrans(fd,td);

        if(god_ServiceTaxTmpCust.Total > (god_ServiceTaxTmpCust.Additions + god_ServiceTaxTmpCust.OpeningBal))

        {

            god_ServiceTaxTmpCust.Utilisations      = god_ServiceTaxTmpCust.Additions + god_ServiceTaxTmpCust.OpeningBal;

            god_ServiceTaxTmpCust.ClosingBalance    = 0;

        }

        else

        {

            god_ServiceTaxTmpCust.Utilisations      = god_ServiceTaxTmpCust.Total;

            god_ServiceTaxTmpCust.ClosingBalance    = god_ServiceTaxTmpCust.Utilisations - (god_ServiceTaxTmpCust.Additions + god_ServiceTaxTmpCust.OpeningBal);

        }

        preClosingBal                               = god_ServiceTaxTmpCust.ClosingBalance;

        currentDate = nextMth(fd);

        if(god_ServiceTaxTmpCust.MonthNumber == 12)

        {

            god_ServiceTaxTmpCust.DueDate               = mkDate(31,mthOfYr(fd),year(fd));

        }

        else

        {

            god_ServiceTaxTmpCust.DueDate               = mkDate(5,mthOfYr(currentDate),year(currentDate));

        }

        god_ServiceTaxTmpCust.update();

    }

}

public void insertFromVendTrans(FromDate _fd, ToDate _td)

{

    VendTrans                               vendTrans;

    DimensionAttributeValueSetItem          dimensionAttributeValueSetItem;

    DimensionAttributeValue                 dimensionAttributeValue;

    DimensionAttribute                      dimensionAttribute;

    select sum(TaxAmount) from taxTrans

        where taxTrans.TaxPeriod == #ServiceTax &&

              taxTrans.TransDate >= _fd &&

              taxTrans.TransDate <= _td

    join DefaultDimension, Voucher from vendTrans

        where taxTrans.Voucher == vendTrans.Voucher

    join DimensionAttributeValue, DisplayValue from dimensionAttributeValueSetItem

        where dimensionAttributeValueSetItem.DimensionAttributeValueSet == vendTrans.DefaultDimension &&

              dimensionAttributeValueSetItem.DisplayValue == vertical

    join DimensionAttribute from dimensionAttributeValue

        where dimensionAttributeValue.RecId == dimensionAttributeValueSetItem.DimensionAttributeValue

    join Name from dimensionAttribute

        where dimensionAttribute.RecId == dimensionAttributeValue.DimensionAttribute &&

              dimensionAttribute.Name == #Vertical;

    god_ServiceTaxTmpCust.Additions = taxTrans.TaxAmount;

}

[SysEntryPointAttribute(false)]

public void processReport()

{

    #define.VERTICALS("VERTICALS")

    GOD_ServiceTaxPayContract               dataContract;

    int                                     flag = 0;

    TransDate                               fd;

    delete_from god_ServiceTaxTmp;

    dataContract = this.parmDataContract() as GOD_ServiceTaxPayContract;

    vertical        = dataContract.parmVertical();

    fromYear        = dataContract.parmFromYear();

    fromDate        = mkDate(1,4,fromYear);

    fd              = fromDate;

    toDate          = mkDate(31,3,fromYear+1);

    ttsBegin;

    while(fd < toDate)

    {

        god_ServiceTaxTmp.clear();

        god_ServiceTaxTmp.month                 = str2enum(MonthsOfYear,mthName(mthOfYr(fd)));

        god_ServiceTaxTmp.MonthNumber           = this.getMonthNum(god_ServiceTaxTmp.month);

        god_ServiceTaxTmp.FromDate              = fromDate;

        god_ServiceTaxTmp.ToDate                = toDate;

        if(god_ServiceTaxTmp.MonthNumber == 1)

        {

            god_ServiceTaxTmp.OpeningBal        = this.getOpeningBalance(fromDate);

        }

        else

        {

            god_ServiceTaxTmp.OpeningBal        = 0;

        }

        god_ServiceTaxTmp.insert();

        fd = nextMth(fd);

    }

    this.insertFromCustTrans();

    ttsCommit;

}

private void updateClosingBalance()

{

    GOD_ServiceTaxPayTmp        god_ServiceTaxPayTmpUp;

    while select god_ServiceTaxPayTmpUp

    {

        god_ServiceTaxPayTmpUp.selectForUpdate(true);

        ttsBegin;

        god_ServiceTaxPayTmpUp.Total             = god_ServiceTaxPayTmpUp.VAT + god_ServiceTaxPayTmpUp.ServiceTax;

        if((god_ServiceTaxPayTmpUp.Additions + god_ServiceTaxPayTmpUp.OpeningBal) < god_ServiceTaxPayTmpUp.Total)

        {

            god_ServiceTaxPayTmpUp.OpeningBal       = preClosingBal;

            god_ServiceTaxPayTmpUp.Utilisations     = god_ServiceTaxPayTmpUp.Additions + god_ServiceTaxPayTmpUp.OpeningBal;

            god_ServiceTaxPayTmpUp.NetServiceTax    = god_ServiceTaxPayTmpUp.Total -(god_ServiceTaxPayTmpUp.OpeningBal + god_ServiceTaxPayTmpUp.Additions);

            god_ServiceTaxPayTmpUp.ClosingBalance   = 0;

        }

        else

        {

            god_ServiceTaxPayTmpUp.OpeningBal       = preClosingBal;

            god_ServiceTaxPayTmpUp.Utilisations     = god_ServiceTaxPayTmpUp.Total;

            god_ServiceTaxPayTmpUp.NetServiceTax    = 0;

            god_ServiceTaxPayTmpUp.ClosingBalance   = (god_ServiceTaxPayTmpUp.Additions + god_ServiceTaxPayTmpUp.OpeningBal) - god_ServiceTaxPayTmpUp.Total;

        }

        preClosingBal                               = god_ServiceTaxPayTmpUp.ClosingBalance;

        if((god_ServiceTaxPayTmpUp.DateOfPaym - god_ServiceTaxPayTmpUp.DueDate) > 0)

        {

            god_ServiceTaxPayTmpUp.Delay = god_ServiceTaxPayTmpUp.DateOfPaym - god_ServiceTaxPayTmpUp.DueDate;

        }

        else

        {

           god_ServiceTaxPayTmpUp.Delay  = 0;

        }

        god_ServiceTaxPayTmpUp.update();

        ttsCommit;

    }

}

private void updateServiceTaxPayTmp(GOD_ServiceTaxPayTmp _god_ServiceTaxTmp)

{

    if(_god_ServiceTaxTmp)

    {

        _god_ServiceTaxTmp.selectForUpdate(true);

        ttsBegin;

        if(taxTrans.Source == TaxModuleType::FreeTxtInvoice)

        {

            god_ServiceTaxTmp.TaxableIncome     += abs(taxTrans.SourceBaseAmountCur);

        }

        if(taxTrans.Source == TaxModuleType::Voucher)

        {

            god_ServiceTaxTmp.TaxableAdvance    += abs(taxTrans.SourceBaseAmountCur);

        }

        if(taxTrans.TaxPeriod == #ServiceTax)

        {

            _god_ServiceTaxTmp.ServiceTax       += abs(taxTrans.TaxAmount);

        }

        if(taxTrans.TaxPeriod == #VAT)

        {

            _god_ServiceTaxTmp.VAT              += abs(taxTrans.TaxAmount);

        }

        _god_ServiceTaxTmp.update();

        ttsCommit;

    }

}

///////////////////////
//////////////
class GOD_ServiceTaxPayUIBuilder extends SrsReportDataContractUIBuilder
{
    DialogField                     dialogvertical,dialogyear;
    boolean                         enable;
    GOD_ServiceTaxPayContract       contract;
}

public void build()
{
    contract            = this.dataContractObject();
    dialogvertical      = this.addDialogField(methodStr(GOD_ServiceTaxPayContract, parmVertical),contract);
    dialogyear          = this.addDialogField(methodStr(GOD_ServiceTaxPayContract, parmFromYear),contract);
    dialogvertical.value("");

}

public void getFromDialog()
{
    contract = this.dataContractObject();
    super();
}
public void postBuild()
{
    super();
    dialogvertical  = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(GOD_ServiceTaxPayContract, parmVertical));
    dialogyear      = this.bindInfo().getDialogField(this.dataContractObject(),methodStr(GOD_ServiceTaxPayContract, parmFromYear));
    dialogvertical.registerOverrideMethod(methodStr(FormStringControl, lookup), methodStr(GOD_ServiceTaxPayUIBuilder,verticalLookup), this);

}

private void verticalLookup(FormStringControl verticalLookup)
{
   #define.DimensionName("VERTICALS")
    Query                   query;
    QueryBuildDataSource    qbds,qbds1;
    SysTableLookup          sysTableLookup;
    DimensionAttribute      dimAttr;

    ;

    dimAttr = DimensionAttribute::findByName(#DimensionName);
    sysTableLookup  = SysTableLookup::newParameters(tablenum(DimensionFinancialTag),verticalLookup);

    sysTableLookup.addLookupfield(fieldnum(DimensionFinancialTag, Value));
    sysTableLookup.addLookupfield(fieldNum(DimensionFinancialTag,Description));

    query = new Query();
    qbds = query.addDataSource(tableNum(DimensionFinancialTag));
    qbds1 = qbds.addDataSource(tableNum(DimensionAttributeDirCategory));
    qbds1.addLink(fieldNum(DimensionFinancialTag,FinancialTagCategory),fieldnum(DimensionAttributeDirCategory,RecId));

    qbds1.addRange(fieldNum(DimensionAttributeDirCategory, DimensionAttribute)).value(queryvalue(dimAttr.recid));

    sysTableLookup.parmQuery(query);
    sysTableLookup.parmUseLookupValue(False);
    sysTableLookup.performFormLookup();

}

///////////////

In SSRS Show the Serial Number in Reports Automatically Using Expression in .NET

 Serial Number in SSRS Reports 

Purpose: I want to add the sequence number or serial number to every row.

Solution

To do this I will use an expression named "RowNumber".

1. Insert a new column on the left side and name it "Serial No."
   
   
   
2. Create the expression by right-clicking on that column as in the following:
   
   
   
3. Select the "Miscellaneous" as a category and "RowNumber" as an item, then expression will be like:
   
   =RowNumber(nothing)
   
   
   
   Where the nothing keyword indicates that the function will begin counting at the first row in the outermost data region. Click the "OK" button.
   
   
    
4. Run the "Default.aspx" page.
   
   

Menu item

if ( this.parmMenuItemDesc()=="CustInvoicePrintJob_FreeText")
    {
     return Dialog::newFormnameRunbase(formstr(CustInvoicePrintJob), this);
    }
    //17022017
   else if ( this.parmMenuItemDesc()=="GODCustInvoicePrintJobCopy")
    {
        return Dialog::newFormnameRunbase(formstr(GODCustInvoicePrintJobCopy), this);
    }
    //17022017
    else
    {

    return Dialog::newFormnameRunbase(formstr(GODCustInvoicePrintJob), this);
    }

ID change in Dynamics AX data dictionary

ID change in Dynamics AX data dictionary

The other day we upgraded to Cumulative Update 3 for Dynamics AX 2012. After that we got some problems in the SqlDictionary table - several table and field IDs did not much those in the AOT anymore.

One of our developers found this post, which contained a job fixing such issues. We had to correct the job a bit, otherwise it failed when trying to process Views or update field IDs that had been "swapped" during upgrade (e.g. before: fieldId1 = 6001, fieldId2 = 6002; after installing CU3: fieldId1 = 6002, fieldId2 = 6001).

This is the final version of the job. I know the change violates DRY principle, but for an ad-hoc job it is probably OK :)

static void fixTableAndFieldIds(Args _args)
{
    Dictionary dictionary = new Dictionary();
    SysDictTable dictTable;
    DictField dictField;
    TableId tableId;
    FieldId fieldId;
    SqlDictionary sqlDictionaryTable;
    SqlDictionary sqlDictionaryField;
 
    setPrefix("Update of data dictionary IDs");
    tableId = dictionary.tableNext(0);
    ttsbegin;
 
    while (tableId)
    {
        dictTable = new SysDictTable(tableId);
 
        setPrefix(dictTable.name());
 
        if (!dictTable.isSystemTable() && !dictTable.isView())
        {
            //Finds table in SqlDictionary by name in AOT, if ID was changed.
            //Empty field ID represents a table.
            select sqlDictionaryTable
                where sqlDictionaryTable.name == dictTable.name()
                && sqlDictionaryTable.fieldId == 0
                && sqlDictionaryTable.tabId != dictTable.id();
 
            if (sqlDictionaryTable)
            {
                info(dictTable.name());
                //Updates table ID in SqlDictionary
                if (ReleaseUpdateDB::changeTableId(
                    sqlDictionaryTable.tabId,
                    dictTable.id(),
                    dictTable.name()))
                {
                    info(strFmt("Table ID changed (%1 -> %2)", sqlDictionaryTable.tabId, dictTable.id()));
                }
            }
 
            fieldId = dictTable.fieldNext(0);
 
            //For all fields in table
            while (fieldId)
            {
                dictField = dictTable.fieldObject(fieldId);
 
                if (!dictField.isSystem())
                {
                    //Finds fields in SqlDictionary by name and compares IDs
                    select sqlDictionaryField
                        where sqlDictionaryField.tabId == dictTable.id()
                        && sqlDictionaryField.name == dictField.name()
                        && sqlDictionaryField.fieldId != 0
                        && sqlDictionaryField.fieldId != dictField.id();
 
                    if (sqlDictionaryField)
                    {
                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                            dictTable.id(),
                            sqlDictionaryField.fieldId,
                            -dictField.id(),
                            dictTable.name(),
                            dictField.name()))
                        {
                            info(strFmt("Pre-update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -dictField.id()));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
 
            fieldId = dictTable.fieldNext(0);
 
            //For all fields in table
            while (fieldId)
            {
                dictField = dictTable.fieldObject(fieldId);
 
                if (!dictField.isSystem())
                {
                    select sqlDictionaryField
                        where sqlDictionaryField.tabId == dictTable.id()
                        && sqlDictionaryField.name == dictField.name()
                        && sqlDictionaryField.fieldId < 0;
 
                    if (sqlDictionaryField)
                    {
                        //Updates field ID in SqlDictionary
                        if (ReleaseUpdateDB::changeFieldId(
                            dictTable.id(),
                            sqlDictionaryField.fieldId,
                            -sqlDictionaryField.fieldId,
                            dictTable.name(),
                            dictField.name()))
                        {
                            info(strFmt("Final update: Field %1 - ID changed (%2 -> %3)",
                                dictField.name(),
                                sqlDictionaryField.fieldId,
                                -sqlDictionaryField.fieldId));
                        }
                    }
                }
                fieldId = dictTable.fieldNext(fieldId);
            }
        }
        tableId = dictionary.tableNext(tableId);
    }
    ttscommit;
}