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 !

I appreciate that I can be a part of this worldwide blog community—as a consultant working from Norway, the blog lets me share more than 20 years of experience with Microsoft Dynamics 365. Along the way, I participated in developing retail, PDA/RF, barcoding, master data, kitting and WMS-solutions for Dynamics. My blog focuses on my deepest interests and expertise: along with a 360 degree view of digital transformation topics, I welcome opportunities to dive into retail and intercompany supply chain automation, logistics, and production—everything that is moving around in a truly connected enterprise. As Enterprise Architect on Dynamics 365, I specialize in strategic development and planning for corporate vertical solutions and works to build international networks that increase knowledge and understanding for Dynamics 365. As an advocate for both providers and customers, I'm committed to ensure that customers constantly changing needs are meet, and I see community as key for increasing expertise. I welcome you to connect with me.

5 comments on “DAX 2012 R3 – Retail Channel POS reports

  1. Hi,
    i am unable to view the staff name in report sales by staff. Can anybody help me on this.


  2. mohamed abd elnabi

    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



  3. Mohammad Shahnawaz

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


  4. hi , Greate idea to Create report in the existing reports
    for new learner


  5. Samran Habib

    Great, thanks for sharing.


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 )

Google photo

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

Twitter picture

You are commenting using your Twitter 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.

%d bloggers like this: