What to do when SharePoint Lists and SSRS isn't enough?

Question

I've developed a complicated InfoPath web form for reporting outages. This system will be maintained by someone with little technical skill, so in a lot of places the form populates its dropdowns from other SharePoint lists (which are much easier to update than editing and republishing the InfoPath form). It works great at getting the data in thanks to property promotion, and basic stuff like filtered views work great. I'd heard great things about SQL Server Reporting Services and its integration with SharePoint lists for doing the complex stuff, but I'm finding that SSRS is really made for consuming already-chewed data and not so great at doing the chewing itself. Here are some examples of things I want to do and find SSRS insufficient due to restrictions on where and when LookUp can be used:

• Each Report has a primary affected system, stored as the ID of the corresponding entry in the System list. Each System is associated with an Organization. Filter Reports by Organization.
• Populate the Organization parameter dropdown with only distinct organizations from the Organization field in the System list.
• Each Report may have secondary affected systems, stored as a semicolon-delimited string of IDs. Parse that and include it in the above filter.
• Each Report may have a semicolon-delimited list of replaced parts. Show part failure rates. Show part failure rates by System type.

These would be fairly easy to solve with SQL or C#, but I can't seem to find a good way to send the data through either of those. Here are the options I'm considering:

1. Create a handful of views on SQL Server that pull data from some SharePoint API (XML?), chew the data, and present it to SSRS in a useful way. I'm having trouble finding a tutorial on building a CLR that consumes SharePoint's web services to get list columns, which is apparently the only way to do that.
2. Change the InfoPath form to send its data to a SQL database instead of a form library. Configure BI to treat the SQL table like a SharePoint list for searching and the simple filtered views. Figure out how to allow users to open existing forms from SharePoint using web browser forms. Figure out a replacement for the workflows. Resolving any other problems caused by leaving the form library for a few SQL tables. Frankly, I'm worried about this because of the work it'll entail and all the problems I've had with BI in the past.
3. Create a handful of views on SQL Server that pull directly from WSS_Content, chew the data, and present it to SSRS in a useful way. Everyone online seems pretty convinced that this is a horrible idea, although it's becoming more tempting as my frustration builds.
4. Find ways to solve these (and future) problems using only InfoPath, SharePoint, and SSRS using subreports, helper lists, and black magic.
5. Find something other than SSRS that can make pretty reports and dashboards while actually doing real data manipulation.

And, I know, shame on me for treating SharePoint lists like a database, but the requirements changed halfway through and (not knowing the limitations of SSRS) I'd assumed it would be easier to update the existing system than to try and rebuild it with a SQL backend. So what's the path of least pain here? Is there another option I haven't considered? For options 1, 2, and 4, I could definitely use some help/code/links.

Update: Apparently in SSRS if you open a report in design view then right click outside of the border of the report and choose "Report Properties" then you get a secret window where you can add VB code to the report. That might be enough for me, although it's definitely not a pleasant development environment and other solutions are still welcome.

Update 2: I've solved the first two problems following the complex steps in this link. I guess the only thing blocking me from option 4 would be the steps and code to put semicolon-delimited IDs into an SSRS parameter. I'm thinking more and more about going with option 2, though, since the difficulty of doing simple things in SSRS would probably be prohibitive as requirements get added.

Update 3: I ended up using something similar to what Alan M suggested by creating a web service that called a stored procedure on SQL, and then creating a second data connection in InfoPath that will call this web service. The "save" button submits data to both the SharePoint list and the web service. This means data is duplicated, but restricting the ways that the user can update the data and writing a good stored procedure should be enough. I've also removed the ability to save the form via the ribbon, and am going to remove the ability for users to delete the form (instead they have a "cancel" option that will remove it from metrics).

Answer 1

I was in a similar situation, and what I did was to use Nintex Workflow to do an "execute SQL" action to write new form submissions out to a database I use purely for reporting purposes. If you don't have Nintex Workflow, ilovesharepoint has an "Execute SQL" SPD add-in for SP2010. The only issue I found is, because of how workflows work; you cannot easily capture a delete from your list; just an add or update.