Get time according to current time zone:
 -  in my case: Timezone::GMTPLUS0200ATHENS_BUCHAREST_ISTANBUL


     utcDateTime dateTime;


    dateTime = DateTimeUtil::applyTimeZoneOffset(DateTimeUtil::getSystemDateTime(),
                Timezone::GMTPLUS0200ATHENS_BUCHAREST_ISTANBUL);
   
    info(strFmt("Time %1", time2StrHMS(DateTimeUtil::time(dateTime))));

Response: Time 16:52:06

SSRS line and borders highlighting

Changing colors on grouped lines:  

Group lines according to Account value and change the color for the entire group.

=IIF(RunningValue(Fields!Account.Value, CountDistinct, Nothing) MOD 2 = 1, "WhiteSmoke", "White")


 Border style on groupings:

Top: =IIF(Fields!Account.Value = Previous(Fields!Account.Value), "None", "Solid")

-            So, when Account value changes, top line becomes gets a solid border style.

Bottom: =IIF(RowNumber("AccountLines_DS") = CountRows("AccountLines_DS"), "Solid", "None")

-            When we reach the last line, draw a solid border.


No grouping at all:


= IIF(RowNumber(Nothing) Mod 2, "WhiteSmoke", "White")



And something ..more like a personal note.

=FORMAT(Fields!TransDate.Value, "dd.MM.yyyy")

Get customer's party type

Starting from a LedgerJournalTrans line:

if (ledgerJournalTrans.AccountType == LedgerJournalACType::Cust)
        {
            if (CustTable::find(DimensionStorage::ledgerDimension2AccountNum(
ledgerJournalTrans.LedgerDimension)).partyType() == DirPartyType::Person)
              {
               ........
               }
        }

So, the partyType() method from CustTable will do the job.

Now here is how i did it in Sql:

--DirPerson 2975, DirOrganization 2978, CompanyInfo 41, OMOperatingUnit 2377, OMTeam 5329
SELECT CASE (SELECT dpt.INSTANCERELATIONTYPE
        FROM DIRPARTYTABLE AS dpt
        WHERE dpt.RECID = ct.PARTY)
            WHEN 2975 THEN 'Person'
            WHEN 2978 THEN 'Organization'
        END AS 'Party'
    , ct.AccountNum
FROM CUSTTABLE AS ct

and in order to get only the persons, just add a cte over it:


WITH parties
AS
(
    SELECT CASE (SELECT dpt.INSTANCERELATIONTYPE
            FROM DIRPARTYTABLE AS dpt
            WHERE dpt.RECID = ct.PARTY)
                WHEN 2975 THEN 'Person'
                WHEN 2978 THEN 'Organization'
            END AS 'Party'
        , ct.AccountNum
    FROM CUSTTABLE AS ct
)
SELECT *
FROM parties p
WHERE p.Party = 'Person'

SSRS - Hide tablix column when all rows are empty

First of all, don't use Hidden property but right click on tablix header, choose Column Visibility

and below "Show or hide based on an expression" write down your expression.

As an example:

 =IIF(Max(Fields!OffsetDimension.Value, "LedgerTransStatementDS")= "", true, false)

So, if maximum of all field values is equal to an empty string, it means we have absolutely no values in this column.

If you would add this expression to the Hidden property then you will end up with a gap in your table.

Check if string enum element is part of real enum.

When importing lines from a .csv file, at some point, i have to check if the account type is correctly provided.

Here is the method for this and a call example :

//Example: enumElementIsValid("Vendor", "LedgerJournalACType");
private boolean enumElementIsValid(str _inputEnumElement, str _inputEnumType)
{
    boolean isValid = false;

    DictEnum enum = new DictEnum(enumName2Id(_inputEnumType));

    int i;
    for (i = 0; i < enum.values(); i++)
    {
        //check if input account equals one of the labels.
        if (_inputEnumElement == SysLabel::labelId2String2(enum.index2LabelId(i), 'en-za') ||
                _inputEnumElement == SysLabel::labelId2String2(enum.index2LabelId(i), 'en-us'))
        {
            isValid = true;
        }
    }
    return isValid;
}


So, i am sending the enum name for this task, and its element as it was read from the .csv file.

I've chosen to compare the string value from the file with the label of the enum element because that's what the users see and not the actual name. And I've done this in two languages.

PurchLine validation

If you need to add constraints on PurchLine s, have a look at :

 

As an example, there is one default behavior for return items:

Check PurchLineType_ReturnItem

    -> validateWrite()

    if (purchLine.PurchQty > 0
    &&  purchLine.SkipUpdate == InterCompanySkipUpdate::No)
        ok = checkFailed("@SYS53512");

the label saying : "Quantity of returned items orders must be negative."

SSRS report - division issue

I've just found out an interesting thing about divisions in an SSRS report. Nothing fancy but you have to pay attention:

My expression is something like this:

  =IIF(Parameters!ReportType.Value="UsedMerchandiseInStore",
     (1.0 * Fields!inventTransCostValue_RO.Value)\(1.0 * Fields!Qty.Value),
     Fields!CostPrice.Value)

I admit: i was desperate and added those * 1.0 but that didn't solved it.

Instead, i was a lucky wanderer and read Paul Miner's answer on this post:

http://arstechnica.com/civis/viewtopic.php?t=168308

This is so nice, how a few words can help over the years.

 So i reiterate his suggestion, to change the "\" to "/". It seems that the former is integer division.

That did it.

Display formatted date if exists in SSRS report, or nothing

    First, of all, i set Allow Blank true and Nullable true for my parameters. In case the user chooses to avoid providing them, just display all the lines, without ranges. 

I am not going to post all the code from my report, but at some point in the processReport phase i set the dates range as :

 
     if (fromDate && toDate)
    {
        qbr = qbdsCT.addRange(fieldNum(CustTrans, TransDate));
        qbr.value(queryRange(fromDate, toDate));
    }

where qbdsCT is my QueryBuildDataSource object.



And finally in the SSRS i use this :

 =IIF(Parameters!CustTrans_DS_FromDate.Value is nothing, nothing,         FormatDateTime(Parameters!CustTrans_DS_FromDate.Value, 2))

Joining with EcoResProductTranslation

Pay attention when joining with this table. It will multiply your rows because it keeps Description and Name for your products in as many languages as you have defined in your system.

In my case, i had to join InventTable with it, in order to get the name of the product. This is the join relation:

And i have restricted the language to only the current one:

 

Set the range on the EcoResProductTranslation table and choose the value of LanguageId as follows:

 

Check if customer is a vendor also

I've encountered this request, to find out if one of our vendors is a client also.

How to check that? Well, there is a field on CustTable named VendAccount. This is the starting point.

 To check all of the existing pairs I've tried this:

T-SQL:

SELECT ct.ACCOUNTNUM
    , ct.VENDACCOUNT
FROM CUSTTABLE AS ct
WHERE EXISTS (SELECT 1
                FROM VENDTABLE
                    WHERE ACCOUNTNUM = ct.VENDACCOUNT)

And in X++:

static void getCustVendAccount(Args _args)
{
    CustTable   custTable;
    VendTable   vendTable;

    while select AccountNum, VendAccount from custTable
        exists join vendTable
        where custTable.VendAccount == vendTable.AccountNum
    {
        info(strFmt("Custmer Id: %1 - Vendor Id: %2", custTable.AccountNum, custTable.VendAccount));
    }
}

For both of them i had to compute the balance. 

Using the above X++ code i wrote:

TmpCustVendTrans tmpCustVendTrans;
;

tmpCustVendTrans = TmpCustVendTrans::custTransBalanceCurrency(custTable.AccountNum);

and for vendor:

tmpCustVendTrans = TmpCustVendTrans::custTransBalanceCurrency(custTable.VendAccount);

then just read the tmpCustVendTrans.AmountCur.