SSRS report with dynamic query taking parameters defined from a database

Question

My overall goal is to produce an SSRS 2012 report which displays metrics about workers in different teams. Additionally, I would like to have some custom dropdown (customizable per team) where only particular workers are displayed.

For example, I have 2 Projects - Project1 and Project2. Project1 wants dropdown choices which allow them to show their workers in San Francisco and New York teams. Project2 wants a dropdown that allows them to show workers in Houston and San Francisco teams.

Teams are not a construct represented by data in the database. I also want to arbitrarily define custom Teams in the future (Team that works from home, Team that knows C++, etc..). The "All" team means no filtering.

The query for a project would be

select Member.MemberName, Member.MemberWork from Member where Member.Project='Project1'

For Project1, San Francisco team, I would write

select Member.MemberName, Member.MemberWork from Member where Member.Project='Project1' and Member.MemberID IN (123, 125, 127) /* teams are not stored in the database so I need to hardcode member IDs that compose a team */

The way I am storing the relationship between each custom dropdown choice is as follows

CREATE TABLE [dbo].[TeamQueries] ([ProjectName] [nchar](128) NULL,
[TeamName] [nchar](64) NULL,[TeamQuery] [nchar](3096) NULL
) ON [PRIMARY]

Example Rows in this table would be in this image (which I cannot post from lack of reputation)

The query (the dataset is called "data") in the SSRS report is as follows

 declare @query nvarchar(max)
    set @query= 'select Member.MemberName, Member.MemberWork from Member where Member.ProjectName=' + @ProjectDropdown + ' and Member.MemberID IN (' + @TeamQuery + ')'
    exec(@query)

The @ProjectDropdown is a single valued TEXT parameter with default and available values coming from a query

select ProjectName from Projects

The @TeamQuery is also a single valued TEXT parameter with default and available values coming from a query

SELECT TeamName  ,TeamQuery FROM TeamQueries
  WHERE (ProjectName=@ProjectDropdown or ProjectName = 'All')

Choosing Team1 and San Francisco in the available dropdowns, the error message I get is:

Query execution failed for dataset 'data'. Incorrect syntax near '127'.

The dropdowns get populated fine. By choosing Project1, I get choices of San Francisco and New York in the dropdown.

My question is - what's wrong, how do I get this to work? The general issue is I want a where predicate string (that is part of an IN clause) to come from the database and be optionally applied in SSRS.

Here are some things I have tried.

I tried to make this construct work outside of dynamic SQL. for example, using an OR construct to avoid the select Member.MemberID from Member and have the following

select Member.MemberName, Member.MemberWork from Member where member.ProjectName=@ProjectDropdown and (@TeamQuery IS NULL OR Member.MemberID IN (@TeamQuery))

I kept getting VARCHAR to int conversion errors. I tried select Member.MemberName, Member.MemberWork from Member where Member.ProjectName=@ProjectDropdown and (@TeamQuery IS NULL OR CONVERT(VARCHAR(Member.MemberID)) IN (@TeamQuery))

or converting @TeamQuery to INT by SPLIT and making TeamQuery a multi-valued parameter.

I tried printing @query in SSRS with the hopes of some debuggability but I cannot see the query that SSRS is using.

If I hardcode the parameter of @TeamQuery in SSRS, the query does work. I suspect something with strings and quotes - the database is retrieving something slightly different than I am expecting (perhaps I need to strip quotes?)

user3019077 user3019077 · Accepted Answer · 2013-11-22T21:44:39

The issue was the definition of the column. I mistakenly made the column definition nchar rather than nvarchar. This caused extra padding and the query error.

SSRS report with dynamic query taking parameters defined from a database

1 Answers