AX 2012 & Management Reporter: Customer rebates customized report

While the customer rebate functionality in AX 2012 R3 is quite good and flexible, there is however one crucial thing missing: Reporting tool! This is if you are using the customer rebate functionality, not the Trade Allowance Management, which is somewhat different.

So, I have a customer which is going to use more or less all the customer rebate functionality in AX 2012 R3, and I need a solution for reporting on the rebates. I have previously made a simple transactions report by setting some filters on the standard “customer transactions” report in AX. This is fine, but doesn’t really look really good.

Instead of going for custom development, I decided to fiddle around in Management Reporter, using predefined posting definitions and attributes to generate this result based on monthly rebates:

Now, if I drill down one step to customer/vendor totals, if looks like this:

And the transactions overview look like this:

Notice that I have added additional information, Voucher number and Transaction description.

What about Excel?

On export to Excel, you have the option to for example sort on voucher numbers:

If you look closely, you have probably noticed that I have reported on both customers (obviously since it is customer rebates) as well as vendors. The easy explanation for this is that in AX 2012 you have the option to either create credit note (on the customer) or pass the rebate to Accounts payable (vendor). I want (and need) to report on both scenarios, because some customers deduct the rebate from payment, and some customers require the rebate to be paid out.

Now, I want to try this! How do I do it?

It is actually quite easy to create this report, especially if you already know your way around Management Reporter. The only prerequisite required is to add “Customer name” and “Vendor name” attributes to be included in the ERP Integration between AX and Management Reporter. You can read all about how to set this up in my blog post here:

https://dynamicsaxtips.wordpress.com/2015/06/04/advanced-management-reporter-attributes-add-missing-attributes-to-management-reporter/

Okay, so I’m going to explain the Row definition, Column definition and how to run the report from a Report definition. After that, I am going to show what settings that needs to be setup in AX to achieve this result.

The Row definition

The Row definition might seem complex and advanced, but it is actually really simple. I have made one row for each customer/vendor that I know I have a Rebate agreement related to. I have also splitted the rebates for the customers that is paid by credit note, and the rebates that is passed to accounts payable (vendors). I want to start explaining the columns from the right, and then move left.

Link to Financial Dimensions column

The main accounts that we want to include in this scenario, are all accounts related to postings for Accounts receivable:

In my case this includes main accounts 130100 to 130300.

I must do this procedure for every customer that I want to display on my report.

For the vendors where I have passed the rebate to accounts payable, I select the Accounts Payable main accounts in a similar way:

In my case this includes main accounts 200100 to 200125.

I must do this procedure for every vendor that I want to display on my report.

Row modifier column

The Row modifier column is where we will define what attributes to filter the selection from. I have made an easy filter, first based on attribute “Customer name” and then based on “Voucher” series interval. I have setup AX to use voucher number sequence starting with REB followed by 6 digits. Repeat this step for all customers you want in the report.

For the vendor I just choose the “Vendor name” attribute, and filter on “Voucher” number series starting with APREB and 6 digits.

Normal Balance column

I have also set the Normal Balance column to “C” for all rows. This is just a setting to get the sign on the account balance to be reversed (to avoid negitive numbers in the report).

The Column definition

The next step is to create a column definition to be able to generate the report.

For this simple example I have just created one DESC description column (mandatory), one Attribute column to display the Voucher number (will only display on transaction level of the report), one Attribute column for Transaction Description (will also only display on transaction level of the report) and finally a financial dimension column to extract the rebate amounts for a predefined month. I will not go into depth on the column definition other than to point out that the Column Type value defines the input type for the column.

The Report definition

The report definition does not require much input other than selecting the right period, row definition and column definition. Also make sure the Detail level is set to include Financial, Account & Transaction, to be able to drill down on account and transaction level.

Now you should get a result similar to my example report (assuming you have rebate transactions in AX of course)

If you drill down to account level, you could use the report to for example document the rebates for the customer/vendor account.

If we drill down to the transaction level of the report, we can see that the Voucher and Description columns are present as well to give us more detailed information on the transactions:

Great!

Just to be clear, here’s the AX setup

Customer rebates are setup from Accounts receivable paramenters (Accounts receivable -> Setup -> Accounts receivable parameters -> Rebate program):

To define the voucher number series for credit notes and AP, you need to set the AR consumption journal and AP invoice journal settings.

For the AR consumption journal, I have setup a custom number sequence starting with REB followed by 6 digits:

For the AP invoice journal, i have setup a custom number sequence starting with APREB followed by 6 digits:

Now I can see that when I post rebates as credit note, I get the correct voucher number:

And also for the accounts payable voucher numbers:

This allows me to extract rebate vouchers based on customer name/vendor name combined with the voucher number series.

I hope this guide has helped you to be creative and to be able to create your own reports based on the key concepts of this article. The possibilities of Management Reporter is many, and you should explore them! I also hope it helps you and your customers to a more flexible rebate handling in AX 2012!