Is it possible to get a "Monthly Sells by Client and Products" report?

[Former Member]

Hi,

I am trying to get a report for senior leadership. This report have to display all "Monthly Sells by Client and by Products". Can someone help to figure this out? or to provide some thoughts on the query I should run?

Thanks a lot!

[Johan_H]

Hi José,

Good news and bad news. The good news is, it can be done. The bad news is, it will require a rather complicated query, that will probably not have any drill down capability in the B1 client, and that can potentially result in a gigantic and for all intents and purposes useless report.

I recommend the following:

1. Discuss with / ask senior leadership, what it is exactly that they hope to glean from such a report. Think about it, let us say that you have 100 active items in your database, and 100 active customers. That would result in a report with 10000 rows. I bet senior management is not actually interested in seeing each and every one of these rows. Probably they really only want to see development of sales over the given period of time, and be able to analyze a given customer or item when sales are developing in an unexpected or unwanted way.
2. Build multiple queries / reports with different levels of detail: a report to show monthly sales by customer group, a report to show monthly sales by item group, a report to show monthly sales by customers of a given customer group, a report to show monthly sales by items of a given item group, and similar reports where you can enter combinations of single customer and item groups, and single customers and items.
3. Look into creating stored procedures for the reports. This will make development a lot easier, as you can do it in SQL Server Management Studio.
4. To help you dynamically build your monthly query (so you can simply provide date parameters), look into tsql loops and/or cursors. You can use them to dynamically add months to your queries.

Regards,

Johan