Log in  \/ 
Register  \/ 


Customizing SCCM 2012 reports to support multiple values in a parameter

In our recent project, I’ve got a request to let custom reports to support multiple values in parameters. It seemed to be a little effort but I spent many hours to make it works.

Many recommendations advice to join the parameter’s multiple values in one using a function JOIN in SQL Reporting Services:


Then using Transact-SQL IN statement check if the variable of the SQL query is among the selected values.

Unfortunately, 'as is' it didn’t work for me.

Let’s customize the default SCCM report ‘Software 10B - Computers with a specific custom-labeled software title installed’. We want to let Label 1 has multiple values.

First, let’s save the report using a new name like ‘Software 10B - Computers with a specific custom-labeled software title installed (custom)’. Now we will make some changes.

Select Parameters, Tag1Name, Properties. Check Allow multiple values.


Then, go to Datasets, DataSet0, DataSet Properties. Select Parameters.


Click on fx for Tag1Name and replace the text with ‘=Join(Parameters!Tag1Name.Value,',')’. Note that the example for Join function loos very similar.


And finally, we have to customize a query. In Dataset Properties click on Query. Scroll down and check the last strings of the query after WHERE.


Please note the text:

(@TempTag1Name IS NULL OR TG1.TagName = @TempTag1Name OR TG2.TagName = @TempTag1Name OR TG3.TagName = @TempTag1Name) AND
(@TempTag2Name IS NULL OR TG1.TagName = @TempTag2Name OR TG2.TagName = @TempTag2Name OR TG3.TagName = @TempTag2Name) AND
(@TempTag3Name IS NULL OR TG1.TagName = @TempTag3Name OR TG2.TagName = @TempTag3Name OR TG3.TagName = @TempTag3Name)

The variable @TempTag1Name contains the parameter’s values for Label 1. As you could mention, the condition is actually met not only when TG1.TagName(Label 1) = @TempTag1Name but also when TG1.TagName(Label 2) = @TempTag1Name or TG3.TagName(Label 3) = @TempTag1Name. The same story is with TempTag2Name and TempTag3Name. In my opinion, it is a bit confusing because the query returns results not only when the value of Parameter 1 equals Label 1 but also when value of Parameter 1 equals Label 2 or Label 3. I offer you to fix it as I did:

(@TempTag1Name IS NULL OR TG1.TagName = @TempTag1Name) AND
(@TempTag2Name IS NULL OR TG2.TagName = @TempTag2Name) AND
(@TempTag3Name IS NULL OR TG3.TagName = @TempTag3Name)

Now it’s time to add support for multiple values for Label 1. The multiple values for Label 1 are defined in @TempTag1Name that can contain the multiple values after JOIN operation.

For instance, if we select three values for Label 1: Apple, Orange, Pear, the TempTag1Name will be equal ‘Apple, Orange, Pear’ with comma as a separator.

Now we have to add IN operation to support the multiple values.

(@TempTag1Name IS NULL OR TG1.TagName IN (@TempTag1Name)) AND
(@TempTag2Name IS NULL OR TG2.TagName = @TempTag2Name) AND
(@TempTag3Name IS NULL OR TG3.TagName = @TempTag3Name)

The final edition:


Save all the changes and report.
In my test system I marked Microsoft .NET Framework 4.0 and 4.5 software with labels 4.0 and 4.5 correspondently.


Let’s test the report with the only value for Label 1. Our customized report presents the result. Voilà!


Let’s test it with two values - 4.0 and 4.5 using Ctrl key to select several items. Oops, there is surprisingly no result.


I spent many hours trying to realize what the problem was. Unfortunately, I still guess why IN operator does not work in the report query. Maybe someone knows the answer but not me.
Fortunately, IN operator also works with results of SELECT. Remember our ‘Apple, Orange, Pear’ string. Transact-SQL language doesn’t support lists, collections, etc. We have to use a function or stored procedures to get a number of objects. We can create the function but luckily, your SCCM database includes one named fnSplitString.


Now our piece of the query code looks like:

(@TempTag1Name IS NULL OR (TG1.TagName IN (select Data from dbo.fnSplitString(@TempTag1Name,',')))) AND
(@TempTag2Name IS NULL OR TG2.TagName = @TempTag2Name) AND
(@TempTag3Name IS NULL OR TG3.TagName = @TempTag3Name)


The next try.


Now it works as expected.

Please be aware that in some circumstances your SCCM database lacks fnSplitString function. You can easily create it using the following code:

USE [YourDababaseName]
CREATE FUNCTION [dbo].[fnSplitString]
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
    WITH Split(stpos,endpos)
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
        Data = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split

Then don’t forget to grant the required rights:
GRANT SELECT ON dbo.fnSplitString TO public

Please also note that in my sample I used comma as a separator in JOIN and fnSplitString. You can choose any other symbol if you suspect that the parameter’s values could include commas.

My custom report is available here.

Categories: configuration, System Center 2012, System Center 2012 Configuration Manager, SCUTILS BLOG

Marat Kuanyshev

Leave a comment

You are commenting as guest.

This is the default dialog which is useful for displaying information. The dialog window can be moved, resized and closed with the 'x' icon.