November 21, 2016 at 10:29 pm
Hello,
I hope someone can help me.
I have created 4 Datasets, a main Dataset, then a Dataset for each drop down box that I want the users to be able to choose from (Main DataSet, then a Dataset called 'New York', a Dataset called 'London', both taken from the Main Dataset, the next two Datasets contains areas of London, and areas of New York respectively.
So choices in the first box are either New York or London. If someone picks London, I need the following values to come from a London data set (areas of London will be listed), but if they choose New York, the next values to choose from come from the New York data set (areas of New York will be listed).
I have worked out how I can choose from London or New York (using You Tube videos), but I cannot for the life of me work out how I can get to use the appropriate dataset for the next drop down box based on the previous choice.
To summarize...
User chooses 'London' -> Then gets to choose London Area -> View report
Or
User chooses 'New York' -> Then gets to choose New York Area -> View report.
Any clues or help would be greatly appreciated.
Kind regards,
D.
November 22, 2016 at 8:09 am
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
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply