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.

Pimp your AX demo VM

Recently, Microsoft released an updated Demonstration VM for Dynamics AX 2012 R3 CU9. You can find it here on PartnerSource, and here on CustomerSource. For the last VM (CU8), I converted both the Virtual Hard Disk files (VHDs) and Virtual Machine Generation to the latest version supported by Hyper-V. I also made some other tweaks which might help with performance. Doing it again now for the CU9 VM, I thought I’d write down the steps. I’ve put them here for everyone to read, but this is a really long post, and gets quite technical (in a non-AX way) in places. If this doesn’t sound like something you want to do, I’d stop reading now. It might be that I’m the only person who finds this interesting or useful. If that’s the case, at least I’ve got it written down for next time!

The download for the Demo VM is spread over 38 files (each 1GB in size), so go ahead and download those, and come back when you’re finished.

DownloadtheVMImage
Downloading the VM using File Transfer Manager

Once the downloads are complete, you need to make sure all the files you’ve downloaded are in the same folder, and run the executable:

RunTheExecutable
Run the executable file

Although it opens an InstallShield Wizard, all it really does is unpack one of the 38 files you downloaded (and display a licence agreement, which I assume is the real reason for this step!). It offers to ‘Install’ it into C:\Program Files. I would change it to the location which you downloaded the 38 files to. And once its done, you’ll have two executables in your folder. Now, run the one with ‘part001’ in the name:

UncompressTheFiles
Run the second executable to uncompress the files

It will trigger the unpacking of the 38 files, one by one. You can set the same folder you’ve used before as the destination for this step. Be aware that whereever you do it, you need nearly 110GB of free space. It will take a long time to complete – almost as long as it took me to download the 38 files in the first place.

When it’s done, you’ll have a word document, and a folder containing the VHDs and configuration of the Virtual Machine.

TheVHDsAreExtracted
The unpacked files

The VHDs are the ‘virtual hard disk’ files used by the VM. They are the VM’s C: and E: drives. C: contains Windows and the applications. E: contains the SQL database files.

At this point, you can delete the file you downloaded called ‘AX2012R3CU9DemoVMA.exe’. You’ll not need it again. You can also delete all of the files with the word ‘part’ in the name. Or, if you think you’ll need them again (maybe you’ll kill this instance of the VM one day, and want to start again), I normally copy them off to an external drive. Either way, we don’t need them now.

Before we boot it up, we’re going to convert the VHD files to the VHDX format. After we’ve booted it up (and made an important change within the VM) we’re going to convert the VM to Generation 2. If you’re hoping to squeeze better performance from your VM because of this, there is a crumb of hope for you. The VHDX format includes support for something called ‘4K logical sectors’. This is something that is only supported in the latest releases of Windows, and offers the potential for a performance increase.

I’m going to use a command line tool to convert the VHDs, and to make it easier, I’m going to move the whole of the AX2012R3A folder to the root of a drive, in this case my C: drive:

MoveToRoot
The files are moved to C:\

The tool we’ll use is called Convert-VHD, and you need to open PowerShell to run it:

OpenPowerShell
PowerShell from the Start Menu

The command I will use is:

Convert-VHD -Path "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_OS.vhd" -DestinationPath "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_OS.vhdx"

If you did not put your ‘AX2012R3A’ folder into the root of the C: drive, you’ll need to modify this. This step will require around 115GB free for the new VHDX files that get created. I have room for them on my C: drive, so I created them in the same place as my VHD files. If you don’t have room, modify the DestinationPath to create them on a different drive.

Put the command into the PowerShell window (hint – Paste is available by clicking the blue PowerShell icon at the top left of the window) and hit Enter:

PowerShell
Paste the command into the PowerShell window

The conversion will begin:

Converting
Converting the VHD

There is another command I will run, to set the sectors on the disk to the magical 4K size. The command is below. Just put it into the PowerShell window after conversion is complete:

Set-VHD -Path "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_OS.vhdx" -PhysicalSectorSizeBytes 4096

Then repeat for the second VHD. The commands are:

Convert-VHD -Path "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_Data.vhd" -DestinationPath "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_Data.vhdx"

and

Set-VHD -Path "C:\AX2012R3A\Virtual Hard Disks\AX2012R3A_Data.vhdx" -PhysicalSectorSizeBytes 4096

You can now delete the old VHD files, but leave the VHDX files alone!

At this point, I’m going to move the AX2012R3A folder to location I want it to live when I’m using the VM. I have a folder in my Documents called ‘My Virtual Machines’ and I’m going to put it there:

