DAX 2012 R3 – Retail Channel POS reports

Both ePOS, mPOS and the upcoming AX7 CloudPOS have a built-in feature for showing small reports and KPI’s directly on the POS. These reports are not running against the Dynamics AX database, but against the retail channel database connected to that specific terminal.

The great thing about these reports, is that it does not require any heavy development to define these reports. They are just XML report definitions, that queries the database for specific columns or stored procedures. This means that we easily can create new reports based on the direct reporting requirements. In the report, shown below a report called “Sales by staff” is shown. Here the staff name, number of transactions, sales amount and average sales amount is shown.

The report can also be shown as graphics.

The report definitions are located in the RetailàSetupàChannel report configuration.

We see here the report definition, and it can look a bit cryptic. But let’s format the XML to better understand the definition. We here see that the retail report basically have 3 sections. The dataset, parameters and the reportcharts.

As we can see here, the dataset is referring to a stored procedure, with 3 parameters; Channel, start-date and end-date. If we open the SQL stored procedure we can see the exact implementation of it.

We see here that the actual source tables are an inner join between retailTransactionTable and retailStaffTable. We can also see that the amount is “retailTransactionTable.paymentAmount”. This amount is the amount of what the customer is actually paying inclusive taxes.

But a US-based customer of me asked if it is possible to make some minor changes, and just show the reporting exclusive taxes. To do this, it means that we must do some changes. We cannot use the existing standard stored procedure. We could surely create new stored procedures, but rolling this out to hundreds of terminals would take a lot of time. But we have the option to use an actual query in the report XML definition instead.

The first step is to try to create a SQL that retrieves the sales amount minus taxes. As far as I have interpreted the table retailTransactionTable, it seems that the field I can use is the -1*[NETAMOUNT]. Here is the query tested directly in SQL Manager, and I have “yellowed” out the differences compared to the script used in the stored procedure CRT. GETSALESBYSTAFFREPORT

It seems to be working, and the next step is to create a new XML report definition. I therefore took the report “101 Sales by staff”, and created a “101_US Sales by staff”, with the following XML report. Here I have colored the differenced to the original report in yellow. As you can see here, I use a query, instead of a stored procedure.

I also localize the labels in the report

To have the new report available to the users, I need to run the CDX Async job to send data to channels.

  1. In AX, go to Retail > Periodic > Data distribution > Distribution schedule

  1. Select job 1110, click Run now

  1. Wait couple of minutes for the job to finish

I then have the new retail report available.

Then I get my POS report, where the amounts is without taxes J

Happy DAX’ing !

9 thoughts on “DAX 2012 R3 – Retail Channel POS reports

  1. Is there a button grid for the reports? i.e. If i want to change the font size, color, images, etc. i would normally just edit the button grid, but i cant seem to find one for the reports section of MPOS…


  2. Hi Kurt i Know it is a old post, but still works on D365 pos.
    I they a way to see @ they exists , like @dt_StartDate,@bi_ChannelId where so they come from


    • Hi Ken. I’m not quite sure I understand, but assume that you would like to understand where the @ variables originate from ? I’m not 100% sure, but by looking at existing reports, I tink you should be able to find most of the variables available, like @dt_StartDate, @dt_EndDate, @bi_ChannelId. Sorry 🙂


  3. dear ,

    when add this code coming error ( The ‘=’ character, hexadecimal value 0x3D, cannot be included in a name. Line 19, position 25. )

    please reply what is the issue



  4. Hello,
    Nice Post,
    it is very helpful for the newcomer.
    could you please add more reports example and forms in details.


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.