1

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!

Alice Guo
  • 11
  • 5

1 Answers1

1

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.

Hannover Fist
  • 10,393
  • 1
  • 18
  • 39
  • Hi, I tried this method, it's wired, I could have the default parameter in Report builder, but it doesn't work when I save it and run the report. Do you have any idea? Thanks! – Alice Guo Oct 30 '19 at 17:50