Dynamic menu ?

Hi all,
I'm working with a report in Reporting Services 2005 and I have a problem
that I don't know how to solve.
In my report I have two drop down menus(not mvp) where the first one holds
countries. The second one holds
cities. I want the second menu to be dynamic, i.e. only show cities for the
country I selected in the first menu.
Is this possible to do in RS?
Thanks!Yes, it is. You are talking about cascading parameters.
Create your country parameter and define its datasource. This can be
either a value list or a dataset.
Create a cities dataset using the country parameter in a where clause.
Create your city parameter and define its datasource as a the dataset
that you just created. The "cascading" effect will happen automatically
because the city drop-down cannot be populated until you assign a value
to the country parameter.
Does this explanation make sense?
-Josh
Malin Davidsson (at) wrote:
> Hi all,
> I'm working with a report in Reporting Services 2005 and I have a problem
> that I don't know how to solve.
> In my report I have two drop down menus(not mvp) where the first one holds
> countries. The second one holds
> cities. I want the second menu to be dynamic, i.e. only show cities for the
> country I selected in the first menu.
> Is this possible to do in RS?
> Thanks!|||If you can populate the second list using SQL based on a parameter from the
first list, it should be possible I'd think. I haven't tried though.
Randall Arnold
"Malin Davidsson" <malin.davidsson(at)aus.teleca.se> wrote in message
news:eSpYAgM2GHA.2176@.TK2MSFTNGP04.phx.gbl...
> Hi all,
> I'm working with a report in Reporting Services 2005 and I have a problem
> that I don't know how to solve.
> In my report I have two drop down menus(not mvp) where the first one holds
> countries. The second one holds
> cities. I want the second menu to be dynamic, i.e. only show cities for
> the country I selected in the first menu.
> Is this possible to do in RS?
> Thanks!
>|||Thank you, that solved the problem =)
<bell.joshua@.gmail.com> wrote in message
news:1158330681.278038.205980@.k70g2000cwa.googlegroups.com...
> Yes, it is. You are talking about cascading parameters.
> Create your country parameter and define its datasource. This can be
> either a value list or a dataset.
> Create a cities dataset using the country parameter in a where clause.
> Create your city parameter and define its datasource as a the dataset
> that you just created. The "cascading" effect will happen automatically
> because the city drop-down cannot be populated until you assign a value
> to the country parameter.
> Does this explanation make sense?
> -Josh
>
> Malin Davidsson (at) wrote:
>> Hi all,
>> I'm working with a report in Reporting Services 2005 and I have a problem
>> that I don't know how to solve.
>> In my report I have two drop down menus(not mvp) where the first one
>> holds
>> countries. The second one holds
>> cities. I want the second menu to be dynamic, i.e. only show cities for
>> the
>> country I selected in the first menu.
>> Is this possible to do in RS?
>> Thanks!
>

Dynamic field question

Can someone point me in the right direction to solve the following
(basic) SQL problem below using SQL Server:

Let's say I have a table like this that lists people's likes:

CREATE TABLE likes (
myname VARCHAR (60),
travel BIT,
eatingout BIT,
disco BIT,
swimming BIT);

Let's say I put the following data inside this table:

INSERT INTO likes VALUES ('Darren', 1,0,0,1)
INSERT INTO likes VALUES ('John',1,1,0,1)
INSERT INTO likes VALUES ('Peter',0,0,0,0)
INSERT INTO likes VALUES ('Jill',0,0,0,1)

Then what I want is to create a (view? Or function? I am not sure),
called likes_details' that when I send this query:

SELECT myname, likes FROM likes_details

Returns the following:

Myname likes
DarrenTraveling and swimming
JohnTraveling, eating out and swimming
PeterDone not like anything
JillSwimming only

Please! Can anyone help!!

Thank you in advance.Darren,

The mess-ed up short term workaround to your problem with the existing
schema is:

SELECT myName,
COALESCE( NULLIF (
CASE travel WHEN 1 THEN 'Travelling, '
ELSE SPACE(0) END +
CASE eatingout WHEN 1 THEN 'eating out, '
ELSE SPACE(0) END +
CASE disco WHEN 1 THEN 'disco, '
ELSE SPACE(0) END +
CASE swimming WHEN 1 THEN 'swimming, '
ELSE SPACE(0) END, SPACE(0)),
'Do not like anything')
FROM likes ;

Now, the real solution to your problem is that you need to overhaul your
schema, it has values as column names, under-normalized and thus unusable. A
good way of representing this information would be like:

CREATE TABLE Persons (
Person_id INT NOT NULL PRIMARY KEY,
PersonName VARCHAR(10) NOT NULL,
...);
CREATE TABLE Hobbies (
Hobby_id INT NOT NULL PRIMARY KEY,
HobbyDesc VARCHAR(20) NOT NULL,
...);
CREATE TABLE PersonHobbies(
Person_id INT NOT NULL
REFERENCES Persons(Person_id),
Hobby_id INT NOT NULL
REFERENCES Hobbies(Hobby_id)
PRIMARY KEY (Person_id, Hobby_id)) ;

The primary keys in the Persons table & Hobbies tables are assigned with the
assumption that there could be other relevant attributes associated with
these entities, otherwise using Name & Desc as keys are just fine. The data
for these tables, based on the information you provided could be like:

INSERT Persons SELECT 1, 'Darren' ;
INSERT Persons SELECT 2, 'John' ;
INSERT Persons SELECT 3, 'Peter' ;
INSERT Persons SELECT 4, 'Jill' ;
GO
INSERT Hobbies SELECT 1, 'travel' ;
INSERT Hobbies SELECT 2, 'eatingout' ;
INSERT Hobbies SELECT 3, 'disco' ;
INSERT Hobbies SELECT 4, 'swimming' ;
GO
INSERT PersonHobbies SELECT 1, 1 ;
INSERT PersonHobbies SELECT 1, 4 ;
INSERT PersonHobbies SELECT 2, 1 ;
INSERT PersonHobbies SELECT 2, 2 ;
INSERT PersonHobbies SELECT 2, 4 ;
INSERT PersonHobbies SELECT 4, 4 ;
GO

The above schema represents a m-to-m relationship between Persons and
Hobbies. It allows you to add persons and hobbies to the system without
having to alter the tables and facilitates efficient querying. Now, you can
have a SQL statement like:

SELECT p1.PersonName, h1.HobbyDesc, ...
FROM Persons p1
LEFT OUTER JOIN PersonHobbies ph1
ON p1.Person_id = ph1.Person_id
LEFT OUTER JOIN Hobbies h1
ON h1.Hobby_id = ph1.Hobby_id

Get the resultset to your client application & cross tab the data to the
format with comma, add words like "and" etc for the requirements for
display.

--
Anith