AXForum  
Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
All
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

 
 
Опции темы Поиск в этой теме Опции просмотра
Старый 28.10.2015, 20:11   #1  
Blog bot is offline
Blog bot
Участник
 
25,459 / 846 (79) +++++++
Регистрация: 28.10.2006
kurthatlevik: DAX 2012 R3 – Retail Channel POS reports
Источник: 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/
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.
 

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
kurthatlevik: DAX 2012 R3 – Retail Channel POS reports Blog bot DAX Blogs 0 12.08.2015 20:17
kurthatlevik: DAX 2012 R3 CU9 Retail database version mismatch and GAC issues Blog bot DAX Blogs 0 25.06.2015 10:11
kurthatlevik: DAX 2012 R3 CU9 – new Retail pricing simulator Blog bot DAX Blogs 0 16.06.2015 16:11
kurthatlevik: New DAX 2012 R3 CU8 Global Retail codeplex solution available Blog bot DAX Blogs 0 28.12.2014 20:11
atinkerersnotebook: Walkthrough & Tutorial Summary Blog bot DAX Blogs 1 09.09.2013 09:11
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Рейтинг@Mail.ru
Часовой пояс GMT +3, время: 02:19.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.