Is adding Ad Hoc reporting to an application worth the effort?

Question

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?

Answer 1

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.

Answer 2

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.

Answer 3

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.

Answer 4

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.

Answer 5

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.

Answer 6

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.

Answer 7

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.

Answer 8

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?

Answer 9

I work for a government IT system that has ad hoc and canned reporting requirements Additionally, users wanted an ad hoc reporting solution that felt "embedded" within existing applications, provided drill through capabilities to view the record information behind the report output, and provided full access to the query the database. The targeted report products were typically just a web page or MS Excel. Security wanted the reports to integrate with existing JEE security controls.

After failing to find an existing solution on the market, we ended up rolling our own ad hoc reporting tool that we've used for several years. However, it is expensive to maintain and a bear to enhance since it wasn’t designed to extend beyond modest join, filter, and sort use cases.

Some problems we've had similar those mentioned by others:

• Inability of users to understand the datamodel - in particular, users regularly create cross join products via the tool and are confused by the output.
• No ability to display the results on a map even though much of the data has spatial attributes.
• Inability to bookmark and return to ad hoc report selections (this was a flaw in the original tool design).

We're currently evaluating Pull Reports to determine if it can solve these issues. We like how the ad hoc interface gives the users a simplified visual of the data model along with textual descriptions of tables and columns. The fact that the user's filter selections are embedded with the report output reduces concern that results will be improperly interpreted.

As to whether or not all of this has been "worth it": in our case, the ad hoc reporting has been cheaper and easier to manage than having technical staff manage a proliferation of canned reports. However, the question is a bit moot because the canned reports - both with our in-house reporting tool and with Pull Reports - are usually built on top of the ad hoc reporting tool's query / reporting engine. Meaning, the canned reports are just ad hoc reports with pre-configured settings.