MyVirtualMachines

I also don’t like the name AX2012R3A, since no doubt the CU10 Demo VM will have the same name, and I won’t know which is which. So I rename it AX2012R3CU9. Finally, I’m not going to use the VM configuration provided by Microsoft. It is the XML file that is in the ‘Virtual Machines’ folder, and I’ll delete it that file. The configuration already won’t work, because it is looking for VHD files (and we don’t have any). It also wants a network card called ‘AX2012R3’, and that isn’t what mine is called.

Instead, we’ll create a new configuration now. But first, we need an Internal network card to give to the VM. Open Hyper-V Manager, and then ‘Virtual Switch Manager’. (If Hyper-V Manager is not installed on your PC, you can add it by ‘Turn Windows features on or off’). If you already have an ‘Internal’ switch, that’s fine. If not, hit ‘Create Virtual Switch’. Call it ‘Internal’, and set the type to ‘Internal network’ then click Apply and OK.

NewInternalSwitch
Create a new Internal switch

Now we’re ready to create the VM. Click ‘Action’ -> ‘New’ -> Virtual Machine. Run through the Wizard. I’m going to call mine ‘AX2012R3CU9’, and I specify I want to save it in my ‘My Virtual Machines’ folder that I created:

VMLocation
Specify the location for your VM

On the next screen, select ‘Generation 1’. This is very important. You might think we can just go ahead and create a Generation 2 VM now. We can’t. The boot disk is not configured for Generation 2 and it won’t boot. On the next screen, give it as much RAM as you can afford. It wants 24GB (24,576 MB). I only have 16GB in my laptop, so I can’t give it that. I give it 12GB (12,288 MB). At ‘Configure Networking’, give it the Internal switch that you have. At ‘Connect Virtual Hard Disk’, choose ‘Use an existing..’ and point it to the VHDX for the OS:

OSHardDisk
Attach the hard disk for the operating system

Then we’re done. Well, not quite. But we’re done with the Wizard. But we still need to add the second VHDX file. Select your new VM, and choose ‘Settings’. On ‘IDE Controller 1’, there is a DVD Drive. I don’t need that, so I’ll Remove it and hit Apply:

RemoveDVD
Remove the DVD Drive

On IDE Controller 1, I then add a Hard Drive. I point it at my Data VHDX, and click Apply:

DataHardDisk
Add the Data Hard Disk

When I’ve added the second Hard Disk, I’m ready to boot up the VM. Close the ‘Settings’ window, and Start the VM.

Once it has booted (and after you’ve logged in), I suggest going to Control Panel -> Network and Internet -> Network Connections, and re-naming the connection that exists to ‘Internal’. Later, I add an ‘External’ card, and knowing which is which might be useful one day. Whilst we’re in here, check the IP address that the Internal adapter has. If it begins with 169., it’s not got the right address. There’s a utility in the root of C: drive on the VM which will fix that. Browse to the utility (it’s called setnetworkip), right-click on it and choose ‘Run with PowerShell’. The utility will look for network adapters with an address in the 169. range and assign the correct address (in the 10.20.12.x range) to it. The network adapter should remember the address it’s been assigned, so you shouldn’t need to run this utility again.

Now at this point, we start the conversion of the VM to Generation 2. Generation 2 VMs are only supported by Windows 8.1 (or Windows Server 2012 R2, and I assume Windows 10). If you don’t have Hyper-V running on those operating systems, don’t do the conversion to Generation 2 part of this blog post.

We’re going to use a utility that can be found here, on Part 10 of a blog post written by a Microsoft program manager on the Hyper-V team. On Part 8 of that blog, he describes that we have to disable something called the ‘Recovery environment’ on the Demo VM. You’re already logged into the Demo VM. Open Command Prompt as an Administrator:

OpenCmdPrmpt
Open the command prompt as an Administrator

Enter this command to disable the recovery environment:

reagentc /disable

Hopefully I’ll remember to enable that again later, after we’re done. Now, we’re ready to start the conversion, so shutdown the Demo VM. If you do it right, Hyper-V will report the ‘State’ of the VM as ‘Off’. Once that is done, close Hyper-V Manager.

Now you need to download a utility called ‘Convert-VMGeneration.ps1’. You can get it from Part 10 of that blog. I save it to the root of C: drive.

The utility will copy the OS VHDX into a VHDX which is formatted for Generation 2. To do this, it needs to create an image of the Generation 1 VHDX file. That means at one time it needs three times the space of the OS VHDX. I don’t have enough space for that on my C: drive, so I use a parameter (the ‘WIM’ parameter) to tell it to create the image on my D: drive. I have space on my D: drive, and when the utility is finished with the image it will delete it. So my command to launch the utility looks like this:

.\Convert-VMGeneration.ps1 -VMName AX2012R3CU9 -Action All -Path "C:\Users\guy.terry\Documents\My Virtual Machines" -WIM D:\temporary.wim -NewVMName AX2012R3_CU9

I’ve told it the name of the VM I wish to convert, and I’ve told it the ‘Path’ to put my new VM in. The ‘WIM’ parameter tells it to temporarily create a file on my D: drive, and the final parameter is the name of my new VM (it has an underscore in it to differentiate it from the old VM).

Again, we run the utility in PowerShell, which you open as an Administrator. Use this command to move to the root of C: drive (which is where I saved the utility):

cd /

Then paste in the command:

LaunchVMGeneration
The utility is ready to be launched

When it starts, there is a lot going on very quickly. You might notice Windows Explorer opens, with some odd drive letters. The utility has mapped the Demo VMs drives – it’s nothing to be alarmed about. After that though, it stops, waiting for your input. It is telling you that it is about to erase the data on a disk, and wants you to check its got the right one. It has created a new disk for your Generation 2 OS VHDX and wants you to check that this is the disk it is erasing. In my screenshot, it says it will erase Disk 3, and that I should open Disk Management to check what Disk 3 is:

EraseDisk
Check before you let it erase a disk

You’re on your own at this point. If it has got it wrong and you lose something important, I can’t help you. If you’re not comfortable, answer ‘N’ and skip this part of the blog. I checked in Disk Management and I can see that all my important data is on Disk 0 and Disk 1, so I answer ‘Y’.

After some time (I think more than an hour), the utility reports that it is finished:

VMGenerationComplete
The creation of the Generation 2 VM is complete

The new VM appears alongside the original in Hyper-V Manager. I have to do a little tidying. I delete the original VM. The utility has only converted the boot disk. The E: drive is attached to the new VM, but in its old location. So I remove the second disk from the VM, move the Data VHDX file to the location I want, and then add the second disk back to the VM. I also give the VM 4 processors, instead of 1.

When the VM has booted, remember to open the command prompt as an Administrator and re-enabled the Recovery Environment:

reagentc /enable

If you were skipping the ‘Convert to Generation 2’ part, this is where you pick up again, because unfortunately, we’re not done there (although you should find AX works fine, and you can use the VM). The VM Profile Manager makes a welcome return in the CU9 Demo VM, and by default it is set to give you the full demo experience. Personally, I don’t need any of the Retail POS stuff, so I open the VM Profile Manager, and on the ‘Settings’ tab I set the profile to ‘Minimum’.

Shut down the VM, so that we can check the VM Profile Manager loads a smaller set of applications when we re-boot. But whilst it’s shut down, I create a second switch in ‘Virtual Switch Manager’. This time, it’s an external one, and will allow the VM to communicate to the internet. When you create the switch, select whichever network adapter you want it to use to access the internet. I normally choose my wireless adapter, since it is much more likely that I’ll have a wireless connection than a cabled one. Add this new ‘External’ adapter to the VM, and power it up.

This time when I boot, I can see a whole bunch of windows services (e.g. ten with Retail Commerce Data Exchange in the name) which haven’t started. Good! Go to Control Panel -> Network and Internet -> Network Connections and rename the new network connection ‘External’.

I think we’re nearly done now. I do one more thing, which may help with performance. There are a couple of SQL Trace Flags I like to turn on. They are T4136 & T4199. If you want to know why I turn them on, look here. To enable them, open SQL Server 2014 Configuration Manager. On the left, click on ‘SQL Server Services’. On the right, find ‘SQL Server (MSSQLSERVER)’. Right-click on it and choose ‘Properties’. Head to the ‘Startup Parameters’ tab. Use the ‘Add’ button to add each flag in turn, using the exact format shown in the screenshot (a dash in front, and no spaces):

SQLTraceFlags
Add the SQL Trace Flags

They’ll take effect the next time you reboot the VM.

