Monthly Archives: April 2014

Distribution List and Retail sync

Let’s look at a common scenario. You have created the database profiles and tested them successfully. You run any of the N-Jobs, the system shows “Successful with commerce data exchange.” in its Status messages in the Distribution schedule form. Still, nothing is replicated to the stores and the messaging tables at the HQ database and Store database are empty. There are also no temporary files in the “Work” directory for the Sync service on the server or store.

First thing that you should check if you attached the particular store to the distribution location list which is located in Retail -> Setup -> Retail scheduler -> Distribution location list.


If its still not working check your current version. In AX 2012 R2 CU7 I encountered a weird “bug” in the distribution location number that might help you. If the value is not a number for example 00001 the system won’t give you an error but it won’t send any data, there will be no messages etc. I tried it on the demo VM R2 CU6 and it seems that there it’s working so it’s only an issue in CU7.

Until next time!



Retail statement parameters

Today we are going to talk about retail statement parameters and how they interact with each other and use transactions to create statements. Retail statements are one of the most important aspects of retail operations and a correct setup is very important for any business scenario.

Specifically, the focus will be on the parameters that split or group transactions based on properties of the transactions.


Retail statement parameters

The parameters explained will be the following:

  • Statement method
  • One statement per day
  • Closing method
  • End of business day
  • Post as business day

The first parameter “Statement method” is pretty straightforward. It has 4 options:

  • Staff – groups transactions on a statement by payment method and  staff
  • POS terminal – groups transactions on a statement by payment method and terminal
  • Shift – groups transactions on a statement by payment method and shift
  • Total – groups transactions on a statement by payment method

Basically it will group the transactions in the selected transactions interval inside one statement based on the selected method (Staff, POS terminal, Shift) and then by payment method. Following are some examples of statement methods.


Statement method Shift


Statement method Total

The second parameter is my “personal favorite” called “One statement per day”. The first time I saw it based on the name and what the official documentation says “a check box that indicates that only one statement will be created each day for the retail store”, I thought great, a parameter that will automatically split all statements by day. Right? Wrong!

After some testing and not getting anywhere we of course start digging into the code to see what it does. We find this jewel in several spots which contains code for the “End of business day” parameter aswell:

if (_storeTable.oneStatementPerDay == true)
        this.transFromDate = this.transToDate;

        if (retailStoreTable.stmtCalcBatchEndTime > 0)
            this.transFromTime = retailStoreTable.stmtCalcBatchEndTime;

The thing that this parameter will actually do if it is set is just initialize the “From Date” on the statement. Some modifications needs to be done to be able to create statements that are automatically split by day. For example, if we wanted to group all shifts that happened in a day in a specific store we can only do it manually by the following steps:

  1. Make sure the statement method for the store is “Shift”
  2. Closing method is “Date and time”
  3. Go to Retail -> Journals -> Open statements
  4. In the group New, choose New Statement
  5. Choose the start and end date and time on the transaction interval to one day
  6. Calculate the statement

Steps 4-6 need to be repeated for every store and every day that needs to be calculated which is tedious but there is no alternative in AX. In one of the next blog posts we will make some modifications in the AX periodic job “Calculate statements” to enable and automate automatic statement creation split by day.

The closing method parameter can be a tricky one. One of the important facts to remember about this parameter is to never change it once it is set. The closing methods are:

  • Date and time
  • Shift

The “Date and Time” method will pull the transactions that are in between the specified Transaction interval on the statement. The “Shift” method works in a specific way. It will pull a whole, closed shift with unposted transactions into the statement if the interval is within the shift. An example of this is in the picture below:


Closing method Shift

Here we can see that the interval is from 2/5/2014 to 2/6/2014 but the statement pulled shifts that have transactions ranging from 2/2/2014 to 2/6/2014. Now, if we used the statement method “Date and time” and posted some transactions but not the whole shift and then we decide to switch me method to “Shift” we won’t be able to pull the shift that already has posted transactions! Here’s an example:

A shift that has transactions from 4/2/2014 to 4/11/2014


The shift ranging from 4/2/2014 to 4/11/2014

Some of the transactions on the shift are already posted using the “Date and Time” closing method.


Posted transactions in the shift

We try to calculate the statement but nothing is pulled into the statement because some of the transactions are already posted.


No transactions are pulled

The parameters “End of business day” and “Post as business day” are linked. The “Post as business day” must be checked for the system to use the time specified in “End of business day” for statement posting. In the “End of business day” field enter the time that the last transaction must be recorded to be included in the business day’s statement. The “Post as business day” parameter is needed when we want to include transactions that happened on different days on the same posted sales order invoice. There is a very good example of this on the Microsoft Technet:

“A store is open between the hours of 8:00 A.M. and 3:00 A.M., and the store is configured with the Post as business day check box selected. The store records transactions between the hours of 8:00 A.M. and midnight on May 31. The store also records transactions between the hours of 12:01 A.M. and 3:00 A.M. on June 1.

