Last updated on September 28th, 2022 at 09:36 am
In my blog post, Turning Three Reports into One Report, I said that I would show you how to adjust your prompt to allow for an, “All Option.” Everything you need to do is within the SQL Server queries themselves. This blog post shows you how to edit BOTH the prompt SQL Server query and the main dataset SQL Server query to allow for an “All Option.” By the way, you don’t need to change a report’s appearance to make the “All Option” work.
Sample SQL Server Queries
In this blog post, I am using two sample SQL Server queries. The main dataset query shows the operating system (OS) for a given system role. The second SQL Server query (aka the prompt query) provides a list of system roles. There are really only two options for System Role: Server or Workstation.
Note: If you cut and paste the queries from this blog post into SQL Server Management Studio (SSMS), watch out for stylized (curly) single quotes instead of the normal single quotes that should be there.
List of Computers for a System Role (Main Dataset Query)
Select
RV.Netbios_Name0 as ‘Computer’,
RV.User_Name0 as ‘User’,
OS.Caption0 as ‘OS’,
S.SystemRole0 as ‘System Role’
From
dbo.v_R_System_Valid RV
Inner Join dbo.v_GS_OPERATING_SYSTEM OS on RV.ResourceID = OS.ResourceID
Inner Join dbo.v_GS_SYSTEM S on RV.ResourceID = S.ResourceID
Where
S.SystemRole0 = @SysRole
Order by
RV.Netbios_Name0
List of System Roles Prompt Query (Second Query)
Select Distinct
S.SystemRole0 as ‘System Role’
From
dbo.v_GS_SYSTEM S
Order by
1
Adding an All Option to Your Main Dataset Query
I’m going to start by editing the main dataset query. I want all results to be displayed when the “All Option” is selected by the prompt. The edit to this SQL Server query is actually very simple. In the Where section of the query, add an OR condition that checks to see if @SysRole = ‘ – all – ‘. Here’s what this does: when the ‘ – all – ‘ is selected in the prompt, the condition in the Where section is true, so all results are returned for the query.
Where
S.SystemRole0 = @SysRole or @SysRole = ‘ – all – ‘
Notice when I run the query in SSMS, that the results show BOTH servers and workstations.
Adding an All Option to Your Prompt Query
Adding the “All Option” to the prompt query isn’t much harder, BUT it leverages the union operator. What does that mean? In a nutshell, if you are unfamiliar with the union operator, it takes two query results and makes them one. You can read the full description on the w3schools site or on the Microsoft docs site Set Operators – UNION.
I am now going to create another select statement to display the “All Option” and then I am going to unite the results together using the union operator.
Above are the before and after screenshots. The screenshot on the left shows the results as two different queries and the right screenshot shows them as one!
Tip: Get into the habit of making sure that what you use within the main dataset query is EXACTLY the same as what is listed within the prompt query. I truly mean exactly the same: the same case, the same spacing, the same everything! This becomes particularly important when you use drill throughs. See my blog post for more details: Why is My Prompt in SSRS Not Working?
Final Steps
With both queries completed, you can now update your report dataset. And, just like that, your report now has an, “All Option!”
I can already hear some of you saying, “Why use this method and not the “Select All” in a multi-select prompt?” The short answer is: drill through reports and subtitles (Adding a Prompt Value to the Title of a SCCM Report). The long answer has to wait for another blog post.
The query I showed you in this post, of course, can be modified. What if you wanted, in addition to an “All Option” prompt, a prompt option for a laptop and a desktop too? It can all be done.
It is features like these that we use within our software solutions, Enhansoft Reporting and Warranty Information Reporting. This allows us to present data in a meaningful way based on customer demand! Please feel free to touch base with me @GarthMJ if you have any questions.