Whats happening with my Procurement and Sourcing reports!?

On some installations of Dynamics AX 2012, reports that should be summarizing the data by country or region do not display the data correctly. You will notice this when displaying reports in the Procurement and Sourcing module, as well as reports in other modules. These reports are based off the standard Dynamics AX 2012 OLAP cubes. Specifically, the “Purchase cube” is used for these reports. The vendor dimension in this cube is based off of the “VendTableCube” view – this is where the problem exists. To see an example of the problem, go to Procurement and Sourcing, Reports, Statistics, Spend Analysis, and run the Spend by country/region report. In the report below, you will see that the report is not summarized by country/region as it should, and there is only a single line for the total.

Why?

As mentioned above, this report, and several others in the Procurement and Sourcing module, is built off of the VendTableCube view. If you were to use the table browser to look at this view, you can see that none of the address information (City, CountryRegion, County, etc.) is populated. The reports that have this issue are generated from the Purchase or Sales cubes. These cubes are built on queries that join in the LogisticsPostalAddress table, which is a date effective table. If you are not familiar with date effective tables, see the explanation in the TechNet white paper here: https://technet.microsoft.com/en-us/library/hh335187.aspx. To see the root cause of the issue, in the developer’s workspace, open the AOT and navigate to Queries, VendTableCube, and drill down to the range on the ValidTo field as shown below. The query is intending to join rows from the DirPartyPostalAddressView view that are in effect and have not expired. The range that is specified is “12/31/2006 12:00:00 am” to “12/31/2006 11:59:59 pm” for the ValidTo field. The correct value of a date to use for a date effective row that has not expired is “12/31/2154”, so the query as written would only return rows that expired on 12/31/2006.

How to fix it

To fix this problem, update the range for ValidTo to “12/31/2154 12:00:00 am” .. “12/31/2154 11:59:59 am”. This will include any rows from the DirPartyPostalAddressView that have not yet expired. In Dynamics 365 for Finance and Operations, the OLAP cubes have been replaced by Power BI reports. If you’re looking for more information about those, click here!