Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

We have an application which collects a lot of data and has reporting baked in to it. The first iteration was a Crystal Reports integration which worked nicely. Create the report in Crystal Report designer and then import the RPT file into the application. This worked nice but users needed the application to run a report and additionally users couldn't create a report. We added filters, sorters, and groupings so that RPT file was customizable, but they couldn't create one from scratch.

The second interation was a web based solution using SSRS, SSAS, and the report builder tool from Microsoft. This required some database work and some work to get the cubes up and running from the OLTP schema, but in the end, it was much easier to create rollup reports. However, we still have to create the reports using the report builder tool, publish then out, etc. We also added filters, sorters, and groupers to make it "customizable".

In both of these sceanarios we have about 30 to 50 out of the box reports created over time.

Now there is some discussion about adding ad hoc reports so that users can create a report from scratch on the fly. Now our data model is very complex and requires a good working knowledge of it to make sense of it. To do this at a minimum would require a good amount of work to get the data model into a schema that is "more reportable" and easier to understand. I don't think our application is suitable for ad hoc reporting (not worth the effort).

Has anyone had any success with providing ad hoc reporting? What tool set did you use? Did it have an impact on the success of your application?

share|improve this question
add comment

8 Answers

up vote 12 down vote accepted

There are some dangers with ad-hoc reporting.

  1. Reports tend to proliferate in the resulting combinatorial explosion.

  2. any report so-created has some built-in legitimacy because, well, it is a printed report, so the information must be valid.

  3. You might think that providing reports in this way reduces your burden for supporting people with new reports, but in fact it increases it.

  4. It's not just about giving people a reporting capability. It's also about document management: what is the retention and destroy policy for such documents? What are the filing and storage requirements?

For all of these reasons, I believe that, if a custom reporting tool is provided, it must be limited in scope; carefully structured so as not to produce excessive, unsubstantiated and unsupported artifacts; and backed up by a policy that clearly outlines what kind of reports can be generated dynamically, and what reports must be defined and produced formally.

In some cases, adding carefully chosen customization to existing reports (a small number of user-configurable parameters, for example) can reduce the need for a custom reporting tool. Also note that, if this is about performing research against an OLAP database, more reporting flexibility is needed than if you're reporting on an ordinary transactional system.

share|improve this answer
2  
+1 for carefully limiting the structure and scope. Its easy to go overboard and create a monster. –  GrandmasterB Jul 27 '11 at 21:43
    
This discussion has been coming up at my office recently and I have a lot of the same feelings, but they're difficult to substantiate. I don't suppose you know of anywhere to get an in-depth treatment of this subject? For example, what a good report definition and/or retention policy would look like? –  Aaronaught Jul 28 '11 at 1:16
    
@Aaronaught: You start with legal mandates for record-keeping, and work your way back from there. For example, in most (sane) organizations, there is a policy for email retention, because if you hold them for too long or not long enough, the company can be exposed to legal liability. Records that pertain to things like warranties and taxes are very clear cut; other kinds of records, not so much. –  Robert Harvey Aug 7 '11 at 19:36
    
What about the part about increasing the burden as opposed to reducing it - how would you explain/justify that to, say, a CTO or CEO? –  Aaronaught Aug 7 '11 at 19:50
    
@Aaronaught: As you've probably already figured out, ad-hoc reporting tools are not a silver bullet; they do provide some degree of simplification over the process, but people who can't think in terms of sets and joins (i.e. SQL) also seem to have difficulty using their computers for more mundane things. So your support effort simply shifts from writing custom reports (which produce corporate assets that can be leveraged repeatedly) to helping neophytes write their own customer reports (which are all one-shot efforts). –  Robert Harvey Aug 7 '11 at 20:31
show 1 more comment

I have seen a lot of expensive failures. I had a business partner tilt at this windmill for years. Their difficulty was their insistence that "non-technical" people be able to create reports. We built a number of solutions that people were able to learn and use to various degrees of success. Much like you, we started with parameterized canned reports.

Then we made a way to save parameter sets and associate them with different "format" templates, which essentially lets you mix and match your canned reports and publish them to other people. That was actually the most efficient thing we ever did considering it was about two weeks of development time (on top of a basic parametrized canned report system) and they used it with some success for years. It was a very simple UI, but still some users could not really build their own reports, they just couldn't work out what their criteria should be. But since anyone could build a report and share it to someone else, they could just have a co-worker make a report instead of having to go to some MIS team and stand in queue.

We kept trying to improve it though and wasted hundreds of thousands of dollars. Crystal Decisions had a pretty fancy toolkit as an add-on to their crystal reports enterprise product. This was version 9 or 10. Its long since renamed, rebranded by Business Objects but I imagine there is still a version of it. It was pretty expensive, and it gave you a complete web designer for building pretty much any report format. It also had a sample application which was more of a wizard that walked you through modifying an existing report. We'd had success with the "save & share parametrized template" idea so this appealed to us as it took it a step further. Well long story short, we didn't really deliver on it. I think the tool was ok, but what we were trying to do was just too confused and wrong to work. In the end even fewer people were able to effectively use it, and it had annoying limitations as we gradually gave up getting some features to work and our budget ran out.

