Log in  \/ 
x
x
x
Register  \/ 
x

29
June
2015

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:

=Join(Parameters!YourParameter.Value,',')

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.

screenshot1

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

screenshot2

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

screenshot3

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.

screenshot4

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:

screenshot5

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.

screenshot6

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

screenshot7

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.

screenshot8

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.

screenshot9

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)

screenshot10

The next try.

screenshot11

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]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[fnSplitString]
(
    @String NVARCHAR(4000),
    @Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
    WITH Split(stpos,endpos)
    AS(
        SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
        UNION ALL
        SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
            FROM Split
            WHERE endpos > 0
    )
    SELECT Id = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
        Data = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
    FROM Split
)
GO

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.