Hidden Collections in SSRS

Fruit Basket (ref. http://ciaopaolo.com/store/category/fruit-gift-baskets/)If you’re reading this, you’re probably already aware that SQL Server Reporting Services has got some useful collections that can be used in expressions all over the report?  Well, just in case you’re not, I’ll cover not just the hidden ones but all of them.

Collections, huh?

If you’ve got some programming experience then I’m sure this concept is familiar to you.  On the other hand, and in that case you’re new to all this, think of a collection as a, well… a gathering of objects.  Similar to a fruit basket which is a collection of fruit.

Let’s first have a look at the syntax, how can these collections be used?

I’m using SSRS 2012 SP1 but this concept is not new.  I believe all expression will even work in SSRS 2005.

Collection Syntax

Each item in a collection can be referenced through two different methods, both giving the same result.  Let’s continue the fruit basket example.  If SSRS had a collection called FruitCollection, you’d be able to reference the different fruits in that basket through following syntax:

FruitCollection!FruitName

For instance FruitCollection!Lemon will give you the Lemon object, assuming such an object exists in the collection.  That’s the first method.  The advantage of this method is that the expression builder will display a nice popup list of all the items in the collection once you’ve typed the exclamation mark.

The second method of referencing an item in the collection is by using the following Visual Basic syntax:

FruitCollection("Lemon")

This syntax requires you to pass the object name as a string.  As a result of that, you don’t get any syntax checks on the object name, so if you’d typed Limon you would only notice the problem when you actually run the report and get an error.  That’s why I prefer using the syntax with the exclamation mark so that’s the syntax I will be using for the examples further down in this article.

Each object in a collection also has a number of properties that can be consulted.  To continue the fruit basket example, the Lemon object could have a Color property.  To access that property you would use this expression:

FruitCollection!Lemon.Color

Alright, time to have a closer look at the different collections in SSRS!

The Parameters Collection

If you’ve got any experience implementing report parameters, I’m sure you already know this one.  The Parameters collection allows you to access your report parameters.

In case you’re dealing with a multiple-valued parameter, there are a couple of interesting properties you should be aware of, such as the Count and IsMultiValue property.  Careful though: the Count property returns the number of selected values, not the number of available values!

To reference just one of the selected values you can use the following syntax:

Parameters!YourParameter.Value(0)

This expression will return the Value property of the first item in the selected items list.  Note that it’s a zero-based index.

Another interesting function you should be aware of is the Join.  It allows you to join the different selected values together into one string.

The screenshot below shows you some expressions and their result.  The Category parameter has got three available values with two of them selected.

The Parameters Collection

The Variables Collection

The Variables collection gives you access to the report variables.  To demonstrate this, I’ve set up the following variable:

Report Variables

And here’s the expression to access its value:

Variables!MyVariable.Value

The Globals and User Collections

The Globals and User collections contain some useful properties and are provided by default in every report.  These are the items available through the Built-in Fields category in the expression builder:

The Built-in Fields: objects in Globals and User collection

Through those collections you’ve got access to some really interesting properties, such as the RenderFormat.Name property which indicates what output format is being rendered.  I’ve actually mentioned this one before: Hide/Show Items Dependant On Export Format (SSRS)

Okay, time to get started with those hidden collections.  But first, why do I call them hidden?  Simple, because they are not shown in the expression builder Category list, as you can see from the previous screenshot.

The (Hidden) ReportItems Collection

A very commonly used collection is the one called ReportItems.  It allows you to reference the different textboxes on your report.  If you know that each “cell” in a Tablix is actually a Textbox, well, I’m sure you realize that this can be quite useful in certain situations.

Let’s have a look at a basic example.  Say I want to retrieve the list of selected Category values that’s shown in the screenshot in the Parameters Collection subchapter above.

Tip: always give your textbox a clear name when you’re going to reference it in an expression.  Never use the default Textbox666 name.  You can rename a textbox by changing its Name property.

In the following expression you can see that I’ve given the textbox another name, one that indicates what it contains:

ReportItems!txtFilterValues.Value

And here’s the result of that expression:

Using the ReportItems collection

Tip: the above is only possible if the textboxes are not in a group in the tablix.  So the tablix on the left does not contain any grouping, it’s a simple table with five basic rows and two columns.

The (Hidden) DataSources and DataSets Collections

Finally we’ve come to the collections which made me write this article.  Did you know it’s possible to display your dataset query in your report?  That’s right, through the DataSets collection you can access your actual query string!  And through the DataSources collection you have access to some other properties, such as the source type and the shared data source being referenced.

My report has got the following data sources and datasets defined:

Data Sources and Datasets

As indicated by the arrow in the data source icon, AdventureWorksDW2012 is referencing a shared data source while tempdb_embedded is an embedded source in the report.

Here are some examples of possible expressions and their result:

Using the DataSources and DataSets collections

Through the DataSources collection you have access to a Type property on the source.  This property is only given a value in case of an embedded data source.

You also have access to a property called DataSourceReference.  It contains the name of the shared data source being referenced so only contains a value in case of a shared data source.

And through the DataSets collection you can access the CommandText property, which returns your actual query statement.  This can be particularly useful when you’re building your query dynamically through an expression, very interesting for troubleshooting!

There’s also a property called RewrittenCommandText.  When I initially saw that property I thought it would return the statement with any parameters replaced with their actual values.  Well, I was wrong, as the screenshot above indicates.  Apparently this property is used for model-based queries, as mentioned by Robert Bruckner.  I try to avoid report models…

Conclusion

This article introduced the concept of collections in Reporting Services and then went into explaining the use of each of them, including a couple of hidden collections.

Have fun!

Valentino.

References

Built-in Collections in Expressions (Report Builder and SSRS)

What DataSet Query Text Is Executed?

Share

Tags: , ,

  1. MikeB’s avatar

    Is there a way to reference a different property other than “value” on reportitems collection? I want to reference a BackgroundColor property (value) like: =ReportItems!MyTextBox.BackgroundColor. I am using SSRS 2008 R2. Intellisense just pulls the Value property.

    Reply

    1. Valentino Vranken’s avatar

      Hi Mike,

      (sorry for my slow response…)

      Interesting question! Unfortunately my current answer to that would be “I don’t think it’s possible but I’m not 100% sure about that”.

      I found out that the type of ReportItems!YourTextbox is TextBoxImpl but that’s about it, no idea what namespace it resides in and SSRS custom code doesn’t know it by default.

      I also tried going through the Microsoft.ReportingServices.ReportProcessing.ReportObjectModel.ReportItem object by passing the whole ReportItems collection into a function. Worked for the well-known .Value property (through ri(“YourTextbox”).Value), failed miserably for BackgroundColor…

      Possibly you might achieve something by using a custom assembly and passing it the TextBoxImpl object but that means you’d need to figure out in what assembly/namespace it’s implemented.

      So if you ever find out how to get this done, I’m all ears!

      V.

      Reply

  2. eat’s avatar

    Hi there everyone, it’s my first go to see at this website, and piece of
    writing is genuinely fruitful designed for me, keep up posting these
    types of articles or reviews.

    Reply

© 2008-2017 BI: Beer Intelligence? All Rights Reserved