I’ve been trying to solve the business issue of end user ad-hoc reporting in a web services world and I'm coming up blank. If you have any ideas on this problem, please post a reply - I'd love to hear from you.

At the moment I’ve tried hooking Crystal XI up to a web service, but the damn thing doesn’t work properly with .Net web services due to a known bug and you have to get the data via a .NET DLL that then calls the web service. Far from ideal but for arguments sake let’s assume it works out of the box.

Now I could have a standard web service called GetCustomers() and want to create a report using that service. In Crystal, I can create some parameters to restrict the selection based on any fields in the resultant data set. So far so good, however when I run the report crystal will quite happily filter the resulting data, but the entire data set is sent down the wire and no server side filtering occurs. So if I had 100,000 customers in the database and 1,000 users all running the report at the same time you can imagine the network bottleneck that woudl occur.

So I started having a think about how to pass the values of the crystal parameters back to the web service in a generic way; maybe I could get crystal to generate a where clause, or add parameters to the web service to restrict the data set. But lets say I want customers with more than 10 orders and with last names between ‘a’ and ‘f’. As you can imagine the web service parameter list would be a nightmare to manage – after all how many parameter combinations are there. In any case Crystal isn’t that advanced in terms supporting data types for parameters and arrays, etc are unusable for the purpose.

In the end it would likely mean having to create specific web services tied to each report as it defeats the idea of ad-hoc reporting in the first place.

My application is web based, but why not give users direct database access with a limited account? Well, firstly in a pure architectural sense the data layers should only be accessed via services and it should be hidden from public view for security reasons; and secondly it’s impossible to guarantee row level data visibility rules (ie customers can only see the orders they have created) if the users have direct table access.

I've come up with some pretty ugly prototyped workarounds to produce a result, but they’re completely unusable in a practical sense and make my application break away from being a web-based solution.

In general terms, one of the features of database centric ad-hoc reporting is being able to join any table to any other table. With web services you lose this, but I don’t mind this flexibility going or having to create lots of web services to support all the different joins, etc that users might want – as long as I don’t lose the flexibility of users choosing their own data selection criteria.

So, the problem comes to this – are there any reporting solutions that work well in a web based solution and that understand an SOA architected solution?

If you've got any thoughts please post a reply (even if it’s “I’ve got no idea”).