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.
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.
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:
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:
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:
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:
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 Variables Collection
The Variables collection gives you access to the report variables. To demonstrate this, I’ve set up the following variable:
And here’s the expression to access its 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:
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:
And here’s the result of that expression:
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:
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:
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…
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.