Bad Steve, where was the semicolon?! 😉

Do you have to do this in SQL, or could you use a different tool? SSRS and a Matrix would be much easier, or even Excel and a Pivot Table.

That point where you write out quite a long topic and are about to post, to realise all you've done is rant and not ask a question... Back to the...

Do you have a different collation on the specific column, as this can be different to the database's. Does this return any results?
USE [YourDatabase];
GO
SELECT *

SSRS sometimes has a bad habit of resolving both sides of an expression inside an iif. That means that even though you're checking for a 0, it's still calculating that...

If the login was mapped to a user with the db_reader role, you wouldn't be seeing that error.

I assume that the query is not cross database? Can the...

Ok, for the moment I've implemented a bit of a "dirty" solution. I've created a VB.Net script which I've linked to an Outlook rule. Then, on a little VM we've...