phil152003
Board Regular
- Joined
- Mar 11, 2011
- Messages
- 89
I have to produce a report on a monthly basis that updates automatically from a Sharepoint database. My manager has stressed that once a month's figures have been reported, they must not change (even if the underlying data does, eg a record from February is deleted and the figures drop by 1, when it comes to March I must still report for February the figure that was reported originally, even though it is now technically incorrect). I understand his reasoning for wanting this, but as a data guy I find this very frustrating.
It is a complex report based on lots of pivot tables, and I have created a lot of manual VBA macros that refresh each pivot table, and copy and paste value the figures below it, which is what feeds the charts. As I'm the only person that can use VBA, this makes it impossible for anyone else to add any extra charts, as it has become so complex. So currently, I've set up an automated report, but it is a total nightmare adding / amending new charts.
I was wondering if anyone could think of any simpler alternative methods I could incorporate? I would much rather just have the report feed off of live pivot tables, but I just connot think of a way that I could use pivot tables and effectivly "freeze" old, already reported data.
Any suggestions?
It is a complex report based on lots of pivot tables, and I have created a lot of manual VBA macros that refresh each pivot table, and copy and paste value the figures below it, which is what feeds the charts. As I'm the only person that can use VBA, this makes it impossible for anyone else to add any extra charts, as it has become so complex. So currently, I've set up an automated report, but it is a total nightmare adding / amending new charts.
I was wondering if anyone could think of any simpler alternative methods I could incorporate? I would much rather just have the report feed off of live pivot tables, but I just connot think of a way that I could use pivot tables and effectivly "freeze" old, already reported data.
Any suggestions?