So that’s pretty much it. There are other things I do, which I won’t write about here. You might put the latest SQL service pack and CU on (SQL Server 2014 RTM is on the VM). You might install the latest windows updates. I set the Time Zone and other language settings in Windows to United Kingdom. I turn off a lot of stuff in the AX Licence Configuration that I will never use (like some Russian functionality, US Payroll stuff, the Public sector functionality) or functionality that barges its way onto many forms (e.g. Catch weight). You might also want to think about disabling WMSII (since it’s not supported with Warehouse management).

Well, that was far too long a post. Its my notes for the next time I do this really, but if it helps one or two others then that’s even better.

Upgrade to Windows 10

This post is nothing to do with Dynamics AX, but I thought I record how my upgrade to Windows 10 went. I said to myself that I wouldn’t install it straight away, since I’d prefer others to go through any early adopter problems. But impatience got the better of me!

On the 29th July, my PC started downloading the update (nearly 3GB):

UpgradeAvailable
Windows 10 is downloaded

When that was complete, it started asking if I wanted to install it. Oh, go on then! The first thing to do is accept the licence agreement:

LicAgree
Boring licence agreement

After a few seconds, Windows update announced it was ready to start:

ReadyToUpgrade
Ready to upgrade

Clicking ‘Start the upgrade’ causes my PC to begin shutdown, triggering the familiar ‘Configuring update….. Do not turn off your computer’ message. After a minute or two, the upgrade process asked me to ‘Sit back and relax’. Never one to argue with that sort of instruction, I made a cup of tea.

UpgradingRelax
Upgrading…. relax

After about 15 minutes, it appeared to be finished:

Upgrade done
Upgrade done

It asked if I wanted to accept some default settings. I wanted to see what that meant, so I went and clicked ‘Customise settings’. It turned out that I didn’t change anything, but at least I know what I was agreeing to. And then it was done.

Did I have any problems? The graphics card I have wanted two reboots, I don’t know why. I didn’t have to do anything else (but I had already read its driver had Windows 10 problems, and had updated it the day before).

Recreate your Replenishment template lines after installing CU9

CU9 for Dynamics AX 2012 R3 contains several updates for functionality in the Warehouse management module. One of them is KB 3051622 “Warehouse Management (WHS) Replenishment performance issue”. If you’re using replenishments in Dynamics AX 2012 R3, it’s worth checking out the LCS page for this fix, because it goes into some detail about the changes that this hotfix brings.

One change relates to the use of the ‘Replenish only fixed locations’ tick box on the replenishment template line. I can’t imagine how you would be using replenishments and NOT have this field ticked, so I think it will be relevant to everyone using replenishments. With this hotfix installed, when ‘Replenish only fixed locations’ is ticked, the ‘Select locations’ button becomes greyed out:

ReplenishOnlyFixedLocations
If ‘Replenish only fixed locations’ is ticked, the ‘Select locations’ button is unavailable

To compensate, the ‘Fixed product locations’ and ‘Locations’ tables are added to the ‘Select products’ query:

ProductQuery
The Product query is extended to the ‘Fixed product locations’ and ‘Locations’ tables

(And, if you’re using Product variants, then the ‘Fixed product variant locations’ table is added to the query).

This, according to the description of the hotfix, improves the performance of the replenishment functionality. In my test environment, which has CU9, I haven’t noticed a problem with the performance of the replenishment job. But I have seen a customer’s test environment (with an extraordinarily large number of items and locations/fixed locations) where the replenishment job was taking over half an hour to create a replenishment for ONE item. So I have high hopes for the performance part of this fix!

An important point is described on the hotfix’s LCS page: “Note: When you apply this hotfix, any existing replenishment template lines that are configured with “Replenish only fixed locations” checked must be deleted and recreated to prevent errors during processing.

This is because the query that was saved against each replenishment template line is now invalid. If you don’t recreate the query, when you run the replenishment job, you’ll get the error “Error executing code: Wrong argument type for function.” and no replenishment work will be created:

ErrorexecutingcodeWrongargumenttypeforfunction
The replenishment job will throw an error

To eliminate the error, the advice says to delete the replenishment template line and recreate it. I find that you don’t have to delete the line, but you do have to delete the query. The easiest way to do this on one line is to un-tick and re-tick the ‘Replenish only fixed locations’ tick box. Then you can re-configure the query and you’re away.

If you managed to import your replenishment template lines, then you’ll want to delete them, adjust the import to build an ever so slightly different query, and reimport them. If you created your replenishment template lines by hand.. well, I hope you don’t have too many. But either way, you’ll need to factor this exercise in to any CU9 upgrade plans that you have.

 

 

 

Automatically cancel (or delete) Sales Order lines for Supplementary Items

In AX 2012 R3, a new field appeared on the Customer form. The field is labelled ‘Automatic cancel’, and is so innocuous you may well have not even noticed it:

AutomaticCancel
The ‘Automatic cancel’ tick box

There is currently no mention of it on the TechNet help page for the customer form. The only clue to its intended purpose comes from the help text for the field, which reads ‘Automatically cancel supplementary item if the item it is attached to is cancelled’.

It so happened that an implementation I was working on expected to use supplementary items on sales orders, and I could use the functionality apparently offered by this tick-box. So I tested it, and found that it didn’t do anything – or at least it didn’t do what I expected it to do. To explain what I expected it to do, I should mention what supplementary items are.

For any item in Dynamics AX, it is possible to define one or more ‘Supplementary items‘. Supplementary items are items that might typically be added to an order when the ‘main’ item is ordered. In fact, it is possible to define Supplementary sales items and Supplementary purchase items for an item. That is to say, you can define a list of items that is typically sold (or purchased) when the main item is sold (or purchased). For example, if your business is in the habit of selling laptop computers, then you might decide to set a laptop bag, an extended life battery and a travel mouse as the supplementary sales items for a laptop item.

Additionally, supplementary item functionality has often been used in buy-one-get-one-free scenarios, since it is possible to define that the supplementary item (which might normally have its own selling price) can be sold for free when added to an order as a supplementary item.

In either scenario, there have always been a couple of problems with the use of supplementary items. One of those is that if you remove the ‘main’ item from the sales order, the supplementary items remain on the order. You have to remember to also remove any supplementary items that were added. If a customer decides not to buy the laptop, will they still want the laptop bag? Probably not. Worse still, if you offer a buy-one-get-one-free deal, and the ‘buy one’ item is removed from the order, there is the chance that the customer will still get the ‘get one free’ item – for free!

So I expected the ‘Automatic cancel’ tick box to plug this gap… or at least I expected it to handle the scenario where the main item is cancelled, by automatically cancelling the related supplementary items. Unfortunately, I found that this did not happen.

So I logged it with Microsoft support. Based on other recent experiences, I was aware that they probably wouldn’t be able to handle it has a bug fix. But I was also aware of a thing called a DCR (design change request?) by which support agents had recently been able to get me changes to the standard product, so I had some hope. Noting that the field actually lived in the MCRCustTable table (and was therefore probably inherited with the Call center functionality), I make some cheeky requests of my own:

  • The functionality should work on all sales orders, regardless of whether the Call center functionality had been enabled
  • The functionality should work when the order line for the ‘main’ item was deleted (not just when it was cancelled) and in this scenario should result in the automatic deletion of the order line for any supplementary items.

And less than two months later, hotfix KB 3072485 was released. And do you know what, it handled both the cancellation and deletion of the ‘main’ item, and it worked even if Call center was switched off. I could not be more pleased. At my direct request, Microsoft support have arranged for the creation of seven hotfixes this year, two of which are DCRs. I am stunned at this level of service from a giant like Microsoft to a little old consultant like me!

But back to the hotfix. What does it do? If you tick the ‘Automatic cancel’ tick box on any customer, when you add a supplementary item to an order for that customer and you either:

Cancel the order line for the main item

or

Delete the order line for the main item

the same will happen to the order line for the supplementary item.

Perfect! Unfortunately, it doesn’t help with the other problem with supplementary items (there is no way to automatically add them, or even to prompt the user so that they know that supplementary items are defined), but you all have your own solution for that already, right?

Its worth noting that the functionality does not also work on purchase orders. And you’ll also want to know that KB 3072485 did not make it into CU9.

‘Nothing happens’ when posting a Purchase order product receipt

I’ve encountered a problem when posting a product receipt on a purchase order that you need to see to believe. For this reason, I’ve recorded a video which shows the problem and the cause.

The problem is that, when posting a purchase order product receipt, nothing happens. This means the product receipt journal is not created, the status of the items on the order doesn’t update, and perhaps most surprising is that you don’t get an error message.

In my example, we see that a missing ‘Purchase expenditure, un-invoiced’ account on the item’s posting profile is responsible.

‘Field Account must be filled in’ on Accounts Receivable parameters

A quick post; one for the search engines more than anything else. If you’re modifying the Accounts receivable parameters in Dynamics AX, you can get the error ‘Field Account must be filled in’.

FieldAccountMustBeFilledIn
Pesky error message

When this happens, any changes you want to make in the parameters form cannot be saved. The cause is quite straight forward; you have configured an active credit card processor at Accounts receivable -> Setup -> Payment -> Payment services. The solution, therefore, can be found on the ‘Credit card’ tab of the Accounts receivable parameters form. Enter an offset account in the ‘Credit card payment posting’ area:

CreditCardPaymentPostingOffsetAccount
Add an ‘Account’ here

Setup for Call Center gets a little bit harder with CU9

I’ve called this post ‘Setup for Call Center gets a little bit harder with CU9’. I could equally have said ‘Call Center gets extra functionality in CU9’. You can spin it either way, depending on whether you want the extra functionality, or not.

I am half-way through writing another post, which starts by describing how simple it is to enable Call center functionality on standard AX sales orders. I’ll steal these words for this post.

The bare minimum setup to enable the relevant Call center functionality is:

If you need help with any of that, I have previously covered this setup. Refer here, and stop when you get to “you’ve done enough to enable Retail functionality on standard AX Sales orders”.

And then I installed CU9, and I’m not able to add lines to sales orders any more. Creating sales order lines produces an infolog which reads ‘Mode of delivery is not valid for Item number…’. Excuse me? What?

ModeOfDeliveryNotValid
Mode of delivery is not valid for Item number

Actually, I shouldn’t have been too surprised. I was actually testing the functionality because a colleague complained AX wasn’t throwing this error. You see, some of us want this behaviour. But what is it all about? Well, there have been some clues on the ‘Mode of delivery’ setup form since AX 2012 R2:

ModeOfDeliverySetupForm
The Mode of Delivery setup form

If you have the ‘Retail Headquarters’ configuration key enabled, there are fast tabs on the Mode of Delivery form for Retail channel, Product and Address. These allow you to restrict when a Mode of Delivery can be selected. It’s possible to say that this mode of delivery can only be used by these stores. And it’s also possible to say that this mode of delivery can only deliver to these countries (or states). Using the ‘Product’ tab, you can also define which products can be used with a specific Mode of Delivery. Until R3 CU9, this ONLY worked with traditional retail functionality (e.g. on the PoS). But CU9 contains hotfix KB 3026775 – ‘When creating a sales order in Call Center, a warning does not appear when a delivery mode and address exclusion combination is entered‘.

As far as I can tell, if you are using call center there is no way to disable the functionality. So we’d better embrace it and find out what we need to do to make it work. And the first step is easy. Add the countries to which you deliver to the mode of delivery:

ModeOfDeliveryCountry
Add countries to the mode of delivery

That was easy! The next step is obvious too. Add your call center to the mode of delivery. Except perhaps you can’t:

ModeOfDeliveryCantAddCallCenter
The call center cannot be selected

If your call center is not available to select, it might because you have been listening to me, and you’ve only done the bear minimum of setup to enable the call center functionality. The dialogue box in the screenshot above gives you a clue. Your call center should be in a Retail Organisational Hierarchy. You’d better refer to this post that I already mentioned (and leave that page open – I have a feeling we’re going to need it again). Once you’ve assigned your call center to the organisational hierarchy, you can add it to the mode of delivery:

ModeOfDeliveryAddCallCenter
Adding the call center to a mode of delivery

The last thing we need to do is add our products to the mode of delivery. If you look at that fast tab, you’ll see it doesn’t mention anything to do with ‘Item number’. You’re going to need a Retail Product Hierarchy, and you’re going to need to add all the items (that you want to sell) to that hierarchy. If you weren’t planning on using a Retail Product Hierarchy, this might be a little annoying. But you don’t have to create a complicated hierarchy. You could just have one level, and assign every item to it. And you never know, you may actually find this functionality useful – do you really want to be able to select ‘Customer collect’ on an order with a delivery address that is two continents away!? Anyway, once you’ve done it, it’s very easy to add ‘All’ items (which are in the product hierarchy) to the mode of delivery:

ModeOfDeliveryAddProduct
You could add All products to the mode of delivery

Now I have to make a confession at this point. I have spent so long playing with Call center to see what setup is required and what isn’t, that I have run out of ‘clean’ R3 environments. My assumption is that I don’t need to create catalogues and assortments and all manner of other retail things – but I don’t actually have access to an environment where I haven’t done that already. Certainly for this example, I haven’t added a Price group to my Call center, and I haven’t added my Call center to a catalogue. I will get my hands on a clean environment at some point, and at that time I will update this post, but until then you’ll just have to let me know.

