CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2012
Alice Guo
Alice Guo

Reputation: 11

SSRS How to set default parameter NOT select all, but using a where clause as the default?

I have a report with multi-value parameter from a sql query: 

SELECT USER_ID, USER_NAME FROM USERS

I want to set the default of that parameter with a where clause. The default I want to set:

SELECT USER_ID, USER_NAME FROM USERS WHERE STATUS = 'ACTIVE'

I create 2 separate dataset with the above query, the 1st one is set as the available values, the 2nd one is set as the default values, it could work.

My question is, is there a better way to do that without creating another dataset? Thank you!

Upvotes: 1

Views: 334

Answers (1)

Hannover Fist
Hannover Fist

Reputation: 10860

Add another column for the default and use your default criteria there: 

SELECT USER_ID, USER_NAME, CASE WHEN STATUS = 'ACTIVE' THEN USER_ID ELSE 0 END AS DEFAULT_ID
FROM USERS

Use the USER_ID for the Available Values and the DEFAULT_ID for the Default USER_ID values.

This assumes that you do not have an ID of 0 - use -1 if you do have a 0 ID.

Upvotes: 1

Related Questions