Category Archives: 6192

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 query in SSRS, you use the STRTOMEMBER or STRTOSET function to handle this. But the MDX has no other indication of what dimension, hierarchy or level is being passed in. If you grab the children of whatever you’ve passed in, you can easily put this on the Rows axis and get one level down. Passing the UniqueName of whatever you’ve just provided back in as the next parameter, and you have infinite drill-through.

Look at the following MDX query:

MEMBER [Measures].[NextLevel] as StrToMember(@SomeDate).Hierarchy.CurrentMember.UniqueName
MEMBER [Measures].[NextLevel Name] as StrToMember(@SomeDate).Hierarchy.CurrentMember.Member_Name

NON EMPTY { [Measures].[Internet Sales Amount], [Measures.NextLevel], [Measures].[NextLevel Name] } ON COLUMNS,
NON EMPTY { (StrToMember(@SomeDate).Children ) } ON ROWS

FROM [Adventure Works]

You see that I provide the UniqueName and Member_Name properties in known columns, so that I can easily reference them in my report. You’ll also notice that nowhere do I actually indicate which dimension I’m planning to drill down on, or to which hierarchy the @SomeDate parameter refers. I have suggested it’s a date, but only in name. At this point I also make sure that the Report Parameter is not restricted to values from a particular query, and I hide it from the user. I’m going to be passing in UniqueName values, which aren’t particular user-friendly.

If I start with [Date].[Fiscal].[Fiscal Year].&[2003], then my NextLevels will be [Date].[Fiscal].[Fiscal Semester].&[2003]&[1] and [Date].[Fiscal].[Fiscal Semester].&[2003]&[2]. This then continues down as far as I want it to go. I could always put a condition on my Action to pick up when there are no more levels, and potentially start down a different hierarchy. After all, I can always use a bunch of other parameters in the WHERE clause to slice the cube in other ways first, for placeholders. It really just comes down to MDX creativity to investigate different ways of drilling through the data.

Please bear in mind that other people may well have achieved the same sort of thing using a different query – I’m just posting what has worked for me. Hopefully by doing this, you can avoid making five drill-through reports just because your hierarchy has five levels. This might just remove 80% of your reporting effort!

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 current report), but I didn’t want the cursor to change.

And then my friend (and fellow MVP) Jason Strate told me that if you make the Expression give the result of “Nothing”, then this does the trick.

So try something like: =iif(inscope("matrix1_SalesPersonID"), “”, Nothing)

Thanks, Jason!

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 I went through it, I found that this was largely down to the sheer number of screenshots. This is great, and particularly for an eBook. It means that someone trying to learn about a particular screen can easily click the “Next Page” button until they see that screen.

As for whether or not I’d recommend this book to someone learning Reporting Services – well, it depends on your style of learning.

If you want something deeper, that explains more about the choices you’re making, and the paradigms you should be considering, then maybe this book isn’t for you.

But if you want to jump into SSRS, looking at example after example, to get a hands-on feel for the product, then this book is great. It feels like labs when you’re reading it, and that’s going to suit a lot of people.

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 to use, so that your users see the right things to query.  But still there’s a fear.

So – if you’re trying to get people to use Report Builder, what can you do?

Well, for starters, use it in front of them – preferably on their own machine. Show them just how easy it is to get at the data.

And secondly, create a favourite for the Report Builder How-To Topics page in Books Online (on the web). And show it to them. If they want to align the values within a cell or text box, they can follow the link. If they want to format a date field, they can follow the link. After all, they don’t really want to have to wait for you to come and tweak their reports – they do want to do it themselves.

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.

multi-value 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 just not the way IN works – in this scenario, it would considered the IN list has a single value, and use that. Don’t bother trying a comma-separated string, it will just see if mycol is that same comma-separated string.

But if you’re using SSRS with parameters that can take multiple values, then this is exactly what you do. You have to write your query as:

SELECT        ProductSubcategoryID, Name
FROM            Production.ProductSubcategory
WHERE        ProductCategoryID IN (@Category)

And the system understands that @Category is a multi-value parameter and passes it to the server in an appropriate way.

At this point, I want to quickly advertise SQL Profiler. Make it your friend, so that you can find out how things are working against your database. Particularly with Reporting Services – it can be very good.

SQL Profiler tells me that when I use a single-value parameter in Reporting Services, sp_executesql is being used, passing parameters to a fixed string. Terrific – this is what I want.

IN_clauseBut when I use multi-value parameters, I don’t get this behaviour. Instead, it turns out that SSRS passed across a query with the parameters pre-inserted – it used dynamic SQL!

Now, it works fine – but I don’t like this. It could easily bloat the plan cache, and I just feel like it’s the wrong way to do it. I don’t know a better way – perhaps something could be done through enhancing the table-valued parameters feature of SQL Server 2008 – but I’m not going to hold my breath on that – currently it would have to have a table type declared, and then do something like “WHERE mycol IN (select col1 from @tvp)” – but that would have other problems associated with it.

I’m not suggesting that you don’t use multi-value parameters – but just understand what’s going on with the queries.

UK SQL MVP Simon Sabin (and Harpendenonian) posted something around this around filters, and has recently posted more about query parameters, but I thought I’d write some more, as it’s been something I’ve been meaning to blog about for a while.

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 becomes a problem, because you can’t put different formatting on the subtotal as you have on the rest of the inner cells of the matrix. That is, without having a slightly trickier formula. The function you want is INSCOPE.

EditGroup First look up the name of the group, by right-clicking on the field that contains the row and choosing “Edit Group”. That might tell us that the group is called ‘matrix1_SalesPersonID’, or something similar. You can rename it if you need to. (And yes, I know… in real life you’d be using the SalesPerson’s name – this is only an example)

Now, change your formula so that there’s a different criteria to set the formula if it’s within the group, rather than outside the group (as is the situation for the subtotals).

=iif(inscope(“matrix1_SalesPersonID”), iif(me.value > 1000000,”Red”,”Black”), iif(me.value > 50000000, “Red”, “Black”))

Now, within the matrix proper, the fields will be Red if they are over 1M, but in the subtotals at the bottom, they will be Red only if they’re over 50M.

subtotal_colours To do the same for the columns, just look up the group name, and consider doing something like:

=iif(inscope(“matrix1_SalesPersonID”) and inscope(“matrix1_Year”), …

So now we have a matrix which has different conditional formatting for the subtotals, compared to the ordinary values within the matrix. You’ll notice in the image here that the subtotal of $32M isn’t red, even though it would’ve been without the InScope function.