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.













Tuesday, June 25, 2013

AX 2012: Database ERDs on the AxErd Website

Thank to MSDN , they ran an awesome online tool in order to manipulate the table relations under AX 2012 with more than thousands tables. General speaking almost it is impossible to construct ERD (entity relationship diagrams) for every table; Every one who involved anytime with customizing or creating x++ in AOT in such a way now should be impressed by this service, like I did. You might need this tool if you did one of the following jobs during your development life style :
1. Manually looking up all the parent tables for a given table which has more child.
2. Technically using cross-reference with more iterations on every child table.
So now you can replace your action by just using this tool and save up to 50% on time and headache!
 Go to now:


Friday, June 21, 2013

x++ series: Where do call Super() ?

For everyone who has ever written a x++ code perhaps noticed to Super() method, but some times it should appear after your code and in another situation may leave it at top, before the code.

For who are new to Super() or confusing about that, let me say it means that "initialize me along with 'this' and alter the rest things". It means that everything you place before Super() will affect that initialization  and each code that placed after Super() just comes up to not alter the initialization but maybe need the objects to be ready to be used afterward or add some new functionality.

It will however more important when you are working with queries.
For example ff you are going to modify the query, by placing the code before the Super() you will make sure that query  is modified just before fetching the data;

The real world situation could be when you want to apply a filter to an existing data source on a form. In this case you have to override the executeQuery() method and place your SysQuery::findOrCreateRange(...) then put Super() at the last line (after your own code).

Thursday, June 20, 2013

x++ series: EDT (Extended Data Types)

One of important AOT objects is EDT. You have heard about Extended Data Types so far, at every job interview this is one of the questions, but no one could answer correctly nor interviewee knows the right answer and why should it is going to be asked! just MUST ask it.
Even worst, because EDT is going to be obsolete at next Microsoft Dynamics releases so developers assume they should ignore from using it.
But, actually EDT is more that these :
1) derived type.
2) Display a variable in user friendly.
3) Make it sense to relevant program's structure.
What is more?
By using EDT, you can establish a table relation which is no longer could be created from EDT node and totally moved to table node properties. It is recommended to convert all existing EDT by using EDT Relation Migration Tool in AX 2012
One of the most advantage of using EDT along with this tool is you can add a data field to a form with just a few steps with no need to define a data source relations manually then thanks to Field Groups just establish everything at once by adding this newly created field to the table's Group Field so having the relevant data in Form Design and that's it, you done!

Tuesday, December 11, 2012

'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine

You may get the error " 'Microsoft.ACE.OLEDB.12.0' provider is not registered" during importing the Excel file from SQL Serve Data Tools (in a business intelligence 2012 project) into an Analysis Service 2012 Tabular instance (However, you may also get this error while working with MS Access or MS Excel data source projects in other environment other than business intelligence projects).

Searching for this error over the net returns the guide to install Microsoft Access Database Engine 2010 Redistributable.
While it is true that you need the above modules in order to fix the error however there is no match to mention that if you do install the 32 bit version of "Microsoft.ACE.OLEDB.12.0" while working on the server which have already installed the Analysis Services in 64 bit mode but the MS Office 2010 in 32 bit at the same time there is no chance to escape from that mentioned error. Meanwhile, if you are going to install the 64 bit version of "Microsoft.ACE.OLEDB.12.0" you will be stuck because you have already installed MS Office 2010 32 bit(or other MS Office products in 32 bit mode).
(Note: As a rule, you won't be allowed to install the other MS Office 64 bit version products as long as you have (even one) the 32 bit of MS Office product, and vice versa).
Solution :
The only solution which I've tried in order to resolve the problem successfully is :
1. Remove/Uninstall all of the MS Office in 32 bit versions, including Project Server, SharePoint Designer, Visio, ... . (do not worry about the current configuration or the data inside Outlook or your records within OneNote, they will be back like a charm while installing the same version of MS Office regardless of 32 or 64 bit).
2. Do reboot your computer even if did not ask for it.
3. Installing the 64 bit version of MS Office 2010 (note that by default when you run the "setup.exe" inside the MS Office DVD it will start with the 32 bit version of setup, instead open "x64" folder and run setup.exe in order to install the 64 bit of MS Office).
4. Now check your outlook or open excel, then activate your product.
5. Install the 64 bit version of  "Microsoft.ACE.OLEDB.12.0"  (AccessDatabaseEngine_x64.exe).
5. Done. Now open the SQL Server Data Tools and continue to do import Excel file into the Tabular instance of the Analysis Service project.

  

Sunday, December 09, 2012

How to deal with Time Dimension in SQL Server 2008,2012 Business Intelligence

Here I'm not going to learn how to create a Time Dimension in business intelligence 2008 and 2012 as you may know it well or it could be found on the net, however there are some consideration in order to create the Time Dimension.
The key difference is that in one approach it will never show you the  Time Dimension during that wizard and you have to create it later by manual.

1. In first approach, we have already installed (or restored) our data mart database using SQL Server Management Studio, so we would create a cube at top of data mart. In this case we do the following steps briefly:
a. Using SSDT (in SQL Server 2012) or BIDS (in SQL Server 2008) to create a new Analysis Services project.
b. Create a new Data Source.
c. Create a new Data Source View.
d. Create a new Cube:
in this step while creating a new Cube, you asked to define the measures and dimensions except Time Dimension. Then we have to create our Time Dimension manually.

2. In second approach, we do not have the data mart yet, instead we are going to create our data mart using business intelligence tools. In this case we do the following steps briefly: 
a. Using SSDT (in SQL 2012) or BIDS (in SQL 2008) to create a new Analysis Services project.
b. Create a new cube:
in this step while creating a new Cube, you asked to define the Time Dimension along with the other measures and dimensions as well as at the end of the wizard you have to check the "Generate Schema" to create the data mart followed by the measures and dimensions which you have defined during that wizard as the schema. In this case we will asked to define a Data Source and Data Source View during the data mart creation (after click "Finish", it will open a new wizard page) and only if we did check the "Generate Schema".

As you may be noticed, in the second approach we followed the opposite direction in compare to the first approach, as well as we create the data mart on demand but asked for Time Dimension, in contrast at the second approach we have to create the Time Dimension manually but already have the data mart.

Share it