Monday, January 28, 2013

Report Building Part 5: Parameters

So we have this lovely SQL Query, but how do we tell it where to look?

The answer ?

Simple, we give it Parameters!

There are built-in Parameters that come with Report Builder but unfortunately they aren't styled specifically for SCCM (mostly of the time/date variety), so we would have to create our own.  The type of Parameters I'm going to focus on is a Dataset Parameter since that allows us the kind of granular control that we are looking for.  The Dataset Parameter has two parts:

The Dataset

This is basically a SQL query to identify a specific section of your database that relates to what you want to find.  In this example, I'm trying to limit my query to only run against specific collections, and I want that option to be available as a dropdown so I can easily choose collections at will.  In the last post I'll take you through the complete creation process in Report Builder, but for the time being this is the query that I'm going to use:

SELECT Name, CollectionID
FROM v_Collection
ORDER BY Name

This query will basically report back all the Collections you have in your environment and order them by name.  So now that we have the dataset figured out, now we need to create:

The Parameter

A Parameter is usually identified with an @ symbol at the beginning of the name.  Within the Parameter would specify which dataset you are using in the Parameter as well as the value and label fields.  This is important as this is how the dropdown is populated.  Looking back at the dataset query above,  you can gleen this info out of it:

Dataset: Dataset1 (this is the name assigned automatically by Report Builder, you can change this if you wish)
Value field: CollectionID
Label field: Name

This will give you a list of collections that are sorted by the collection name.  In the background its linking the name to the CollectionID so you don't have to try to do this by Collection ID specifically.

Once the Parameter is built, you have to add it to your primary SQL Query so the two essentially talk to each other.  This is done using a WHERE statement, like this:

SELECT Manufacturer0, Model0, COUNT(model0) AS 'Count'
FROM v_GS_COMPUTER_SYSTEM
INNER JOIN v_FullCollectionMembership ON v_GS_COMPUTER_SYSTEM.ResourceID = v_FullCollectionMembership.ResourceID
INNER JOIN v_Collection ON v_FullCollectionMembership.CollectionID = v_Collection.CollectionID
WHERE v_Collection.CollectionID = @CollectionID
GROUP BY Manufacturer0, Model0
ORDER BY Model0

Note that in this, I've named the Parameter CollectionID.  Because it is a parameter, you will need to enter it as @CollectionID.

So how does this all fit into Report Builder?  So far we've talked about some general/basic SQL Query building concepts but haven't really touched Report Builder yet.  In the next segment we will go through the Report Building process from end-to-end using the queries that we've built.

Everyone is different when it comes to building SQL Queries, as to how they wish to go about it.  Personally, I've gotten the hang of manually typing out a Query in Notepad first, then slowly cleaning it up with the actual report building as a final step.  If you wanted to check your progress along the way you can use Report Builder or SQL Server Management Studio to test your SQL Query to ensure that you are using the correct syntax.

Once this series is complete I do plan on revisiting a lot of this as I'm still developing my skills as a blogger .. there is a lot that I have to clean up.

No comments: