SSRS Dropdown for column items

Question

I've built a SSRS report using a SQL query for the Dataset1. I'm trying to build a parameter that gives the users a dropdown list. I ended up creating a Dataset2 to get distinct values for the parameter Label and then use Dataset1 for the actual value (allowing for multiple values).

EDITING PER Request:

Dataset1 query:

SELECT vu_SOPWork_HistoryUnion.Type
    ,vu_SOPWork_HistoryUnion.SOPTYPE
    ,vu_SOPWork_HistoryUnion.SOPNUMBE
    ,vu_SOPWork_HistoryUnion.Date_Document
    ,vu_SOPWork_HistoryUnion.ExtendedPrice
    ,IV00101.ITEMNMBR
    ,IV00101.USCATVLS_2 AS Family
    ,IV00101.USCATVLS_3 AS Product
    ,vu_SOPWork_HistoryUnion.VoidStatus
    ,RM00101.CUSTCLAS
    ,GL00100.MNACSGMT
    ,vu_SOPWork_HistoryUnion.BillTo_CustNum
    ,vu_SOPWork_HistoryUnion.BillTo_CustName
    ,vu_SOPWork_HistoryUnion.sales_territory
    ,vu_SOPWork_HistoryUnion.ITEMDESC
FROM (
    (
        test.dbo.vu_SOPWork_HistoryUnion vu_SOPWork_HistoryUnion INNER JOIN test.dbo.IV00101 IV00101 ON vu_SOPWork_HistoryUnion.ITEMNMBR = IV00101.ITEMNMBR
        ) INNER JOIN test.dbo.RM00101 RM00101 ON vu_SOPWork_HistoryUnion.BillTo_CustNum = RM00101.CUSTNMBR
    )
INNER JOIN test.dbo.GL00100 GL00100 ON IV00101.IVSLSIDX = GL00100.ACTINDX

Dataset2 Query:

SELECT DISTINCT IV00101.USCATVLS_2 AS FamilyNames
FROM test.dbo.IV00101

I would like to use the Dataset2 "FamilyNames" as the label options against Dataset1 "Family" values. Dataset2 gets the distinct values that I want to put in the drop down for the user to choose and then receive all rows that has that value in Dataset1 "Family". I'm an expert on not giving enough information. Hope this helps you help me. Thanks.

can you share sample or image of what u r doing in dataset2 and what you actually want? I hope you are not trying to refer the report items in parameter expressions etc. — ViKiNG
It just selecting Distinct values: Select Distinct Column AS Type from Database I'm just trying to give a dropdown so the user can show specific values in a column. — psstdnt
from what? You need to show your Dataset1 query and what you are trying to see in your parameter list. — Alan Schofield
I have a column with multiple values, 1, 1, 1, 2, 2, 3, 3, 3 and I'm trying to setup a parameter to only list 1, 2, 3 and then show all rows with that particular value. The query is a simple select query: SELECT vu_Work.Type, vu_Work.Number, vu_Work.Family, vu_Work.Product FROM db1.dbo.vu_Work — psstdnt
So what column contains the numbers you want as your values and where will the parameter labels come from? Please just edit your question to show everything you have. People will help you but only if they have enough information. Please don't be vague and say "I get it from another dataset", actually show that dataset query. The answer is probably really simple but we're just guessing at the moment. — Alan Schofield

Alan Schofield Alan Schofield · Accepted Answer · 2017-07-20T06:45:35

OK, do the following..

Create DataSet1 with the query as you have it now but append the following to the end of the query

WHERE USCATVLS_2 IN(@Family)

When you do this, the @Family parameter will be created automatically in your report, we'll get back to this later.

Create another dataset called Dataset2 (or a more sensible name like 'families' in this case) with the following simple query.

SELECT DISTINCT USCATVLS_2 FROM test.dbo.IV00101 ORDER BY USCATVLS_2

Edit the @Family parameter:

Set it to multi-value
Change the available values to be a query
Select your 2nd dataset as the datasource
Choose USCATVLS_2 as both the labels and values.

Create your report as normal with the tablix/matrix based on Dataset1.

Notes: A few other things you might want to consider, but not required.

If a list of family names with some kind of ID is available in your database then I suggest you use that for your parameter list, you would also then have to edit the query in dataset1 to match. Remember that the parameter will contain whatever is in the column of the query that you chose as the value column in the dataset that populates the parameter list
Consider using aliases for table names in your queries rather than the full table names all the time, it makes the code more concise and easier to read.
Name your datasets according to what they contain or do, e.g. I would call Dataset2 'Families' or something similar as that is what it contains. When your reports get more complex it will make them easier to understand.

SSRS Dropdown for column items

1 Answers