Our support engineers have assembled the top recommended solutions for you.
Microsoft Dynamics AX 2012
Upgrading to Microsoft Dynamics AX 2012
Data Import, Export, and Migration
Microsoft Dynamics AX 2009
Application Object Server (AOS)
Enterprise Portal and Role Centers
SSRS and SSAS Integration
I'm installing my first AX 2012 (CU1) @ a customer site and stumbled on problem processing the OLAP cubes.
I've followed the error to it's source and appearantly it's a view that won't work ( Data convertion error from nvarchar to string)
In the view PRODCALCTRANSEXPANDED, there is fixed date (N'24/10/2011') that should be converted to DateTime.
First of all, because I'm on a European server with DD/MM/YYYY settings, it writes the date the same in T-SQL. But in T-SQL, it's always MM/DD/YYYY. This is a bug I presume. Setting my server to US dateformat,synschronising the view and the error is gone.
But then I tried to understand the logic of this view, the date is updated every time you do a synchronise of that view. Meaning if you don't synchronise very often and Prodstatus is not 5 or 7, you production-order is not delayed ( DELAYED = 0 ). Why doesn't they use GETDATE() or am I missing something here?
The same for view PRODROUTETRANSEXPANDED, same bug and some illogical use of a fixed date.
This is the piece of code that holds that fixed date in the first view.
CAST((CASE WHEN (((T2.PRODSTATUS) != (5)) AND ((T2.PRODSTATUS) != (7))) AND (T2.DLVDATE < N'24/10/2011') THEN 1 WHEN ((T2.PRODSTATUS = 5) OR (T2.PRODSTATUS = 7)) AND (T2.DLVDATE < T2.FINISHEDDATE) THEN 1 WHEN ((T2.PRODSTATUS = 5) OR (T2.PRODSTATUS = 7)) AND (T2.DLVDATE >= T2.FINISHEDDATE) THEN 0 ELSE NULL END) AS INT) AS DELAYED
T2 is PRODTABLE in this piece of T-SQL.
Both issues are fixed in AX2012 R2, namely in class method ProdTableExpandedColumn::delayed(), which is used by view ProdCalcTransExpanded. The code which resulted in hardcoding current date with date format derived from local machine:
was replaced with proper:
As the result, the view (after synchronization) contains:
CAST((CASE WHEN (((T2.PRODSTATUS) != (5)) AND ((T2.PRODSTATUS) != (7))) AND (T2.DLVDATE < GETUTCDATE())
Hope that helps.
Did you get a permanent solution to this issue.
We found this "synchronisation" date is also still used in the view created in the AX2012 R2 version.
Not sure why they hard coded a date in there, but maybe you can wrap that part with some additional SQL just to make sure the type remains a datetime (assuming DLVDATE is same type as TRANSDATE).
Depending on what version of SQL you are running, perhaps you can get inspired from here:
Or here: msdn.microsoft.com/.../ms187928.aspx (look at the first community comment at the bottom).
Tommy Skaue | Dynamics AX Developer from Norway | http://yetanotherdynamicsaxblog.blogspot.no/ | www.axdata.no
Other Microsoft Sites
I'm a Customer
I'm a Partner
Follow Microsoft Dynamics