When the store posts its statement for the close of the business day, a sales order is generated that includes all transactions that were recorded between the business hours of 8:00 A.M. and 3:00 A.M., even though the transactions occurred on May 31 and June 1.

If the same store is configured with the Post as business day check box cleared, separate sales orders are generated when the store posts its statement for the close of the business day. One sales order includes the transactions that were recorded between the business hours of 8:00 A.M. and midnight on May 31, and the second sales order includes the transactions that were recorded between the hours of 12:01 A.M. and 3:00 A.M. on June 1.”

The last parameter “Split by statement method” refers to the first parameter in this “Statement/Closing” FastTab which is “Statement method”. If this parameter is checked the system will create separate statements based on the selected “Statement method”. Here is an example of this by using the “Calculate statement” job in the Periodic section of the Retail module. The selected “Statement method” is Shift.

After running the job the system made separate statements for each shift. Notice that the system calculated the statements for the whole interval up to today which means there is no start date.


Split by statement method checked

If the checkbox is not selected and we run the job again all unposted shifts are pulled in one statement.


Split by statement method unchecked

That’s all you need to know about retail statement parameters and transactions grouping. Keep on DAXing!

Retail Offline Sync Service fails to synchronize

Last week I had a case where my Retail Offline Sync Service stopped working.

A little background first. This is the topology for a Retail store and what this post is all about.


The POS has to be in „Work offline mode“ so that it checks its own database. If the POS is in “Online mode” it will check the store database.


Let’s get back to our problem. I added a new Expense account to my store expecting it will be replicated to my offline POS database.


We go to our POS that is currently in offline mode and try to add an expense. To our surprise the new expense account is not present.


The first thing we check is if the expense account is even replicated to the store database. So we log into the POS that is directly connected to the store database and check expense accounts. Sure enough the account is there.


The first conclusion is that there is a problem with the Offline Sync Service in the store. By default the tracing on the Offline Sync Service is not enabled. To enable the tracing we need to change the config file. The config file on your computer with the offline POS database is located in:

C:Program Files (x86)Microsoft Dynamics AX60Retail POSRetailOfflineMicrosoft.Dynamics.Retail.Offline.Service.exe.config

Append the next code to the config file:


    <!--  0-off, 1-error, 2-warn, 3-info, 4-verbose. -->
      <add name="SyncTracer" value="3" />

    <trace autoflush="true">

        <add name="Tracing" type="System.Diagnostics.TextWriterTraceListener" initializeData="SyncTrace.log" />

In the “SyncTracer” value field you can specify how much info we want from the tracer. We will put this to “info” to see a little more information. After all is done and finished don’t forget to switch the tracing to “off” because the trace file can become rather large.

After we changed the config file we restart the offline sync service just in case.


The log file  SyncTrace.log is created in the same folder as the config file. We open the log file and this is what we see.

ERROR , Microsoft.Dynamics.Retail.Offline.Service, 7, 04/13/2014 09:56:14:123, LocalConnectionStringMissing

It’s telling us that some connection string is missing. So we open up the config file for the offline sync service again and check the connection strings. This is what we see:

        <add name="Microsoft.Dynamics.Retail.Offline.Service.Properties.Settings.LocalDBConnectionString"
            connectionString="" />
        <add name="Microsoft.Dynamics.Retail.Offline.Service.Properties.Settings.RemoteDBConnectionString"
            connectionString="Data Source=AX62POS-ZG-01;Initial Catalog=RetailSplitStore;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True" />

Apparently the connection string for the offline database is missing. The connection string for the remote database (the store database) is correct. We fix the connection string and restart the service:

        <add name="Microsoft.Dynamics.Retail.Offline.Service.Properties.Settings.LocalDBConnectionString"
            connectionString="Data Source=AX62POS-ZG-02;Initial Catalog=RetailSplitStoreT2;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True" />
        <add name="Microsoft.Dynamics.Retail.Offline.Service.Properties.Settings.RemoteDBConnectionString"
            connectionString="Data Source=AX62POS-ZG-01;Initial Catalog=RetailSplitStore;Integrated Security=True;Persist Security Info=False;Pooling=True;Encrypt=True;TrustServerCertificate=True" />

TIP: To check if the data is replicated we could just connect to the databases and check the tables. Sometimes it’s a much faster way to check replication instead of going through the POS. This is the store database data in the RETAILINCOMEEXPENSEACCOUNTTABLE table (the one with the income and expense accounts). We can see that our new expense account is present:


This is the offline POS database data for the same table:


The account is still missing. We open up the SyncTrace.log again and see this:

INFO, Microsoft.Dynamics.Retail.Offline.Service, 7, 04/13/2014 10:04:53:132, Terminal Offline: YES

The catch is that it won’t sync the database unless we get the POS back into online mode! Once we do that the account is replicated to the offline database.


We take the POS offline again and try to add an expense account and sure enough it’s there:


I hope you enjoyed the tips&tricks of the offline sync service. Until next time!