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?
Best Answer
There are some dangers with ad-hoc reporting.
Reports tend to proliferate in the resulting combinatorial explosion.
any report so-created has some built-in legitimacy because, well, it is a printed report, so the information must be valid.
You might think that providing reports in this way reduces your burden for supporting people with new reports, but in fact it increases it.
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.