Infinite Drill-through in a single SSRS report

Grant Paisley of Angry Koala and Report Surfer put me onto this a while back, and I have to admit I’m a bit of a fan. The idea comes from the fact the way that SQL Server Reporting Services (both 2005 and 2008) handles parameters with Analysis Services, and lets you make a report that drills through into itself, deeper and deeper into a hierarchy. Today I did a talk at the Adelaide SQL Server User Group, and mentioned this was possible (but didn’t have the time to demonstrate it properly). If you make a parameterized query in an MDX … Continue reading Infinite Drill-through in a single SSRS report

SSRS: Removing the Navigation link using an Expression

There are times when you want to have a Navigation property of a textbox (typically providing a link to a URL or other report), but you don’t always want the link to be there. Sometimes you just want it to be an ordinary textbox. This particularly applies when you’re using a Matrix, and you don’t want the Subtotal rows to have the navigation links. Previously I’ve blogged about using InScope to control various properties. But the thing that I hadn’t noticed was how to make the link actually disappear. I could make it point at somewhere less useful (like the … Continue reading SSRS: Removing the Navigation link using an Expression

A review: "Learning SQL Server 2008 Reporting Services” by Jayaram Krishnaswamy

A while back I got asked if I’d review this book, but as they could only get me a copy in PDF format, it’s taken a while to get to it. Whilst I like having electronic copies of books, I generally prefer to read them in paper form, and just have the electronic copy for reference. But anyway – this book calls itself “A step-by-step guide to getting the most of Microsoft SQL Server Reporting Services 2008”. At 536 pages, I remember my first thoughts being that it seemed awfully long for the level that it’s aimed at. However, as … Continue reading A review: "Learning SQL Server 2008 Reporting Services” by Jayaram Krishnaswamy

Guide for new Report Builder users

“They’ll probably still just ask me for their reports…” I hear students say when I teach them about Report Builder in SQL Server 2005 Reporting Services (SSRS). It’s a reasonable fear. Their managers have always asked them for reports. Sometimes they just ask for data and then manipulate it in Excel to make it look the way they want – but on the whole, the way it’s done is to send a request to the developers, asking for a new report. And Report Builder should change that. After all, you’ve just spend time putting together the perfect model for it … Continue reading Guide for new Report Builder users

How multi-value query parameters in SSRS are treated by SQL Server

This is something that has bothered for me for a while. In SQL Server Reporting Services, you write a query against a data source, and that becomes the data set for the report. Brilliant. I like to write the query in Management Studio first, and then paste it across. But this doesn’t always work, and multi-value parameters is why. Multi-value parameters are those parameters where you’ve ticked the checkbox to say that you can select multiple options. Like in the image there to the right. Now, in T-SQL, it is not considered valid to write: WHERE mycol IN (@ListOfMyCols) It’s … Continue reading How multi-value query parameters in SSRS are treated by SQL Server

How to format the subtotals of a Reporting Services matrix differently, using InScope

Here’s the scenario. Your SQL Server Reporting Services (SSRS) report has a matrix, showing the sales of each salesperson per year. You put conditional formatting in place so that cells in the matrix that have sales over 1000000 are in a different colour – which is done by putting a formula in the property ‘color’, like =iif(me.value > 1000000, “Red”, “Black”) But then you put subtotals into your matrix (right-clicking on the column/row field to get the option), and you find that all the subtotals are being coloured Red, because together, they add up to more than 1000000. And this … Continue reading How to format the subtotals of a Reporting Services matrix differently, using InScope