Home Forums Reporting Services Reporting Services Cascading Parameters, first choice determines dataset used for next drop down. How? RE: Cascading Parameters, first choice determines dataset used for next drop down. How?

  • You'll need multiple datasets to achieve this. I don't have any DDL for what you current set up is, but hopefully this'll help you out.

    Firstly, I'm going to create a Basic table, which I'm going to use to get the parameter values from. For example (I'm using countries and Cities, but the logic is the same):

    USE DevTestDB

    GO

    IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = 'Country') BEGIN

    DROP TABLE Country;

    END

    CREATE TABLE Country (CountryID INT IDENTITY (1,1),

    CountryName VARCHAR(50));

    IF EXISTS(SELECT [name] FROM sys.tables WHERE [name] = 'City') BEGIN

    DROP TABLE City;

    END

    CREATE TABLE City (CityID INT IDENTITY (1,1),

    CountryID INT,

    CityName VARCHAR(200));

    INSERT INTO Country (CountryName)

    VALUES ('America'),

    ('England'),

    ('France'),

    ('Germany');

    INSERT INTO City (CountryID, CityName)

    VALUES (1, 'New York'),

    (1, 'Washington DC'),

    (2, 'London'),

    (2, 'Manchester'),

    (3, 'Paris'),

    (4, 'Berlin');

    Next, I'm going to need a dataset which gives me a list of the Countries that I have to chose from. Which can simply be:

    SELECT CountryID, CountryName

    FROM DevtestDB.dbo.Country

    I can then create a 2 parameters in my SSRS report with a Label of CountryName, and a Value of CountryID. I'll call them "Country1", and "Country2".

    I then need two further datasets, one for Country1, and another for Country2. So for Country1:

    SELECT CityID,

    CityName

    FROM DevtestDB.dbo.City

    WHERE CountryID = @Country1;

    I can then use these details as my label and value for your 3rd and 4th parameters.

    Finally, I would need return further data on this information, so you would then require and 4th and 5th dataset (I'm assuming you have two seperate datasets, but if you're passing both to a single DS, then that's all you need do), using my two City values.

    When creating your parameters your Parameters, ensure that they are listed in the correct order. Meaning, in this example:

    Country1

    Country2

    City1

    City2

    Hope that all makes sense.

    Edit: Just re-read your post, I think i was slightly confused by your initial statement I assumed the user was selecting two cities from a list, not select either new York or London. The above will still give you the answer, but goes an extra step. Effectively you will only need 2 datasets. You definitely don't need 4 (unless you're bringing back several sets of data). One that has your list of Areas (in my case Cities), and then your final dataset. You can use Default values for your City Options.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk