Last updated on August 6th, 2022 at 04:02 pm
Prompts are extremely useful because with a prompt you can limit what is displayed within a report. You can get even more granular results when you add a second or a third prompt. Adding additional prompts, however, can lead to some minor inconveniences and unintended consequences. That’s why, in this post, I show you how to create cascading prompts.
I’ll explain in more detail what I mean by, “minor inconveniences,” and “unintended consequences,” a little later on in this post, but essentially, if YOU know what the problem is, this isn’t a big deal. BUT, considering that reports are generally created for others, you may have to do some explaining. You can’t give end-users a reason to believe that there is a problem with the results. Instead, you must gain their trust. That’s the reason behind this post.
What Is a Cascading Prompt?
A cascading prompt is a prompt which is limited by another prompt. A good example of when you would need a cascading prompt is when you have a prompt for specific collections and then a second prompt for only showing the results for a chosen collection.
Why Would I Use Them?
In the query example below, List of Computers by System Role (Main), you see two variables (prompts). The first one limits by collection (SQL query not shown) and the second variable limits by system role (List of System Roles Prompt Query). For this purpose, you may remember that I used this example in my previous blog post, How to Create a Collection Prompt Query.
If I select a collection that only has workstations in it, and the second prompt isn’t adjusted, I will see that I can select servers. Now, I know that there are no servers in this collection, so when I run the report, there are no results for servers and I know that is exactly what is expected. Another person, however, who isn’t familiar with this collection and is reviewing the same report may think there’s a problem with the report itself. Given that the prompt said that they could select servers and none appeared, they question the report’s validity. That’s what I was talking about earlier when I mentioned the small inconveniences you can have without using cascading prompts.
Using cascading prompts, in this example, means that once a collection is selected ONLY the valid options for that collection are shown. This ensures that the report only shows results for valid options.
The above video shows both the problem of not having a cascading prompt and how a cascading prompt should work. Now that you understand the problem, read on to see how you can solve it.
Example SQL Server Query
Below are the two SQL Server queries . Further, the main query shows the operating system (OS) for a given collection and the system role. Furthermore, the second SQL Server query shows a list of system roles. Meanwhile, in the second query, there are only two options for system role: Workstation or Server.
List of Computers by System Role (Main)
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
Inner Join dbo.v_FullCollectionMembership FCM on RV.ResourceID = FCM.ResourceID
Where
FCM.CollectionID = @CollID
and S.SystemRole0 = @SysRole
Order by
RV.Netbios_Name0
List of System Roles Prompt Query
Select Distinct
S.SystemRole0 as ‘System Role’
From
dbo.v_GS_SYSTEM S
Order by
S.SystemRole0
How to Create Cascading Prompts
At first this might seem tricky, but really it is fairly simple. In my earlier post, How to Create a Collection Prompt Query, I showed you how to add a collection variable to a query. In this case, you are doing the exact same thing, but with a SysRole variable. The only trick to this tip is to remember that the SysRole variable (prompt) must be listed after the collection prompt. This creates the cascading effect of a cascading prompt. With that simple edit, you can create any number of cascading prompt queries to use within your reports.
The final query in my example looks like this:
Select Distinct
S.SystemRole0 as ‘System Role’
From
dbo.v_GS_SYSTEM S
Inner Join dbo.v_FullCollectionMembership FCM on S.ResourceID = FCM.ResourceID
Where
FCM.CollectionID = @CollID
Order by
S.SystemRole0
Finally, don’t forget that you can subscribe to my RRS feed to stay on top of the latest trips and tricks. Additionally, If you have any questions, please feel free to touch base @Garthmj.