Inserting a blank line in a Reporting Services parameter field

I had a parameter in Reporting Services with a list of user IDs. I needed to add a blank line as the first value instead of the default  value of “<Select a value>”. I messed around with the parameter properties screen, including checking “Allow blank value” and “Allow null value”, but neither did what I wanted. My solution was to tweak the query a little bit.

The UserName parameter was populated with a UserNameDataSet Dataset, and that Dataset was populated with the following query:

SELECT  UserName
FROM [User]
ORDER BY UserID

Nothing fancy – the UserName field holds text user name that is used to log into the system. In order to add a blank line at the top of the list for the user UserName parameter, I had to modify the query to look like the one below, adding a new, dummy “ID” field and UNIONing in a blank space line:

SELECT UserName, 1 AS ID
FROM [User]
UNION
SELECT ‘ ‘ AS UserName, 0 AS ID
ORDER BY UserName

Hey, it met the requirements.

Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: