🛠️ How to Create a Custom SSRS Report to Get All Software Products by a Specific Company

by

If you’re managing a large IT environment and want to generate a report that lists all software installed across systems from a specific company — like Microsoft, Adobe, or Google — you’re in the right place.

In this guide, you’ll learn how to build a custom SQL query for SSRS (SQL Server Reporting Services) that uses a text input parameter instead of a dropdown, allowing dynamic filtering by company name.

🧩 The Scenario

You’re tasked with generating a report that shows all installed software products from a particular company — e.g., everything from Microsoft Corporation. Here’s the original SQL query you might start with:

sqlCopyEditSELECT DISTINCT 
    SYS.Netbios_Name0, 
    SYS.User_Name0, 
    SP.ProductName, 
    SP.CompanyName, 
    SP.ProductVersion 
FROM fn_rbac_GS_SoftwareProduct(@UserSIDs) AS SP
JOIN fn_rbac_R_System(@UserSIDs) AS SYS 
    ON SP.ResourceID = SYS.ResourceID 
WHERE SP.CompanyName = @company  
ORDER BY SP.CompanyName, SP.ProductName, SP.ProductVersion;

But this setup filters exact matches only, and SSRS will default to a dropdown if you don’t configure it manually.

🚫 Why Dropdowns Aren’t Ideal Here

  • Too many company names — dropdowns become slow and hard to navigate.
  • Misspellings or minor variations in names break the filter.
  • Users need more flexibility to search using partial names.

✅ The Fix: Use a Text Input to Filter by Company Name

1. Update the Query for Partial Matching

Let’s make the query more flexible by using the LIKE operator. Here’s the improved version:

sqlCopyEditSELECT DISTINCT 
    SYS.Netbios_Name0, 
    SYS.User_Name0, 
    SP.ProductName, 
    SP.CompanyName, 
    SP.ProductVersion 
FROM fn_rbac_GS_SoftwareProduct(@UserSIDs) AS SP
JOIN fn_rbac_R_System(@UserSIDs) AS SYS 
    ON SP.ResourceID = SYS.ResourceID 
WHERE (@company IS NULL OR SP.CompanyName LIKE '%' + @company + '%')
ORDER BY SP.CompanyName, SP.ProductName, SP.ProductVersion;

This allows users to type any part of the company name — e.g., “micro” will match “Microsoft Corporation”.

2. Change the Parameter Input Type in SSRS

Now let’s make SSRS show a text box instead of a dropdown:

  • In the “Report Data” panel, locate the @company parameter.
  • Right-click → Parameter Properties.
  • Go to the “Available Values” tab.
  • Choose “None”.

SSRS will now render a simple text input box, allowing users to type whatever they want.

🔍 Make It Case-Insensitive (Optional)

If your SQL Server collation is case-sensitive, you can explicitly ignore case like so:

sqlCopyEditWHERE (@company IS NULL OR LOWER(SP.CompanyName) LIKE '%' + LOWER(@company) + '%')

🧪 Test Example: Searching for Microsoft

Once your report is ready:

  • Run it.
  • Type Microsoft in the input box.
  • Boom — you’ll see all Microsoft products installed across your environment.

🎉 Final Thoughts

By switching from rigid dropdowns to text input parameters, you give users flexibility and control — especially useful for environments with hundreds of vendors and software products.

This approach is easy to implement, improves performance, and provides a much better reporting experience.

Leave a Comment