Where clause that looks up values from an outside source, using Report Builder in SSRS

[kwoltman]

First off, I apologize if this is posted in the wrong place, but I didn't see anything that was dedicated to Report Builder.

Secondly, I'm in Finance and understand some level of SQL reporting so please bear with me on this question.  I also realize that being within the corp walls, that my ask here might be something that I may not have permission to do, but I won’t know for sure until I attempt this.

I use SQL Server Management Studio 19.3 to create my queries.  Once I have my query where I want it, I launch Report Builder from SSRS.  I do not see a version # for Report Builder.  I copy my query into the SSRS report and then publish it (save it, etc).

Example data:

Table 2 has a field called “Username”

I write lots of queries that ultimately end up being SSRS reports that rely on Table 2/Username.  Many of these queries I hard-code in a list of Usernames.  So, say I have 10 SSRS reports already created but then in the future the list of Usernames changes.  It’s a pain to update all of the different queries/SSRS reports.

Say my query looks something like this:

Select t1.ID, t1.Field1, t1.Field2

From Table1 t1 inner join Table2 t2 on t2.ID = t1.ID

Where t2.Username in (‘Username1’,’Username2’,’Username3’)

Is there a way to have the above WHERE clause look at another table where I store the values ONE time?  Where I can manage that list of Usernames in ONE place?  Basically ending up with something like the following?

Select t1.ID, t1.Field1, t1.Field2

From Table1 t1 inner join Table2 t2 on t2.ID = t1.ID

Where t2.Username in Table3/External Source

If so, I would appreciate any insight on how to accomplish this.  The more detail the better as I am a novice at this.

If you need more info, please let me know, but I believe this should cover what I’m after.

[v-jtian-msft]

Hi,@kwoltman .I am glad to help you.

You can create a separate user information table (e.g. UsernamesTable), and you can refer to the list of usernames in this table in a query using a subquery (or using a multivalued query parameter)

Create a new table (e.g. UsernamesTable) that stores all the usernames. This table can contain a field such as Username.CREATE TABLE UsernamesTable ( Username VARCHAR(50) );
Insert Usernames into UsernamesTable: Inserts all required usernames into this table.
Modify your original query to refer to the newly created UsernamesTable: Use an IN subquery in your query to refer to the usernames in the UsernamesTable (or use the query parameter as @Parameter) SELECT t1.ID, t1.Field1, t1.Field2FROM Table1 t1INNER JOIN Table2 t2 ON t2.ID = t1.IDWHERE t2.Username IN (SELECT Username FROM UsernamesTable);

The advantage of this is that it enables centralized management and you only need to update the list of usernames in one place without modifying the query for each report.

URL:
Paginated report parameters in Report Builder - Microsoft Report Builder & Power BI Report Builder |...

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.