- Views in AX
- View methods
- Advanced Query range
- Reverse engineering the SQL behind the views
Starting with the view that did not return all the expected data, we have the SalesOrderCube View.
Now the Query in itself isn't all that fascinating, but I wasn't aware that you could add ranges across datasources like done in this Query. That is pretty handy!
Notice how the Query uses another View as DataSource. There are plenty of examples of Views and Queries being nested, and this is a powerful way to create results from a rather complex ERP data model.
Notice also there is a custom Range on ValidFrom and ValidTo. The Ranges compare the Dates from ExchangeRateEffectiveView with the CreatedDateTime from SalesTable.
If we look at the definition of the ExchangeRateEffectiveView we see that ValidTo is a field of type Date. Furthermore we see the field is coming from a View Method. But we know CreatedDateTime on SalesTable is a DateTime.
How can it compare a Date with a DateTime? The answer is that actual date is stored in the database as a datetime where the time part is 00:00:00.000.
How can it compare a Date with a DateTime? The answer is that actual date is stored in the database as a datetime where the time part is 00:00:00.000.
So it compares the values and that works like charm.
The problem
What happens if you have daily exchange rates in your system? Then your ValidFrom and ValidTo becomes the exact same date and more importantly the same time. This will not work correctly since CreatedDateTime also keeps track of what time on the day a Sales Order was created.
So let's look at one specific example where we have rates for the 9th of December 2014.
And if we run the Sales Order Cube view, and modify the selected columns so we can see the problem, we will notice that the query is unable to collect the rates. The values from Exchange Rates is NULL.
The Solution
There are probably many ways to solve this, but the solution I went for was to make sure that the ValidTo always returns the time part at the max value, which is 23:59.59.000 (AX doesn't operate on the milliseconds, as far as I know).
So compare the results coming from the ExchangeRateEffectiveView before I apply the change.
By doing some small changes to the validTo View method, I can give the time part a better value.
And the result is that the Sales Order Cube now has the Cross Rates as expected.
I hope you enjoyed this post. I sure enjoyed solving this challenge.
Here is the method body (ExchangeRateEffectiveView.validTo):
private static server str validTo(int _branchNum)
{
str returnString, fieldString, fieldString2, fieldString3;
boolean generateCode = false;
DictView dictView;
// Axdata.Skaue.04.03.2015
// Fix ValidTo with proper time 00:00:00.000 -> 23.59.59.000 ->
str adFixValidToString = 'DateAdd(ss,-1,DateAdd(d,1,%1))';
// Axdata.Skaue.04.03.2015 <-
dictView = new DictView(tableNum(ExchangeRateEffectiveView));
switch (_branchNum)
{
case 1:
returnString = dictView.computedColumnString('VarToVarBefore', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;
case 2:
fieldString = dictView.computedColumnString('DenToVarEuroBefore', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToVarEuroBefore', 'FixedStartDate1', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;
case 3:
fieldString = dictView.computedColumnString('DenToDenBefore', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenBefore', 'FixedStartDate1', FieldNameGenerationMode::FieldList, true);
fieldString3 = dictView.computedColumnString('DenToDenBefore', 'FixedStartDate2', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
fieldString3 = strFmt(adFixValidToString, fieldString3);
// Axdata.Skaue.04.03.2015 <-
returnString = 'CASE when ' + fieldString + ' <= ' + fieldString2 +
' and ' + fieldString + ' <= ' + fieldString3 + ' then ' + fieldString +
' when ' + fieldString2 + ' <= ' + fieldString3 + ' then ' + fieldString2 + ' - 1 ' +
' else ' + fieldString3 + ' - 1 end';
break;
case 4:
returnString = dictView.computedColumnString('SameFromTo', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;
case 5:
returnString = '\'21541231\'';
break;
case 6:
returnString = '\'21541231\'';
break;
case 7:
returnString = dictView.computedColumnString('DenToVarAfter', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;
case 8:
returnString = dictView.computedColumnString('DenToVarAfterRecipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;
case 9:
returnString = dictView.computedColumnString('VarToDenAfter', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;
case 10:
returnString = dictView.computedColumnString('VarToDenAfterRecipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
returnString = strFmt(adFixValidToString, returnString); // Axdata.Skaue.04.03.2015
break;
case 11:
returnString = '\'21541231\'';
break;
case 12:
fieldString = dictView.computedColumnString('DenToDenAfterStart1', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenAfterStart1', 'StartDate2', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;
case 13:
fieldString = dictView.computedColumnString('DenToDenAfterStart1Recipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenAfterStart1Recipical', 'StartDate2', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;
case 14:
fieldString = dictView.computedColumnString('DenToDenAfterStart2', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenAfterStart2', 'StartDate1', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;
case 15:
fieldString = dictView.computedColumnString('DenToDenAfterStart2Recipical', 'ValidTo', FieldNameGenerationMode::FieldList, true);
fieldString2 = dictView.computedColumnString('DenToDenAfterStart2Recipical', 'StartDate1', FieldNameGenerationMode::FieldList, true);
// Axdata.Skaue.04.03.2015 ->
fieldString = strFmt(adFixValidToString, fieldString);
fieldString2 = strFmt(adFixValidToString, fieldString2);
// Axdata.Skaue.04.03.2015 <-
generateCode = true;
break;
}
if (generateCode)
{
returnString = 'CASE when ' + fieldString + ' <= ' + fieldString2 + ' then ' + fieldString +
' else ' + fieldString2 + ' - 1 end';
}
return returnString;
}
