Post reply

Dynamic Drop Down in SSRS

  • March 28, 2022 at 6:51 pm

    #4007248

    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

    #4007251

    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 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply