I have used Commission Plan for Microsoft Dynamics GP from EthoTech to handle salesperson commissions for a long time — over 10 years. It’s a great piece of software and works really, really well. There’s just been one feature I really wanted to have: a way to automatically send a nice, detailed SSRS report to the Sales Manager once commissions were processed.
A few years ago, I started a project to get all of the Report Writer/Word Template forms for Receivables Management, Payables Management, Sales Order Processing, Purchase Order Processing and Field Service to use SSRS. Then I could use views as the data source, add as many tables as I wanted, etc. This report was one of the last stragglers.
Out-of-the-box, the report isn’t bad, but it could look a lot better:
Plus, there is some custom information specific to the organization that is shown on the report. Commission Plan is also used to enter, track and process Point of Sale data from distributors.
This is the report I designed for the Sales Manager:
So, how could I use my fancy, new SSRS report and make it run on a schedule — only when commissions were processed?
The ETI28914 table in the company database.
This table keeps a record of every time the process commissions routine is run. There is a date column and a time column for both the start and the completion.
All I needed to do was set up a SQL query in Rockton Software‘s Dynamics Report Manager as the batch report data source to look for the record in the table that is equal to today’s date. Then just pass the automatically generated Commission Run ID field to the report.
Now, when commissions are processed, the nifty SSRS report gets emailed the same night! Sometimes it’s great to solve a business problem with no custom code, no customizations, just using the tools you have.