'Classify as tangible product'! What the heck does that mean?

It’s bugged me for a while, but only recently bugged me enough for me to try to find out what it does.

I’m talking about the Classify as tangible product parameter that you can set on categories in a sales or procurement category:

ClassifyAsTangibleProduct

What little documentation I can find for it is, I now believe, misleading. Perhaps more amazing is that there is nothing written about this parameter in any place that I can get Google (or Bing) to find.

What follows is a description of the functionality that I think is linked to this parameter. As I am not lucky enough to have access to Visual Studio, it’s based on what I could find with a trawl through an AX 2012 R3 AOT. Perhaps I missed something (likely), and perhaps something has changed in F&O, but this has to be better than nothing.

As far as I can tell, this parameter affects only interactions with Sales orders and Purchase orders. I assume we are all familiar with how we can create Return (sales) orders, and how we can use Registration and Pick on order lines for normal Items. You may never have tried to do those things for an order line which is for a Category, but if you did, you might find that you can’t add a Category to a Return order:

and you would see that the Update line -> Pick and Registration options are unavailable:

However, if the Category has Classify as tangible product set, then you are able to perform Pick and Registration, and add the Category to a Return sales order line.

Here I am performing Registration for the Category Cars (in the Contoso dataset):

The Contoso dataset has no Sales category which is Classified as a tangible product, but I have set the Television category so, and find I am able to add it to a Return order line:

This, I believe, is the full extent of the functionality linked to the Classify as tangible product parameter. If you know different, please let me know.

An item can exist in multiple counting journals

New functionality that allows an Item to exist in multiple count journals.

It might have been a long while since I last blogged, but it’s been even longer since I had to fight with counting journals in Dynamics AX. Never the less, I spotted something count related in the December 2017 release of R3 that piqued my interest.

KB_4057006

This hotfix is related to count journal functionality in the inventory module, not Cycle count work in the Warehouse module. And the last time I used count journal functionality in anger, I encountered two commonly known limitations:

  • An item can only exist in one open count journal*
  • A single count journal can only be opened by one user
* stictly speaking, you can count the same item in different journals, but only if you are counting it in different warehouses.

These two limitations pose quite a problem when counting a warehouse that contains several thousand locations. You’d want to create multiple journals (perhaps one per warehouse aisle or area) so that you can spread the data entry burden across multiple people, but you can’t do that if there’s a chance that some items might appear in different journals. In fact, unless you modified the behaviour, you ended up with one very large count journal, and dozens of count teams standing around whilst the poor system administrator punched in all the counted quantities.

I took a look in a test environment to see if this hotfix offers a solution to this problem….

The new functionality surfaces through a new Counting status registration policy parameter on the Journal name setup form:

Journal name setup form

The help text for the new field is long, and reads: Use this setting to control if counting should be registered as started for items on the journal lines. When enabled, an item with identical dimension values can only be included within the same counting journal. If disabled, counting will not be registered as started for warehouse items and an item with the same set of inventory dimension values can be included on any number of counting journals.

So the new functionality is active when the Counting status registration policy is set to Disabled. And after adding an item to an appropriately configured count journal, I find that the Counting started parameter on the Warehouse items form does not become ticked:

WarehouseItems

If I create another count journal, I am able to add the same item, and both journals can be posted without error. Certainly it solves the problematic scenario I described above.

Of course, you do have to be careful not to make the same count adjustment in multiple journals. For example, in Location 11, I have found one more of the item than AX expected.

Count

If I post this result in both journals, AX actually increases the On-hand quantity from 1.00 to 3.00!

On-hand

Despite this, I’d rather have the option to make that mistake than be forced to do the whole count in a single journal.

Note that I tested this in an environment that also included hotfix KB4093686, which is included in the February 2018 release of R3:

KB_4093686

Links to both hotfixes on LCS: KB4057006 and KB4093686

Changing Reservation hierarchy when tracking dimensions are involved

A review of KB4022327, which allows the Tracking Dimension Group and Reservation Hierarchy of an item to be changed.

The Reservation hierarchy in Dynamics AX is the thing that enables two-level inventory reservation. Essentially, we get to choose if we want to reserve inventory at Batch and/or Serial level, or not. Once set, changing a Reservation hierarchy has been particularly difficult, especially because often this is being done in combination with a Tracking Dimension Group change.

