0

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

Ben C.
  • 1,761
  • 5
  • 15
  • 24
  • 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 Jul 30 '13 at 21:14
  • @supergrady I added an example. – Ben C. Jul 30 '13 at 21:20
  • 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 Jul 30 '13 at 22:07
  • @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. Jul 31 '13 at 13:07

2 Answers2

0

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.

hemanth
  • 577
  • 6
  • 17
  • This didn't work. It gave me an error, and then asked me to fill in values for Site. – Ben C. Jul 31 '13 at 13:56
  • Ok if "Warehouse" parameter should not depend on values of site, then remove the where condition.Please look at the edited answer – hemanth Jul 31 '13 at 14:20
  • Well, that's exactly my problem. Warehouse does depend on Site, but I only want the distinct values for Warehouse based on which Sites are chosen in the first parameter. – Ben C. Jul 31 '13 at 14:28
  • ok if it depends on site then my previous query will not allow you to select warehouse before selecting site. First select site, according to that my query will filter according to input of site then gets the distinct warehouse values – hemanth Jul 31 '13 at 14:34
  • I'm still pretty new to SQL. Where exactly are you suggesting that I select Site before moving on to the rest of your query? – Ben C. Jul 31 '13 at 15:28
0

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

Community
  • 1
  • 1
Ben C.
  • 1,761
  • 5
  • 15
  • 24