kurthatlevik: DAX 2012 R3 – Retail Channel POS reports

[Blog bot]

Источник: https://kurthatlevik.wordpress.com/2...l-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 !




Источник: https://kurthatlevik.wordpress.com/2...l-pos-reports/