Unique values for drop-down menu

0
votes

I've got an SSRS report with several different drop-down parameter menus, each of which depends on the selections in the menus before them. The report is taking similar data from several different tables, so there is some repeated data, and these values are showing up multiple times in the menus. For example, say I select sites A, B, and C; each of these sites may have warehouses called WH1 and WH3, so "WH1" and "WH3" each show up multiple times in the Warehouse drop-down menu.

I know that the reason it's doing this is because I have both Site name and Warehouse name in my SQL query, and, even though I have the queries for each table joined with a UNION, which should eliminate duplicates, I'm selecting both the Site and Warehouse columns from each table, so each combination of Site and Warehouse is considered a unique value. I can't take the Site column out of the query, because I need to filter the Warehouse menu by Site. Is there a way to get only the unique values of Warehouse for the menu?

Here's my SQL query for the dataset which feeds the Warehouse parameter. The dataset also has a simple filter based on the Site chosen in the first menu.

SELECT DISTINCT Site, Warehouse
FROM T1
UNION
SELECT DISTINCT Site, Warehouse
FROM T2
UNION
SELECT DISTINCT Site, Warehouse
FROM T3
ORDER BY T$CWAR

2
sqlssrs-2008business-intelligencebids
A code example would help. It sounds like you could wrap the union query in another select statement that grouped by the warehouse name. – supergrady
@supergrady I added an example. – Ben C.
what do you mean by " I can't take the Site column out of the query, because I need to associate Warehouses with their respective Sites". – Filipe Silva
@FilipeSilva I need the site column to be able to filter the warehouses by site. If I only had the Warehouse column in the query, I'd have a bunch of warehouse names with no idea which site they were at. – Ben C.

2 Answers

0
votes

Create a new dataset with below query:

select DISTINCT temp.Warehouse from (
SELECT DISTINCT Site, Warehouse
FROM T1
UNION
SELECT DISTINCT Site, Warehouse
FROM T2
UNION
SELECT DISTINCT Site, Warehouse
FROM T3
) temp

Pass this query to availible values in parameter properties of "Warehouse" if needed give in default values also.

0
votes

Here's what ended up working. It's kind of convoluted, and I'll admit that I don't quite understand why it works, but it does work.

SELECT Site, Warehouse, ROW_NUMBER() OVER (PARTITION BY Warehouse
ORDER BY Warehouse) AS RowNum
FROM(
SELECT Site, Warehouse, ROW_NUMBER() OVER (PARTITION BY Warehouse
ORDER BY Warehouse) AS RowNum
FROM Table1) AS T
WHERE T.RowNum = 1

The solution was adapted from the answer to this question: "Invalid column name" error on SQL statement from OpenQuery results