Multi Values Parameter - how select all
Hi, I have problem with passing multi values parameter. I have one table and I want to parameter by first column where I have 3 type of data. I can use Get Query from a Query and allow MultiValues but label will be data1,data2,data3. So I want to use Specify Values and for simple value it is working but if I want to pass multivalues it is not working. I tried different syntax but without success. I tried to use in the expression for value: SELECT firstcolumn FROM table My Dynamics CRM Blog: http://bovoweb.blogspot.com
December 13th, 2009 6:06am

Hi kgorczewski,You can pull too columns - one with the values, and another one with the labels. Something like:SELECT firstcolumn,lablecolumnFROM tableYou can also specify your own column - say:SELECT firstcolumn,UPPER(firstcolumn) AS labelcolumnFROM tableThe above example converts firstcolumn to UPPER CASE.Then, you can just use that column as a Label column for your MultiValue parameter. Boyan Penev --- http://www.bp-msbi.com
Free Windows Admin Tool Kit Click here and download it now
December 13th, 2009 4:44pm

Hi Boyan, Yes, I tried this but if I will choose All report will not return any data. See screenshot: http://img301.imageshack.us/img301/9030/ssrs.png My Dynamics CRM Blog: http://bovoweb.blogspot.com
December 13th, 2009 6:59pm

Hi kgorczewski,If you want the All parameter to show you both Tasks and Appointments, you would be better off specifying ="Task,Appointment" as a Value for All, and then in your SQL for your reporti dataset make sure that it says:WHERE column IN @Paramrather than WHERE column = @ParamThis should do it for you. On a side note, you cannot write SQL code for SSRS expressions. Explore a bit more the part where you can see Category and Item to get a bit more familiarised with Reporting Services expressions syntax. An useful link:http://msdn.microsoft.com/en-us/library/ms159238.aspxAlso, if you want to get all the values which are in your table, you can define a report dataset for populating this parameter in particular. What you have to do:1. Define a new dataset (in example ParamActivityType)2. As a SQL query for the dataset use something like:SELECT DISTINCT activitytypecodenameFROM FilteredActivityPointer3. Modfiy your parameter to use a query (Get values from a query)4. Select your dataset (Paramerer one ParamActivityType)5. Select Value and Label fields from the dataset6. Go to the General tab and select Allow Multiple Vaues7. Your parameter should be now configured, and should allow you to select all values returned by the query8. Make sure that your dataset, which gets filtered by the parameter can work with multiple values (with the IN syntax described above, or with a split function).9. If you want to have an All level, you can either manually add it to the parameter dataset by doing the SELECT and the UNION 'All', which will give you another value All, and then making sure that your main dataset gives you all the data when All is selected.Please try and see where you can get with these tips, and post back - I'll try to get you through this one.
Free Windows Admin Tool Kit Click here and download it now
December 13th, 2009 7:18pm

Hi Boyan, I tried earlier ="Task,Appointment"but it is not working. Method using 'Get values from a query' is working but in label I need have label 'All' How to use UNION and pass all values ? My Dynamics CRM Blog: http://bovoweb.blogspot.com
December 13th, 2009 7:51pm

I had similar problem. I found a simple solution where the parameter can default to the value ALL, and the value ALL can be displayed in the report header. I kicked myself at how simple it was, once I figured it out. Basically add an ‘ALL’ value to your select list using a UNION statement, and then have your main query select ((' ALL' IN (@Parameter)) OR (your_value IN (@Parameter))) I blogged the step-by-step about it here: http://dataqueen.unlimitedviz.com/2011/06/how-to-default-to-all-in-an-ssrs-multi-select-parameter/Martina White
Free Windows Admin Tool Kit Click here and download it now
June 17th, 2011 2:06pm

Hello Gurus! To Galera with a question that is killing me ... I am using a C # where WindowsForm have the data client and start date and end date of the consultation, after performing the search in a datagridviewer it returns all the data regarding the query. In datagridviewer have a column with checkbox where I can select the data that I want to appear in the report. Fazo forech a sweep on the grid and take the data you want and off into a List <string>, until so good. My problem is passing the IDS data selected in the checkbox to the report. I have created in a report parameter named @ IDS where I configured it can receive multiple values. My SQL (I use mysql DB) using the clause IN (where IN idsXXX (@ IDS)). When I perform the selection of only one record it generates the report, when I select more than one he did not return anything, the report provides no data. Can anyone give me a solution. SQL QUERY: SELECT re.DSCREGIAO, vl.PESO, cl.E_MAILCONTATO, cl.RAZAOSOCIAL, cl.CNPJ, cl.CIDADE, cl.BAIRRO, cl.UF, cl.CEP, cl.RUA, cl.FONE, cl.FAX, cl.CONTATO, co.IDCONHECIMENTO, co.NUMEROAWB, co.QUANTIDADE, co.REMETENTE, co.DATAREMESSA, co.VLRTABELA, co.VLRFINAL, co.TXDESCONTO, co.TXIMPOSTO, co.TXCOMBUSTIVEL, co.VLRDESCONTO, co.VLRCOMBUSTIVEL, co.VLRIMPOSTO FROM cliente cl INNER JOIN conhecimento co ON cl.IDCLIENTE = co.IDCLIENTE INNER JOIN valorplano vl ON co.IDVALOR = vl.IDVALOR INNER JOIN regiao re ON co.IDREGIAO = re.IDREGIAO WHERE (cl.IDCLIENTE = @pCodigoCliente) AND (co.NUMEROAWB IN (@pIds)) AND (co.STATUS = 'A') :(
June 15th, 2012 1:28pm

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics