#1 2020-08-07 19:37:29

monteillin
Member
Registered: 2020-07-16

How to reference multiple values in a parameter to use in a filter?

I’m trying to create an ad hoc report where the users are able to specify a list of items that they want to see, identified by a serial#. So my natural thought is to use a parameter, and in the query, I would just add a WHERE serialnumber IN (${key1}). However, I’m not sure how this would work. The text parameter seems to only support one value. I’ve tried separating multiple value with a comma, or using quotations, but to no avail. So I thought about using a “File Selection Parameter” and upload a CSV with the list of serialnumbers I want to query, but I can’t get that to work. Any suggestions on how to do this?

Offline

#2 2020-08-19 13:28:21

alex_lust
Moderator
Registered: 2020-07-03
Website

Re: How to reference multiple values in a parameter to use in a filter?

Hi, maybe this will help you: https: //reportserver.net/en/guides/admin/chapters/using-parameters/

Offline

#3 2020-08-19 19:44:25

monteillin
Member
Registered: 2020-07-16

Re: How to reference multiple values in a parameter to use in a filter?

That's the page I was referencing for parameters for but not sure how I would do it in this case. Am I on the right track that I should be using a "File Selection Parameter" and upload a CSV with a list of serialnumbers? or is there a way to cut and paste multiple values into a text parameter?

Offline

#4 2020-08-20 07:51:15

alex_lust
Moderator
Registered: 2020-07-03
Website

Re: How to reference multiple values in a parameter to use in a filter?

At first you need to add your csv as a datasource in Administration->Datasources
Now you can add a parameter from the datasource for your report (Reports->YOUR_REPORT->Parameters->Edit-> Specific Properties to choose datasource)
and use in your query, for example:
SELECT * FROM RS_USER where $X{IN, sex, my_id}

Offline

#5 2020-08-20 17:17:00

monteillin
Member
Registered: 2020-07-16

Re: How to reference multiple values in a parameter to use in a filter?

I see. I can try this, but I assume you would need admin priviledges to be able to upload the CSV right? users won't be able to do that?

Offline

#6 2020-08-24 07:42:54

eduardo
Administrator
Registered: 2016-11-01
Website

Re: How to reference multiple values in a parameter to use in a filter?

Hi monteillin,

if you create a CSV datasource and select its "argument connector", the csv input field will appear directly at the report. So the user would not need admin priviledges. Please take a look here:
https://reportserver.net/blog/2016/08/0 … /#more-254
https://reportserver.net/blog/wp-conten … tadata.png

"The text connector allows to specify the data directly at the datasource while the URL connector allows you to specify a URL from which the CSV file is loaded. Finally, the argument connector provides you with a neat way to easily enter static data, for example, for datasource parameters or the metadata datasource. If we create a CSV datasource with an argument connector, name it CSV Argument Source and choose it as the datasource for our metadata datasource we are provided with a text field into which we can directly type in the information:"

Regards,
Eduardo

Offline

#7 2020-08-24 15:36:54

monteillin
Member
Registered: 2020-07-16

Re: How to reference multiple values in a parameter to use in a filter?

Thanks Eduardo. I will try this.

Offline

Board footer

Powered by FluxBB