I knew for an upcoming project that I was going to need to change Items that were set as Batch-only to become Batch & Serial enabled. I had already dabbled with it, and found that you hit a catch-22; you can’t set a serial-enabled Tracking Dimension Group if your item has a Reservation hierarchy which does not have Serial in it…. and, well, the periodic job that exists for changing an item’s Reservation hierarchy did not seem particularly interested to do that for item’s which already have a Reservation hierarchy. (Rather, it worked for items which did not. This might be fine if you’re enabling warehouse management functionality in an environment for the first time, but was no good for me).

It was whilst reviewing a list of recently released hotfixes (as I’m sure you all do) that I spotted KB4022327: ‘You cannot change Tracking dimension group (and Reservation hierarchy) for WHS enabled items’. It’s like it was written for me; how did they know! Although it was released in May, I only just got round to checking it out.

After installing it, I rushed to change the Tracking dimension group of one of my items, and find I am presented with an updated error message:

'use the Change tracking dimension group for items periodic task'
‘You can change the tracking dimension group and reservation hierarchy simultaneously by using the ‘Change tracking dimension group for items’ periodic task’

Change both the Tracking Dimension Group and the Reservation hierarchy at the same time? You spoil me! But where is this periodic task? It’s been added in the Warehouse module (alongside the ‘Change item hierarchy’ job), and you’ll now also find both of these jobs in the Product Information Management module:

The 'Change tracking dimension group for items' periodic job.
The ‘Change tracking dimension group for items’ periodic job.

Opening the job reveals this shiny new dialogue:

ChgTrkDimGrpItm

There are some things that you need to do in preparation for running this job. Things that you would have to do anyway when you change a significant property of a Tracking Dimension Group….

  • You need to make sure that the Products (which are linked to your Released products) do not have a Tracking Dimension Group set. If they do, you can just remove it from the Product.
  • You’re going to need to remove all inventory for the items. After doing that, you might think about doing an Inventory close (and therefore, when you do this for real, you might need to do it at a period end).
  • The job will error if you have any open reservations. When you removed all inventory of the items, you would needed to remove all reservations anyway.
  • The job will error if you have any open receipt transactions. It’s odd that I’m allowed to make this change if I have open sales orders, but not if I have open purchase orders. The very first link I posted at the top of this post says, in relation to changing a Reservation hierarchy, ‘all items must be purchased or sold’. If you decide to leave open sales orders, I suggest that you test that they can still be released/picked afterwards.

After doing these things, the job worked for me on my test items. One less headache to worry about.

Cancel ‘Product receipt’ and ‘Packing slip’ in WHS Scenarios

Functionality to Cancel Packing Slips and Product Receipts has been introduced to AX 2012 R3 and Dynamics 365 for Operations

AX 2012 introduced functionality to allow amendments to or cancellations of posted Sales order Packing slips (Delivery notes) and Purchase order Product receipts.

Correct / Cancel a posted Packing slip
Correct / Cancel a posted Packing slip

The functionality was an improvement over the solution available in AX 2009 and earlier, which was to create an order line for an equal but opposite quantity and post that on another packing slip, effectively reversing the earlier posting. As if to to confirm the that new functionality was the correct way to handle adjustments to posted packing slips and product receipts, the ability to reverse the posting using the AX 2009 method was removed in AX 2012.

This was all fine, until the release of AX 2012 R3 and the Warehouse management module. The functionality to Cancel or Correct Packing slips and Product receipts did not work well in scenarios which involved Warehouse management processes (with Loads, Shipments and the like), and with the release of CU9 the functionality was actually prohibited in these cases.

Unable to Cancel/Correct Product receipts and Packing slips in WHS scenarios
Unable to Cancel/Correct Product receipts and Packing slips in WHS scenarios

The good news is that Microsoft have reintroduced the functionality to WHS scenarios with CU12 – and you can get it now via hotfix KB3196554. I installed it into my R3 CU11 demo VM so that I could take a look.

It wasn’t immediately obvious how I could take advantage of the new functionality, so I extracted the contents of a model which had ‘KB3196554’ in the name:

Contents of the KB3196554 model
Contents of the KB3196554 model

The changes to the WHSLoadTable form looked the most promising; three new buttons on the ‘Ship and receive’ tab on the ribbon bar of the Load:

