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.
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.
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.
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.
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.
After clicking OK, you should see your new dataset in the report.
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.