Thursday, May 15, 2014

AXAPTA: Converting between UTCDateTime and current date time (current timezone)

AX stores date/time within database in UTC format and it might be different form what you get in AX Forms (DateEdit form's controls are basically responsible to show date/time in user defined time zone), specially when you are in different timezone, developers however need to do conversion more often, here the journey just began. ...among those resources which are available over the net to study AX time zone conversion, most of them not working properly and some of them utilize Global .NET conversion method in order to convert between UTC and current timezone  - which is not useful when you have multiple company accounts in AX.
By using from DateTimeUtil::newDateTime() class, developers are happy to overcome time zone conversion, however it's not as much as easy and straight forward that you assume, so lets see how to treat newDateTime method in order to get a working result in terms of conversion between time zones :
In the code below if you try to put getCompanyTimeZone() in newDateTime method you'll get dateTime + companyTimeZone not the difference!
ie, assuming a companyTimeZone is in PST (-8UTC), it will then show you utcNow+8 not utcNow-8 !
Having said that typically this method turned out to create a new instance of date/time in desired time zone so it's not a conversion method.
I used to make a time in companyTimeZone first, then making a date  in companyTimeZone and ultimately going forward with newDateTime with no offset information in order to convert between time zones;
By the way be aware of using newDateTime method to compare between UTC date/time in your own code.

static void JobUTC2TimeZone(Args _args)
{
    utcDateTime dateTime;
    date dateInCompanyTimeZone;
    TimeOfDay timeInCompanyTimeZone;
    ;
    dateTime = DateTimeUtil::utcNow();
    timeInCompanyTimeZone = DateTimeUtil::time(DateTimeUtil::applyTimeZoneOffset(dateTime, DateTimeUtil::getCompanyTimeZone()));
    dateInCompanyTimeZone = DateTimeUtil::date(DateTimeUtil::applyTimeZoneOffset(dateTime, DateTimeUtil::getCompanyTimeZone()));
    dateTime = DateTimeUtil::newDateTime(dateInCompanyTimeZone, timeInCompanyTimeZone);
    info(strfmt(datetime2str(dateTime)));
}

SQL Server Reporting Services (SSRS): Parsing multi-value parameter values, obstacles

Recently it happened to me to manage a complicated SQL report out of AX. Regardless of the query, one of the big challenges was dealing with multi value parameter for SSRS. I faced a problem where I was unable to find an reasonable answer over the net either and the time was matter. All solutions are about to how to split or join the selected values of a multi value parameter and then pass it to SQL query or so on;Well, I'm not going to dig into Joint or Split functions in here. It's not so easy to make a range values to filter the report group or make a colored cell by the user discretion options when you do not know how many options are going to be selected and the order of the selections are important too. In this case you need to recognize the user selection first, but this time it's not as easy as a simple Joint to match against a dataset field. You need to pars each value, compute and then compare. So perhaps you need to use from either IIF or SWITCH to catch user selection and traverse between selected values to compute and make a range among them. The obstacles here comes up by an unknown error (#Error!) inside IIF where keep it from moving forward. In order to catch the user selection we need to check what the index of a selected option is :
Array.IndexOf(Parameters![NAME].Value, [VALUE])
It's the only way to use from the above expression within IIF or SWITCH when you want to check to see whether the Parameter has valid index for a given value and then proceed to fetch its value :
IIF(Array.IndexOf(Parameters![NAME].Value, [VALUE]),  
Parameter![NAME].Value(Array.IndexOf(Parameters![NAME].Value, [VALUE]), 
nothing)
however it raised an unknown error due to the index is out of range ( = -1)! Am I missed something? It seems IIF never checked for the condition and it went ahead to execute the expression instead! The answer is both right and wrong.Right because the expression evaluates before checking the condition and wrong because it was not actually executed , by the way we got that error! It's weird because if it's only an internal evaluation process then we shouldn't have got that error.
Indeed the reason is :
Conditional functions purposely evaluate the expression before they actually check the condition part for performance intensions, reported by Microsoft SQL developers team. So this is a bad news because sounds like you will never be able to guess the selected option and parse it using nested IIF.So I have got to figure it out to find out a workaround. I realized that there are two practical solutions to overcome this issue however I chose the second one :
1. Using from custom code to manipulate the values and as far as we're using from VB there is no obstacles to use from nested IF or Switch to compute the values. Downside is, per my MCITP in business intelligence and big companies job interview questions, the best practice suggests keep from using assembly or custom codes in reports as much as you can due to code promotion, upgrade and portability situations.
2. I created report Variables per Parameter values, have the variables to fetch the index of every prospective selection (it might include -1 as well which it means not selected):
=Array.IndexOf(Parameters![NAME].Value, [VALUE])
then by using the variables value I was able to :
1. Recognize the selected option/value.
2. Compare a dataset field against every selections that the user has made to see if it is a match
=switch(Variables![NAME].Value <> -1
switch(Fields![NAME].Value >= 0 AND Fields![NAME].Value < 100, "Green")

Conclusion:
Reminding that, considering this solution for a complicated circumstances where you do not know how many selections the user makes and you need to make a range from the selection(s) and ultimately compare every selection against the report data (not database or query) or whatsoever situations that you're being asked to compute multi-value parameter values, so this is the practical way to catch the selection without getting "Index out range" error or #Error when using nested IIF - to catch the index - due to the reason that how IIF does pre-evaluates the expression purposely for performance intentions.













There was an error in this gadget