I'll lay out the specifics of my current situation.
We have > 1000 reports that we manage on a regular basis. Current tools are built in Excel with VBA running the interactivity of the workbook (There are 3 different workbooks as part of a published set). The publishing process is another VBA workbook that does some validation in the previous files, then pushes them out to a SharePoint location. The data that populates these reports is pulled from Syndicated Data providers, and saved as, You guessed it, Excel workbooks.
So someone on the team I support has to:
- Download data from Syndicated data provider into workbook. (Sometimes multiple sets.)
- Possibly massage and organize data into specific layout and format for report to consume.
- Run macro in said workbook to refresh Pivot Tables and lists.
- Open up a third Macro enabled workbook that will pull and validate multiple reports, then publish them out to a SharePoint location.
Issues that have arisen:
- If any bugs are found, or changes (cosmetic or analytical) are needed, the template of the report gets fixed, then large sets of reports have to be rebuilt from that template so they are current (Meaning no proper way to push out fixes or changes. It is all by hand)
- Users have to come to the SharePoint site to download the reports
- Organization into specific departments or regions is accomplished by futzing around with a single SharePoint Document library, followed by filtered list views in SharePoint Web Parts.
- We are fast hitting capacity in both space on the SharePoint Site, and man-hour-maintenance time, as there are far more reports than team members to refresh, update, and publish
Here are the caveats before answers are provided.
- Whatever the solution is, IT department involvement needs to be an absolute minimum. Maybe IT is involved in setup, but support and maintenance are kept up by myself (in team/house developer), and the business team-mates
- Excel Web Services for SharePoint has already been discussed, and the company does not want to pay for that licensing
- We do have Cognos available in the company, but IT is very adamant that they handle any and all table and report creation. The don't let control of that tool outside of their department.
- Software needs to be able to maintain semi-disparate data. Meaning, all of these reports are in roughly the same format (Location, Time, Product, and measure values). However locations do not match up from one set of data to the next (i.e. "MidWest" vs "Midwest Geo"). Same goes for all "dimensions" if you will. So some sort of homogenization is needed.
The end goal is to have one time development of these reports, which are able to consume and display any set of data, as long as the data is in the same layout. If issues or changes arise, they are made to the single report "shell", and everyone continues about their business. In turn it would be web delivered so there wouldn't be a need for updating and publishing, and the end-user would have the option to download the reports into Excel, Access, or PowerPoint if needed.
I do not have any budget established for this, because they don't know I'm researching at the moment. I see a train wreck happening in < 1 year's time on the current "Excel Platform", and I am trying to avoid that. Suffice to say, the company doesn't like spending money, so affordable is a bonus.
Thanks in advance for answers given, and I apologize for the wall of text.