Adding a Prompt Value to the Title of a SCCM Report

Little things do make a difference and this is especially true when it comes to SCCM reports. Oftentimes in my report writing sessions, I don’t have time to show folks all of the little things, so in this blog post I will demonstrate how adding a prompt value to the title of an SCCM report can make a world of difference. By the way, you might want to review last week’s blog post, “How Can I Add a Title to an SCCM Report,” for additional information.

I’ll use one of Enhansoft Reporting’s recently updated reports, Count by Chassis Types, in my example. First, let’s look at a modified version of this report. You’ll notice that I haven’t added a prompt value to the title yet.

Report 1

Report 2

These reports give me all the information I need to know about the number of desktops, laptops, etc. at two of our sites. Nice, eh? But here’s the problem: which sites? We have ten different locations and each site has its own SCCM collection. Our SCCM Admin actually put together three reports (I’m only showing you two of them) so I’m left guessing which one belongs to which site.

Fortunately, I can quickly determine which report belongs to which site (see above). If I didn’t have access to this information, though, it might take a lot of detective work to figure out which report belongs to which site. Instead of going through all this work, why not add a sub-heading that says what collection the report was based on?

Collection Query

You’ve probably used a collection query similar to the one below. This one will display all collections sorted by name.

Select
Coll.CollectionID,
Coll.Name
from
dbo.v_Collection Coll
Order By
Coll.Name

Collection Prompt

The prompt or parameters for a report will generally be set up using the collection query I showed you earlier. This query information will populate labels and values. Notice that the CollectionID is the Value and the collection name is the Label.

Main Query – Relevant Section

Here’s the relevant section of the query that deals with collections for my report:

when ’23’ then ‘Rack Mount Chassis’
when ’24’ then ‘Sealed-Case PC’
else ‘Undefinded’
end as ‘PCChassis’,
count(*) as ‘Total’
FROM
dbo.v_FullCollectionMembership FCM
join dbo.v_R_System_Valid RV on RV.ResourceID = FCM.ResourceID
join dbo.v_GS_SYSTEM_ENCLOSURE SE on SE.ResourceID = RV.ResourceID
Where
FCM.CollectionID = @Coll
Group by
SE.ChassisTypes0
Order by
SE.ChassisTypes0

The CollectionID is used to limit the number of computers to a particular collection.

Adding a Prompt Value to the Title – Value

Despite what you might think, adding a prompt value to a title is a fairly simple task. Add a text box to the report header section and make sure to size it appropriately. Right-click on it and then select Text Box Properties… In this example, the text box is immediately below the title.

On the General node, select the equation button next to the Value area.

On the lower left-hand side of the screen, select Parameters. Doing so will show the value on the right-hand side. Double-click on the Coll parameter in order to add it into the Set Expression for: Value text box. Make sure that the equal sign precedes the text. Click OK twice to return to the Designer tab.

Once you preview the report, you will see something similar to what you see in the above screenshot. Technically we’re done. I showed you how to add a prompt value to the title, but what collection is SMSDM003? It is useful to know what CollectionID you used from the prompt query, but it would be even more helpful to know the collection name.

Adding a Prompt Value to the Title – Label

Go back to the Expression dialog box.

Remove the word Value, but keep the “.” period. Once you do this, the IntelliSense feature in Visual Studio automatically displays the available options. Select the Label option and double-click on the OK button.

Now when you preview the report, you will see the collection name! Isn’t that cool? Again, technically we are done, but I want to give this sub-heading more context.

Adding a Prompt Value to the Title – String

I’m going to change the format in order to show that the “Kanata Office,” is the selected collection. I will do this by appending the text, “Collection:” to the collection name.

Simply go back to the Expression dialog box and update it with the following:

=string.format(“Collection: {0}”,Parameters!Coll.Label)

Once you update the expression, double-click on the OK button.

Preview the report, once again. Doesn’t that look nice? This update now solves the mystery of which report was for which collection. This simple tip can take your reports to the next level.

If you have any questions about adding a prompt value to the title, please feel free to contact me at @GarthMJ.