SAP B1 AR Invoice Query Summary Report By Date & Month

[Former Member]

Hi Experts how can i query a summary report of AR invoice  by Date,

here's my example below.

Thank you & Best Regards

[Johan_H]

Hi Erwin,

Could you please explain what all the different numbers in the screenshot mean ?

Regards,

Johan

[Former Member]

Hi Sir Johan,

1-31 number corresponds  with the date & the number below are the quantities ordered  by date,

Thanks,

[Johan_H]

Hi Erwin,

Ok, quantities of what ?

Regards,

Johan

[Former Member]

Quantity of Item Ordered And AR Invoice.

[Johan_H]

Hi Erwin,

Ok, but your table shows customer name, not item name. How do we know to which item the quantity, and price belong ?

Regards,

Johan

[Former Member]

Hi Sir Johan,

In my AR invoice i have my customer name, and in my AR invoice row i have my item and qty.

example:

AR Invoice Dated By: 01/July/2016

Customer A

Item                 Qty

0001                  5 

AR Invoice Dated By: 02/July/2016

Customer A

Item                 Qty

0001                  3 

then in my summary it show

                                   01\July\2016      02\July\2016

Customer Name                   5                     3     

[Johan_H]

Hi Erwin,

Ok, I see. Do you have only one single sales item in your database ?

Let us assume the following example:

AR Invoice Dated By: 01/July/2016

Customer A

Item                 Qty

0001                  5

0002                  3

AR Invoice Dated By: 02/July/2016

Customer A

Item                 Qty

0001                  3

0003                 12

then in your summary it will show

                                   01\July\2016      02\July\2016

Customer A                   5                            3

Customer A                   3                            0

Customer A                   0                            12

How can you tell from the report which item each quantity belongs to ?

Next problem is the price. What happens to this report, if the price changes during the month ?

Let us assume the following example:

AR Invoice Dated By: 01/July/2016

Customer A

Item                 Qty       Price

0001                  5         25

0002                  3         30

On 02/July/2016, the price of item 0001 changes from 25 to 24

AR Invoice Dated By: 03/July/2016

Customer A

Item                 Qty       Price

0001                  3           24

0003                 12          25

then in your summary it will show

                                   01\July\2016      02\July\2016      03\July\2016       Price

Customer A                   5                            0                            0                        25

Customer A                   0                            0                            3                        24

Customer A                   3                            0                            0                        30

Customer A                   0                            0                            12                      25

This example assumes only three items and one customer. Do you see how this report is going to be impossible to interpret, if you have 50 items, and 50 customers, and even worse if your prices change in the middle of the month ?

Regards,

Johan

[Former Member]

Hi Sir Johan,

then how can i do a report like that sir.

[Johan_H]

Hi Erwin,

There are several possibilities:

• You can use a parameter for the item, so it will always only be one item, and you could show only the total sales value at the end (so not the unit price)
• You can use a parameter for the customer, and use the customer column for the item code or item name, and you could show only the total sales value at the end (so not the unit price)
• Instead of item quantities, you could show the total value of invoices per day.
• Instead of item quantities, you could show the number of invoices per day, and the total number for the month, and/or the total value of all invoices in the month.
• Instead of item quantities, you could show the number of different items invoiced per day, and you could show only the total sales value at the end (so not the unit price)

The important question is, what do you need to glean from this report ? What I mean is, what business decision do you need to make, that this report will help you with ?

Regards,

Johan