I have a report I am designing. It is basically a payroll/commission report. Here is the challenge...
For a given item (large ticket) sold, there are multiple types of commissions paid out. I have all the data, percentages, etc. working perfectly. The challenge is that on any given report, I must show YTD, current period, and future earnings based on sales not yet closed/completed.
Additionally, a partial payment may have been made in the current period against future earnings down the road. Once again, all of this is being captured fine.
The challenge is that a single line of data - across the various joins - can have a commission item from a prior period and a future period.
My plan was to have a report with a 3 distinct sub-reports (sales rep main report) and 3 subreports:
1) YTD detail
2) Current period detail
3) Future earnings
The data for each is basically the same. I want to use the same detail report for each sub-report and simply alter the recordsource based on the current pay period and the sales rep data in the primary (top-level) report. Unfortunately, there doesn't seem to be a way to alter a series sub-reports that use the same underlying base report.
My alternate method - where I am currently leaning - is to run a process and create a temporary reporting table prior to running the report. I can create a query of all the necessary data - YTD, current, and future earnings. As I loop through the DB, I can take the data from a given line and create any required records (ytd data, current period data, future earnings data). This way, a single line item that contains a preclose commission and a future commission would result in 2 reporting records.
I would then run a straight report with sorting and grouping to keep my YTD, current, and future data separate.
Any ideas on the sub-report and how to manage that?
Thanks.
Matthew Moran (career blog and podcast below)
Career Advice with Attitude for the IT Pro
For a given item (large ticket) sold, there are multiple types of commissions paid out. I have all the data, percentages, etc. working perfectly. The challenge is that on any given report, I must show YTD, current period, and future earnings based on sales not yet closed/completed.
Additionally, a partial payment may have been made in the current period against future earnings down the road. Once again, all of this is being captured fine.
The challenge is that a single line of data - across the various joins - can have a commission item from a prior period and a future period.
My plan was to have a report with a 3 distinct sub-reports (sales rep main report) and 3 subreports:
1) YTD detail
2) Current period detail
3) Future earnings
The data for each is basically the same. I want to use the same detail report for each sub-report and simply alter the recordsource based on the current pay period and the sales rep data in the primary (top-level) report. Unfortunately, there doesn't seem to be a way to alter a series sub-reports that use the same underlying base report.
My alternate method - where I am currently leaning - is to run a process and create a temporary reporting table prior to running the report. I can create a query of all the necessary data - YTD, current, and future earnings. As I loop through the DB, I can take the data from a given line and create any required records (ytd data, current period data, future earnings data). This way, a single line item that contains a preclose commission and a future commission would result in 2 reporting records.
I would then run a straight report with sorting and grouping to keep my YTD, current, and future data separate.
Any ideas on the sub-report and how to manage that?
Thanks.
Matthew Moran (career blog and podcast below)
Career Advice with Attitude for the IT Pro
