Need a reporting suite, semi-disparate data, small learning curve, web based deliverable

Question

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:

1. Download data from Syndicated data provider into workbook. (Sometimes multiple sets.)
2. Possibly massage and organize data into specific layout and format for report to consume.
3. Run macro in said workbook to refresh Pivot Tables and lists.
4. 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.

1. 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
2. Excel Web Services for SharePoint has already been discussed, and the company does not want to pay for that licensing
3. 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.
4. 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.

Answer 1

I would suggest taking a look at Python + Pandas. Both are free, open source and have a lot of references available including very active communities. Python has a relatively shallow learning curve and Pandas has a lot of capability to handle disparate data.

Reports could be produced on a schedule or on demand if you set up a server to do so.

Answer 2

From what you describe i believe you're looking for something that requires minimal IT knowledge, if that's the case, you can try DBxtra, which lets you create a report from query to final design by pure drag and drop, although if you want to fiddle with the SQL code to optimize it or improve it, you can do so.

Also, with DBxtra you can connect to your Excel files directly as if they were a database, and any change you make to the query or the report, will be published immediatly into the web interface (the Report Web Service).

Regarding security, you can create user groups and give them permissions into specific reports or proyects and restrict users based on their role (Designers or Viewers).

However, you'll still need to download the Excel files from your data providers and possibly curate the data so it can be consumed by DBxtra; if you want to, you can download the fully functional 30 days trial to see if it fits your needs.

P.S.: I'm a DBxtra evangelist.