General Ledger
Active Member
- Joined
- Dec 31, 2007
- Messages
- 460
Dear All,
I have a report in Access 2007 that contains a few pivot tables on one page. The pivot tables all use the same table for their source data, Open Sales Orders. Each pivot table summaries dollars based on different fields: on supplier, product, customer, etc. All of these work fine and the report is fine.
My problem is I want to generate in the same report but include one page per salesperson. Each page will use the same pivot tables but filtered by each salesperson. I want to end up with one page that has the pivot tables summarizing all data, which I have now. Then one page summarizing data for each salesperson. Each salesperson page will have the same pivot tables.
I am quite familar with pivot tables in Excel. I see the pivot tables in Access also allow for a filter field. I added the Salesperson field as a filter field of all the pivot tables. I left the filter field selection as All.
I added a grouping to my report by salesperson. In that section, I copied all the pivot tables and the salesperson field so to identify the person.
The report generates the correct number of pages, each labeled with the salesperson name. However, the pivot tables are not being filtered on each salesperson. Every page has the same data.
Does anyone have any suggestions?
Do I have to create pivot tables filtered by each salesperson and add them individually to the report? At any time, some salespersons might not have open sales orders so there would be no data and I would want to skip pages for them. Also, when we add or subtract a salesperson, I would have to remember to change the report.
I am at best an intermediate user of Access. Therefore, please be detailed in your responses.
Thank you very much,
GL
I have a report in Access 2007 that contains a few pivot tables on one page. The pivot tables all use the same table for their source data, Open Sales Orders. Each pivot table summaries dollars based on different fields: on supplier, product, customer, etc. All of these work fine and the report is fine.
My problem is I want to generate in the same report but include one page per salesperson. Each page will use the same pivot tables but filtered by each salesperson. I want to end up with one page that has the pivot tables summarizing all data, which I have now. Then one page summarizing data for each salesperson. Each salesperson page will have the same pivot tables.
I am quite familar with pivot tables in Excel. I see the pivot tables in Access also allow for a filter field. I added the Salesperson field as a filter field of all the pivot tables. I left the filter field selection as All.
I added a grouping to my report by salesperson. In that section, I copied all the pivot tables and the salesperson field so to identify the person.
The report generates the correct number of pages, each labeled with the salesperson name. However, the pivot tables are not being filtered on each salesperson. Every page has the same data.
Does anyone have any suggestions?
Do I have to create pivot tables filtered by each salesperson and add them individually to the report? At any time, some salespersons might not have open sales orders so there would be no data and I would want to skip pages for them. Also, when we add or subtract a salesperson, I would have to remember to change the report.
I am at best an intermediate user of Access. Therefore, please be detailed in your responses.
Thank you very much,
GL
