Dataset Results

I have created adataset to make a drop down list for a parameter. As shown:

SELECT distinct [Table] FROM [Database]

The results i get back are A, B & C. What i am trying to do is show a result as a different word.

i.e instead of displaying A is shows in drop down list as Apple, B shows Banana. etc.

Can anyone help please.

Thanks

Lookup the usage of case(sql server). This should get you your desired result.|||The parameter can use two columns of the returned dataset, one for the value column and one for the display column, getting this back from the query and binding it to the appropiate properties in the parameter setting should help.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Ok got it working.

select distinct

case [COLUMN NAME]

when 'A' then 'Apple'

when 'B' then 'Banana'

else 'Unknown'end

from [TABLE NAME]

where [COLUMN NAME] in ('A', 'B', 'C')

Dataset Distinct Query

I am trying to build a report with a prompt to select building. It all works fine except I in the prompt building is list as may times as there is data for it. I want it to only display once, but not effect the other data is there a way I can query this?

I have my 2 datasets and my parameter set up to display building to select from. My issue is I get multiple values in the Select Value Prompt. It will pull the correct data I just want to clean up the Select Value Prompt.

Example: of prompt list

Building 1

Building 1

Building 1

Building 2

Building 2

Dataset 1:

Select Building, First, Last

From Table

Dataset 2:

SELECT Building, First, Last

From Table

WHERE (Table.Building = @.Building)

Hi Ray,

You can use the DISTINCT clause:

Select DISTINCT Building, First, Last

From Table

Cheers,

Rob

|||That doesn't work|||What had to do is make a 3rd dataset in the report, w/ just the select distinct statement in it and then, change the available values dataset to the one that is just the select distinct.sql