Hoping someone can help.
Issue:
On a frequent basis a report is run and exported into an Excel format on to the server. The file is always named the same thing, let’s says Hours.xls, and overwrites the existing file to always have the most current data.
We need to view the Hours data in a verity of ways and therefore use pivot tables for that. However I don’t want to re-create them each time a new report is generated. My thinking is to create a second xls that imports the data from the Hours.xls into tab 1. Additional tabs within this second xls would have pivot tables from tab1 in different ways.
Here is what I’ve done. I export an Hours report from the db and posted to the server. 100 records. I created the Second xls called HoursPivot. Via Data > Existing Connections > From Microsoft Query I point to the Hours.xls and import the data. Then I created additional tabs of different pivot tables pointing to tab1. This works great.
I re-run the report from the db, 50 records > export as Excel and overwrite the hours.xls. Open HoursPivot and refresh all tabs. Tab 1 shows 50 records and pivots reflect that.
Re-run the report again from the db, this time 150 records. Overwrite file on server. Open HoursPivot and refresh all tabs. Tab 1 shows 100 records and pivots reflect that. The issue as you can see is that it only viewing the number of records from the original exported report.
Is there a way to have the imported data from a query always import ALL the records from the xls it is pointing too?
Hope that make sense. Thanks for your help,
Cindy
Issue:
On a frequent basis a report is run and exported into an Excel format on to the server. The file is always named the same thing, let’s says Hours.xls, and overwrites the existing file to always have the most current data.
We need to view the Hours data in a verity of ways and therefore use pivot tables for that. However I don’t want to re-create them each time a new report is generated. My thinking is to create a second xls that imports the data from the Hours.xls into tab 1. Additional tabs within this second xls would have pivot tables from tab1 in different ways.
Here is what I’ve done. I export an Hours report from the db and posted to the server. 100 records. I created the Second xls called HoursPivot. Via Data > Existing Connections > From Microsoft Query I point to the Hours.xls and import the data. Then I created additional tabs of different pivot tables pointing to tab1. This works great.
I re-run the report from the db, 50 records > export as Excel and overwrite the hours.xls. Open HoursPivot and refresh all tabs. Tab 1 shows 50 records and pivots reflect that.
Re-run the report again from the db, this time 150 records. Overwrite file on server. Open HoursPivot and refresh all tabs. Tab 1 shows 100 records and pivots reflect that. The issue as you can see is that it only viewing the number of records from the original exported report.
Is there a way to have the imported data from a query always import ALL the records from the xls it is pointing too?
Hope that make sense. Thanks for your help,
Cindy
