Report with a Selectable Collection Parameter (SQL)

Purpose: Create a Report that allows a user to select the limiting Collection from a drop down list

When you are creating more complex reports with the Report Builder, you will begin to use different kind of parameters with queried values. One of the most common SCCM parameter is the Collection parameter. This guide is valid when you need create your report and let a user to limit it to a single collection.

(1/4) Create the report you want using the report builder wizard

First, you need to create the report using the report builder new report wizard. This case begins by creating the report without the collection restriction and after that query works, add the query restriction as shown below.

(2/4) Create support report Parameter: UserSIDs

To add the dropdown list of collections to restrict the query, we need to create few support elements listed below. Start by creating a new report Parameter (Right-click on Parameters -> Add Parameter…)




Click Specify values, click ADD, click on the Fx button at the right side of the row to create an expression for this value. Put this in the expression area.

Expression
=SrsResources.UserIdentity.GetUserSIDs(User!UserID)


Click OK, and click OK to close the parameter creation (you don’t need to do anything in the Advanced tab). Now your UserSIDs parameter is visible in the report builder.

(3/4) Create the drop down report dataset

You need to create additional dataset, and set this query to produce results in that dataset. Right click on datasets and select Add dataset… -option to display the window below. The Support Parameter created previously in part (2/4) will be used in this query as shown below.

Notice: Don’t forget to add your datasource in this dataset! In the picture the selection empty, it is not supposed to be empty if you want this to work! See my post “How to add a new datasource for sql report service” on how to create a new datasource if the list is empty for you.

Remember to click ”Refresh Fields!” after you have set a name, datasource and query for this dataset (Refresh Fields button will automatically create the required values to the FIELDS and PARAMETERS tab of this window from the query which you copy pasted here. Click OK, we are done with this.

1
2
3
SELECT CollectionID, CollectionName=Name, NameSort=CollectionID+' - '+Name 
FROM    fn_rbac_Collection(@UserSIDs) 
ORDER BY 2
SELECT CollectionID, CollectionName=Name, NameSort=CollectionID+' - '+Name 
FROM 	fn_rbac_Collection(@UserSIDs) 
ORDER BY 2


After clicking OK, you should see your new dataset in the report.

(4/4) Create support report Parameter: COLLID



Enjoying the results

Now that you have created two support parameters (UserSIDs, COLLID) and a secondary data set (DataSet_Get_Collection_Names) you are all set to get the dropdown list restriction for your main query, what this was all about. You need to alter your main query like the following (this might vary, depending what kind of query your main query is). This is the key factor for your query to actually respect the collection restriction we are trying to do.

1
2
3
...
WHERE (dbo.v_FullCollectionMembership.CollectionID = @COLLID)
...
...
WHERE (dbo.v_FullCollectionMembership.CollectionID = @COLLID)
...

4 Thoughts on “Report with a Selectable Collection Parameter (SQL)

  1. satyen on September 26, 2013 at 08:57 said:

    I am sorry I am very new with SQL report builder. I tried following your steps. But could not follow this step. “You need to create additional Data Source, and set this query to produce results in that Data Source. The Support Parameter created previously will be used in this query.”
    If possible please provide me a step by step guide to do this particular action.

  2. satyen on September 26, 2013 at 09:01 said:

    Basically I want to modify the report provide by this “sherry Kissinger” on the myitforum.com website. “http://myitforum.com/cs2/blogs/skissinger/archive/2008/06/08/report-on-local-administrators-group-membership-updated.aspx”
    My company needs to have the option of adding the drop down to choose the collection on which this report will run.
    Hope you can help.

  3. Jyri Lehtonen on September 26, 2013 at 13:39 said:

    Hi satyen. First let me apologize that the screens are in Finnish, was too lazy to change language back when I did this 🙂 Luckily everything is in the same place in an English UI. I urge you to change to English if you try to follow my steps with this additional guide.

    “You need to create additional Data Source, and set this query to produce results in that Data Source” This was indeed mistyped. What I actually meant was that “You need to create an additional *Dataset* and set the query of that dataset.”

    I will make some changes to this post soon, so that It is more understandable for you (and for me).

  4. Jyri Lehtonen on September 26, 2013 at 14:28 said:

    Changes have been made for this post on September 26th. No additional changes will be made anymore unless seemed necessary. Was this post now more helpful for you?

Leave a Reply

Your email address will not be published. Required fields are marked *


− three = one

Post Navigation