2

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.

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.

  • This would be shallow learning curve for me. Those who would also be required to build reports would never be able to use this (the business team members I mentioned). On top of which, IT Department wants to put a kibosh on almost all Open Source software due to "security" concerns. – JHStarner Apr 8 '15 at 14:58
  • There have been a large number of studies showing that Open Source has fewer security concerns as the code is open to inspection, unlike closed source code that you can never be sure what is in there. The business team members can use pre-defined reports, possibly generated on demand, or a web/gui interface that pulls the elements together as you have defined. en.wikipedia.org/wiki/Open-source_software_security - Note that python is at Coverty rung 2. – Steve Barnes Apr 8 '15 at 17:38
  • Oh, please don't misunderstand. I wasn't trying to down on Open Source. I personally love it, and try to use it whenever I can. Notice I put security in quotes. I'm just telling you the walls I'm having to face, and I have no pull to change minds. – JHStarner Apr 9 '15 at 18:29
  • @JHStarner - Same boat - Coverty is a useful argument. – Steve Barnes Apr 9 '15 at 19:01
1

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.

  • Does this software only churn out static reports? Meaning, in the finished Reports, is there any interactivity? I.E. our current reports are some pivot tables that connect to graphs. Of course the pivot tables have their selectable filters, which change both the table, and any graphs associated with the table. If the final product is just a static set of charts and tables, this may not be what we need. – JHStarner Apr 14 '15 at 16:23
  • Pixel perfect reports are static, but you can create separate pivot tables with the Pivot Grid component, and apply filtering and customizations, also, you may want to look to the Dashboard component which offers a lot more interactivity. – Miguel Garcia Apr 20 '15 at 15:10

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.