So, have we now done enough? Not quite. Go to your Call center channel -> Setup -> Modes of delivery. No mode of delivery is listed. How can that be?

CallCenterHasNoModeOfDelivery
The call center has no mode of delivery

There’s a periodic job in the Retail module called ‘Process delivery modes’. We need to run it. And then, it all starts to work. Once again, I can create sales orders which have access to all the great call center functionality.

You might consider setting the ‘Process delivery modes’ job to run in batch with a recurrence. If, for example, you add a new item to the product hierarchy, the job will need to run before the item can be used with your modes of delivery.

So, I hope this is a ‘heads-up’ to anyone using call center to supplement sales order functionality and that is thinking of upgrading to CU9.

Finally, please, if you see this error, will you comment below. I can’t be sure if the environment I am using is a bit of a mess, or if I need to do more setup! I can make it appear by clicking the ‘Recalculate’ button on the ‘Sell’ tab of the sales order.

DataValidationExceptionSaleslineitemcouldnotbefound
DataValidationException: Sales line item could not be found

 

New in R3 CU9 – Display a list of open work on the mobile device

CU9 for Dynamics AX 2012 R3 was released last week. It contains a good number of fixes, more than a dozen of which I really need for AX implementations I am involved in. But it feels to me that, when compared to last year’s CU8, it does not contain so much new functionality. Many of the new features are in the Warehousing module, and although two of them are on my ‘must have’ list, they have been available already as hotfixes in their own right.

One enhancement, however, was available first in CU9, and is the topic of this blog post (it has subsequently been made available via hotfix KB.3062537). It is the ability to create a menu of open work on the mobile device, allowing the operator themselves to choose what to do next. To configure it, head into the ‘Mobile device menu item’ setup form. The eagle-eyed amongst you will notice two new buttons in the action pane (both greyed out) labelled ‘Edit query’ and ‘Field list’:

Two new buttons appear on the menu item setup form
Two new buttons appear on the ‘mobile device menu item’ setup form

If you create a new menu item, and set ‘Mode’ to ‘Indirect’, you’ll find a new ‘Activity code’, called ‘Display open work list’, is available:

WorkListMenuItem
A new Activity code is available

Selecting this activity code causes the two new buttons to become available. Let’s have a look at this on the mobile device, and then we’ll come back and look at how we can configure it. In the screenshot below, you can see seven instances of work are shown, along with a little information about each:

WorkList
The work list on the mobile device

The operator can select some work to do, by clicking on the relevant Work ID. They can also scroll the further down the list (only the first 7 of 14 work items are shown in my screenshot), and they can click on any column heading which is highlighted in grey to re-sort the list.

To configure what is displayed to the operator, we have four options:

1. The operator must already have permission to access any work shown by means of another menu item. This means if a user has access only to menu items which allow ‘Inbound’ type activities (Purchase order receiving and put away, for example) then they will not see any work related to replenishments or sales order picking. You don’t need to do anything particularly to configure this – it just works based on what menu items they already have access to. What’s more, these menu items must have ‘Directed by’ set to ‘User directed’. i.e. they must already be configured to allow the user to select what work to do next. If the operator has access to pick Sales orders, but the menu item is configured to System directed (i.e. it is configured so that AX is telling the user what to do next), they are not going to be able to come into this work list and decide for themselves what Sales order to pick next.

2. As with other menu items, you can select which class work that appears on the list must have (and selecting no class means the list is not filtered by work class):

WorkClasses
Menu item work classes

3. The ‘Field list’ button allows you to define which columns are shown on the screen of the mobile device. Up to eight fields can be shown. The first is always ‘Work ID’, and is mandatory. The other fields that you can select come from WHSWorkTable and WHSLoadTable. WHSWorkTable contains the Work header, and can be seen on the ‘All work’ list page. WHSLoadTable contains the Load header, and can be seen on the ‘All loads’ list page. Any field that you think is useful from the Work or Load can be shown on the list. In addition, two ‘display method’ type fields can be added. These show either the location on the first Pick line of the work header, or the location of the last Put line. Since these two fields are display methods, they cannot be filtered or sorted on. The data actually comes from the work lines, and any intermediate locations (e.g. staging locations) cannot be displayed.

FieldList
Select which fields should be displayed

4. The ‘Edit query’ button has two uses. It can be used to filter the records shown in the list. Any field on the Work and Load headers can be used. It also allows you to define the default sorting order of the list (and if you do nothing, the list will be sorted by Work ID in ascending order).