The 'Reverse' area on the 'Ship and receive' tab of the 'All loads' list page
The ‘Reverse’ area on the ‘Ship and receive’ tab of the ‘All loads’ list page

A maximum of one of these buttons will be available at any time, depending on which action is appropriate for the direction and status of the Load which you have highlighted. And you’ll also notice that it is now possible to Reverse the Confirmation of a Sales order Shipment (although it is still not possible to do that for a Shipment linked to a Transfer order).

After a quick look at the functionality, it seems that if you want to cancel a Sales order packing slip, you must do it from the Load; which is fine, because all Sales orders have Loads (even if you aren’t particularly using the Load functionality). However, you can also cancel a Purchase order Product receipt from the Product receipt journal, which is good, because the use of a Load is optional for Purchase order receipt.

The functionality in this R3 hotfix is also included in the November 2016 update for Dynamics 365 for Operations.

 

GRNI (Accrued purchases) Report

In the U.K., a report to show Goods Received Not Invoiced (GRNI) seems to be a common requirement. Dynamics AX 2012 has an Accrued purchases report which fits the bill, but unfortunately this report has been enabled only for legal entities operating in the USA and Canada. As a workaround, I have been known to suggest using appropriate filtering on the the inventory transactions table, but this did not stop at least three projects I have worked on requesting that we enable the real Accrued purchases report.

Microsoft have provided a short example of how it is possible to remove the regional restriction on this report, but I recall the date field on the dialogue box also needed modifying to recognise dates in the UK format, so I think that this example could be a bit lacking.

Luckily, all of these problems are resolved by a hotfix that was released at the end of April for Dynamics AX 2012 R3. The hotfix is KB 3160455 and is titled ‘Accrued purchase report can be available for all countries’. I installed it into my demo VM so I could take a look at it.

The country/region restriction on the report is now completely removed, and I also see that it has a new name – ‘Accrued purchases excluding sales tax report’:

The menu item of the Accrued purchases report
The menu item of the Accrued purchases report

Unfortunately, ‘sales tax’ is normally translated to ‘VAT’ in the en-gb language, but if the report works, I think I’ll forgive that. The appearance of the report’s dialogue box has not changed, and offers the option to exclude receipts which have not been recorded in the ledger:

Accrued purchases report dialogue box
The dialogue box of the Accrued purchases report

It seems to be happy with the UK formatted date that I give it. The output itself looks like this:

Accrued purchases report
The Accrued purchases report

And at the bottom of the last page, there is a grand total.

Comparing it to the output from the report in an R3 CU8 system that I have to hand, it seems that one column ‘Value of line of PO’ has been removed, but otherwise the output is the same.

How would you like to double the number of DIXF entities you have?

A colleague pointed me to a hotfix that will be released in AX 2012 R3 CU10. The hotfix is KB3061216 and is called ‘Support for new DIXF entities’. The hotfix description lists fourteen AX modules and shows the number of new entities in each module; in total 226 new entities. But it doesn’t tell you what the new entities are. I only knew one way to find out, so I installed it into my R3 CU9 Demo VM.

Since I wanted to know which entities were new, first, I dumped a list of the entities that I had in CU9 into Excel; 238 in total. And then I installed KB3061216. And after completing the software update checklist, I rushed to the ‘Target entities’ form and hit the Export to Excel shortcut. And I still had 238 entities. Hmm. Not what I was expecting. Perhaps the list needs refreshing. I looked around for an option to do that, but couldn’t find one. What if I delete them all, and then re-open the Target entities form? Worth a try – and that did it. Now I have 467 entities.

That’s a whole 229 new entities. Actually, I did some comparisons and found that two of the entities in CU9 had disappeared – ‘VendPaymFee’ (Payment fee in the A/P module) and ‘Terminals’ from the Retail module. So that’s 231 new entities. What are they all? To save you the bother of installing the hotfix to find out, I’ve attached an Excel list of all 467 entities.

Entities KB3061216

In the list, the lines in green are entities that were added by the hotfix. I don’t know if any existing entities have been extended – I took a look at the Product entity and it appeared that it had not been. Let me know in the comments below if any entities you’ve really been waiting for have been added.

Power BI comes of age

Earlier in the year, I took a look at Power BI, and tried to find out what it could do with AX data. My ambition was to use Power BI’s natural language search to build reports on AX data. Natural language search (called Q&A in Power BI) allows you to create reports by typing english sentences – such as ‘Sales by Month’.

