March 28, 2022 at 6:51 pm
In SSRS reporting, is it possible to dynamically drive drop down values based on a main table and sub table? (e.g. Category and SubCategory).
For example, I have the following two tables:
-- DROP TABLE #c
CREATE TABLE #c (CategoryID int, CategoryDesc varchar(100))
INSERT INTO #c VALUES (1, 'Fruit')
INSERT INTO #c VALUES (2, 'Veggie')
SELECT * FROM #c
-- DROP TABLE #sc
CREATE TABLE #sc (SubCategoryID int, CategoryDesc varchar(100), CategoryID int)
INSERT INTO #sc VALUES (1, 'Apple', 1)
INSERT INTO #sc VALUES (2, 'Orange', 1)
INSERT INTO #sc VALUES (2, 'Grapes', 1)
INSERT INTO #sc VALUES (2, 'Peas', 2)
INSERT INTO #sc VALUES (2, 'Carrot', 2)
SELECT * FROM #sc
How can I make them work in an SSRS Report so that if I pick Fruit from Category, only the Fruit options about in the SubCategory drop down?
March 28, 2022 at 7:08 pm
Parameterize your subcategory parameter available values query to use CategoryID parameter as a filter:
SELECT SubCategoryID, CategoryDesc
FROM #sc
WHERE CategoryID = @CategoryID
You will set the Dataset parameter @CategoryID parameter to [CategoryID] parameter.
General note for others: One can't actually use temp tables directly in SSRS as they only exist within the context of a session -- I'm sure you know this & provided temp table DDL because it's easily reproducible & doesn't contaminate existing database.
(temp tables can be used within a stored procedure used as a dataset)
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply