cmcreynolds
Active Member
- Joined
- May 21, 2015
- Messages
- 295
Hello everyone - just needing advice here.
I have been tasked to create a 'simple' four-page report on a quarterly basis. Each page will summarize data coming from a different data source (Pg 1 is via MS Access querying our Customer Management Software's database, Pg 2 is a spreadsheet from our email server, Pg. 4 is from a different department's Access database, etc.).
My initial thought is to save monthly data in a folder grouped by the page (so the data structures of each spreadsheet are the same), then with Power Query, get the data into the Data Model and summarize each page with its own Power Pivot Table.
But, I have two questions that arise:
1. I would like to export all four of my pages as one PDF. So, can I put the pivot tables all on one worksheet? The number of rows in each pivot table won't change (I'll be summarizing by our membership types). Or is there a way in Excel to put different worksheets together and export as a PDF?
2. Since each page/pivot table will be summarizing data from different sources, can I still have a "master slicer" (by Fiscal Quarters on a separate worksheet, perhaps?) that controls the different date fields? (Do I just make sure they're all related to the same calendar table?)
3. Should I even be doing this in Excel? Should I trying querying in MS Access 2013?
Thanks again for your help, I'm just trying my best to create this report and make it work easily for others in my organization.
I have been tasked to create a 'simple' four-page report on a quarterly basis. Each page will summarize data coming from a different data source (Pg 1 is via MS Access querying our Customer Management Software's database, Pg 2 is a spreadsheet from our email server, Pg. 4 is from a different department's Access database, etc.).
My initial thought is to save monthly data in a folder grouped by the page (so the data structures of each spreadsheet are the same), then with Power Query, get the data into the Data Model and summarize each page with its own Power Pivot Table.
But, I have two questions that arise:
1. I would like to export all four of my pages as one PDF. So, can I put the pivot tables all on one worksheet? The number of rows in each pivot table won't change (I'll be summarizing by our membership types). Or is there a way in Excel to put different worksheets together and export as a PDF?
2. Since each page/pivot table will be summarizing data from different sources, can I still have a "master slicer" (by Fiscal Quarters on a separate worksheet, perhaps?) that controls the different date fields? (Do I just make sure they're all related to the same calendar table?)
3. Should I even be doing this in Excel? Should I trying querying in MS Access 2013?
Thanks again for your help, I'm just trying my best to create this report and make it work easily for others in my organization.