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