Two more fields exist on the menu item setup form. The first allows you to define how many work headers should be shown on each ‘page’. If there is more work than can fit onto the page, the operator will be able to click on ‘Previous’ and ‘Next’ buttons to scroll through the list. This field, in combination with the number of columns that you choose to display, allows you to configure the data that is displayed to fit onto the screen of your mobile device, although Microsoft themselves recommend displays which are at least 7″ in size.

The ‘Allow users to filter by work transaction type’ tick box adds a drop-down above the list, which allows operators themselves to filter the list (beyond any filtering you may have already created for them):

FilterWorkList
The work list can be filtered by the operator

When the user chooses some work to do, the mobile device switches to the relevant menu item (that the user already has access to) to complete the work. If the user has more than one menu item which could do the work, they are asked which they would like to use:

ChooseMenuItem
The user may be asked to select which menu item to use

Although not particularly suitable on the traditional hand-held barcode reader type devices, I am sure that users in some warehouses will find this functionality useful: where system directed cannot be used, it eliminates the need for printing Work lists and scanning Work IDs. As is the case throughout the Warehouse management module, so much of this functionality is configurable. In that way, it is limited partly by your imagination. In my example, I showed only and small number of fields on the list. It is possible to allow the operators to see (and perhaps crucially, sort or filter on) any number of fields. For example, the scheduled ship time (from the Load) can be used to ensure orders which are to be delivered first are picked first. The Sales order number can be shown, which, to my knowledge, allows the operator to select work by sales id for the first time.

If you want to read more about it, the TechNet site has been updated here (under ‘Setup a menu item for activities and queries’). And the product team have written about it on the AX SCM blog.

Trade agreement ‘Find next’ on Call center Sales orders

On Sales Price (and Discount) Trade Agreements, AX will look for applicable trade agreement lines in a specific sequence (as described here. Basically, it looks for a “Customer = Table and Item = Table” record first, and “Customer = All, Item = All” last). If ‘Find next’ is ticked on the trade agreement line, AX will keep looking for a trade agreement line with a better price (or discount) for the customer. If ‘Find next’ is not ticked, when AX finds a trade agreement line it stops looking.

If you’ve a price defined for an individual customer and a price for All customers, it is possible to un-tick ‘Find next’ to ensure AX will always use the individual customer price in preference to the All customer price, even if the individual customer price is higher. In this screenshot, trade agreement lines are created for an Item. ‘All’ customers get a nice low price of 10.00 USD. Customer ACBE alone gets a price of 15.00 USD. Notice that ‘Find next’ is not ticked on the trade agreement line.

Trade agreements for 'All' customers and for customer ACBE
Trade agreements for ‘All’ customers and for customer ACBE

Here is a standard Sales order for Customer ACBE for the Item. The price defaults to 15.00:

The price the customer gets on a standard sales order is 15.00
The price the customer gets on a standard sales order is 15.00

I thought I was on pretty strong ground when it came to this behaviour….. and then I enabled Call center functionality and brought Retail Pricing into play. Although AX still refers to the trade agreement sales prices, it seems the behaviour when ‘Find next’ is un-ticked (that I described above) is being ignored: Retail sales orders always find the ‘best’ price that the customer could get – as though ‘Find next’ was ticked on all trade agreement lines. Here, on this Retail sales order, the price defaults to 10.00, even though no changes have been made to the setup of the Trade agreements:

The price the customer gets on a retail sales order is 10.00
The price the customer gets on a retail sales order is 10.00

ACBE is getting the ‘All’ customer price, even though he has his own price which should always apply.

I wanted to investigate using Retail pricing on sales orders, but also had the requirement to honour the ‘Find next’ setting. More in hope than in expectation, I logged the behaviour I’d seen with Microsoft support. I fully expected to be told that this is how the retail pricing engine worked, so when I got back the news that this bug had been fixed I was stunned!

After installing the hotfix, Customer ACBE gets the price 15.00 on the Retail sales order:

The price the customer gets on a retail sales order after the hot fix is installed is 15.00
The price the customer gets on a retail sales order after the hot fix is installed is 15.00

The relevant hotfix (KB 3064046) is a kernel update only. This must mean the retail pricing engine sits outside the AX application. It also means that you’ll have to take this modified behaviour (should you decide to update your kernel), whether you want it or not! After installing KB 3064046, the Kernel version of the becomes 6.3.1000.2594. Therefore, assume that any kernel version equal to or higher than this exhibits the modified behaviour I describe. The hotfix is available now on LCS.