All this time the business had to keep a staff of MIS developers who did a lot of their ad-hoc reporting. The best they ever got out of our stuff was a little more flexible canned reporting that best case made it faster to develop a new canned report provided there was another existing report that was somewhat similar. If you wanted to somehow integrate a new data source forget it. And mostly, thats what MIS did for them was integrate more and more data sources in a sloppy but very quick-to-market fashion.

Eventually they began heavily using Business Objects - the desktop version of the BI tool. This let you integrate local data with data that you found out about in the online metadata catalog. So you could do both real production stuff for the masses and the quants and managers could keep crunching different sets of data their research led them toward. The skill-set got even more rare, it certainly wasn't something just anyone could pick up and do. Still they were able to get a lot more people using it effectively than they could have ever afforded to hire as dedicated MIS people. MIS staff never was reduced much though, which is telling.

My own impression of this general problem is that you have got to be willing to invest significantly in skill development for the people that you imagine using this tool, and you have to accept that not all your staff is going to ever get there. And if they can't spend a couple weeks learning a BI platform, they will never be able to get the most out of any tool that you give them. Some people, for whatever reason, just never seem to get basic ideas such as outer joins. Huge classes of problem sets will never be within their reach to solve with any tool because they do not get far enough into it to understand at a conceptual level what they are really trying to ask the computer to do. That isn't to say they "can't" learn that, just that a lot of them never will.

share|improve this answer
add comment

We are facing this situation currently. At this point instead of an ad-hoc reporting interface we are running a trial with using Excel and Power Pivot. We integrated it with the Excel toolbar and allow users to import the data directly into and build reports using this. We found that many of these ad-hoc reports where one offs that where needed at a specific time to answer a specific question.

At this point it is working out well, a little training and hand holding was required up front but it is being used by the finance department so they of course are most comfortable in excel.

By the way if you want to talk about some of the implementation details let me know.

share|improve this answer
    
+1, office in many ways is the ultimate reporting platform –  Wyatt Barnett Jul 28 '11 at 2:28
add comment

I've done a couple over the years. As you said, with databases that rely on certain domain knowlege, it can get very tricky. As such I (or the team I was on) developed them without using a reporting tool. They were frankly too much trouble to work with, trying to get all the necessary logic into them. You end up fighting them as much as they help.

Users really like being able to make their own reports, so I'd say it was definately worth it if you have the time to develop such a system.

share|improve this answer
add comment

In a similar scenario on a project I'm managing we offered the customer to add a datawarehouse with an OLAP solution on top. To keep costs down we chose PostgreSQL as the DWH database and Pentaho Enterprise as the BI/OLAP analysis tool - we chose the paid version because the OLAP tool is much more user friendly.

Exactly as you said, you need to do your analysis to design a data model that is suitable for the users' needs. It took us three months time from requirements to deployment, and at first there were some glitches to fix, but in the end the customer is very satisfied with the results. Users now create their own analysis and sometimes use them as reports (exporting them to PDF). There is also a feature that allows to create simple enough ad-hoc reports, but at least for now the analysis tool is more than enough for their needs.

share|improve this answer
add comment

The short answer is that it can be.

I worked for a company in the mid 90s that built software that does just what they're asking for. We had a good market in the pharmaceuticals industry, where clinical trials meant lots of querying and reporting--so much so that cutting out the IS middle men made sense.

That company was swallowed by another, which in turn was swallowed by another that didn't know or care what to do with the product.

Still, the (oxymoronic) world of Business Intelligence relies in part on allowing end users the ability to define or at least refine queries to data systems. There are tools out there for making this somewhat easier for the user. Business Objects (now part of SAP) was king in this realm. Then they bought Crystal. Then SAP bought them. Their current offering in this realm is SAP Crystal Interactive Analysis.

It's a big effort--the tools generally require a lot of work setting up your metadata and all that. It's really a question of do your users REALLY need this--what will the ROI be?

share|improve this answer
add comment

The broader the domain and the size of the companies you have as clients tends to lean towards customizations, data integration, and ad hoc reporting. It's going to come down to cost.

Most companies discourage customizations so they charge high fees for this service. Programmers tend to see this things as unecessary, but when you can save time and make it easier for a few hundred users, the savings add up.

For reporting, this creates an opportunity to charge for additional training. Ad hoc reporting can have an additional fee.

Your job as the developer will get tougher. Most places I've ever worked that had 3rd party software had custom reporting. It was easy for some because they had simple data structures. The larger/more complex ones needed custom reporting because that's the way they run their business. If they wanted to do things the same as everyone else, they wouldn't have hired me. I've had to put a few DevExpress Reporting questions on SO.

It's up to sales and marketing to see if there is a need. Not "Ad hoc reporting would be cool", but "I would buy your software because it has ad hoc reporting." You just need to make everyone aware of the technical investment required.

share|improve this answer
add comment

My solution is to get your application to generate some basic spreadsheets and let the users play with Access until they see what they want.

A more sophisticated approach would be to write an access/vbscript program to "refresh" the base data so allowing users to reuse there customizations.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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