In the end it didn’t go quite as well as I’d hoped. I couldn’t get the PowerBI.com website to look at my on premise SQL database, and I couldn’t get it to look at the standard AX Cubes. But the post did end on an optimistic note – support for the functionality I was looking for was either in development or being planned.

Since then, Power BI has come out of Beta. And it has had lots of updates; specifically, the Power BI Personal Gateway, which allows you to connect PowerBI.com to your on-premise SQL database. (I still can’t connect to the AX cubes – that work is not yet finished). So with my AX 2012 R3 CU9 Demo VM, I thought I’d take a look at it in action.

The link above describes how to install the Power BI Personal Gateway, but the first thing I notice is that the Personal Gateway is only available if you have a Power BI Pro subscription. This cost £9.99/month, and will be required for whoever create the reports, and anyone who views them. Its not loads of money, but I don’t particularly want to spend anything for this exercise. Luckily, a free 60-day trial of Power BI Pro is available, so I’ll go for that. To download the Personal Gateway, I login to PowerBI.com and click the Downloads icon in the top right:

DownloadPowerBIPersonalGateway
Download the Power BI Personal Gateway

There are some limitations about where you can install the Personal Gateway. It must be on a 64bit Windows operating system, and you’ll probably want to install it on a device which is left powered on (at least, in real use you will. For this test, I installed it into the CU9 Demo VM). Whilst running the install, its worth being signed in as a user with Windows Administrator permissions. If you do this, the Personal Gateway will install as a Windows Service, and will run even if no-one is signed onto the computer.

The install is an easy ‘Next’, ‘Next’, ‘Next’, and when its finished you’re asked to sign-in to PowerBI.com. Use the same login that you used when you downloaded the Personal Gateway, and if you get it right you’ll be asked to provide the windows credentials that the Personal Gateway will use:

ProvideWindowsCredentials
Provide the details of a windows (or domain) account

When it’s complete, I’m asked to sign-in to PowerBI.com to configure my datasources. We can’t simply go to PowerBI.com and ask to connect to our on premise database. First, we have to create a report which uses that database, and upload it to PowerBI.com. I’ll use Power BI Desktop for this. You can download it from the same place that you downloaded the Personal Gateway. In Power BI Desktop, SQL Server is listed as one of the data sources:

GetDataSQLServer
The SQL Server data source

I enter the details which allow Power BI Designer to find my SQL database:

SQLDatabase
Enter the location of the SQL database

At the next screen, I have to say if I want to use Windows login or SQL login to access the database, and then I’m presented with all the tables (and views) in my AX database. Since I know the views CustTableCube and CustInvoiceTransExpanded are used by the AX cubes, and provides a nice set of data for reporting, I’ve used those to create this report which shows Invoice value by Zip Code in the USMF company:

SalesByZipCode
A map showing sales by zip code

When I’m done, I publish the report to PowerBI.com. And when I head to PowerBI.com, I see I’ve a new Dataset. If I click the three dots next to the Dataset, I’m able to Refresh it, or Schedule a refresh:

RefreshDataset
Refresh a dataset

When I attempt a Refresh, I am informed that I need Power BI Pro, and asked if I want to create a trial:

PowerBIProRequired
Power BI Pro is required

And the first time I refresh, I have to confirm that I want to use Windows authentication:

ConfirmAuthentication
Confirm authentication

And after that, my data can refresh. I also have the option to schedule a refresh (up to eight times per day), and as long as my Personal Gateway is running, the data will refresh for me. Result!

But can I use natural language to create a report? Sure I can:

NaturalLanguageReport
A report created by Power BI Q&A

The report above was created by typing ‘Show Value by Customer in a column chart’. I had to add ‘in a column chart’, because it wanted to use a horizontal bar chart, and I didn’t like it. All they need to do now is integrate Cortana, and I’ll be in seventh heaven:

“Cortana, show me contribution by customer group”

And that is not a dream – work has already started. You can even see it demonstrated by Joe Belfiore in the Ignite 2015 Keynote (skip forward to 00:45:30).

This is all very nice, but I live in the AX world. Why should I be bothered? I’ll leave you with one more thought. If you look hard enough, you’ll see news is also coming out about tighter integration between Power BI and the yet-to-be-release AX 7, and that’s enough to keep me interested.