One way of creating Ad Hoc reporting I've been looking at is to build a simple ASP.Net app where I create a list of options for the users to select from then it'll dynamically build the query, run it, and present it for them. Then they can download it to Excel to do whatever they want with it. For some of the canned queries where they just basically select the criteria on what records are returned I hope to go abit further and use SSRS to generate reports with more data like charts and custom highlighting based on various metrics.

So if you have some DotNet experience in your shop this might be an option.