How do obtain a distinct list of string values?

[bonniehsueh]

I need to create a distinct list of product descriptions for a product prompt. There is a 'distinct' function. The issue is that it is to be used with an aggreate description.

DISTINCT dataItem
A keyword used in an aggregate expression, to include only distinct occurrences of values. See also the function unique.

So far I have tried:
distinct [Business View - Relational].[Product].[Product Family Desc]
distinct ([Business View - Relational].[Product].[Product Family Desc])

I checked the function Unique. It can only be used on dimensional data.

I imagine there is a way to create a distinct list of values in Framework manager, but would prefer to do something in Report studio.

Any ideas would be appreciated.

Thanks!

[CognosPaul]

The easiest way to do this is to set up a dedicated query for the prompt and ensure the auto-group and summarize is set to Yes. The SQL generated for the prompt will be something like (for SQL Server at least):

Code Select Expand

select distinct p.name AS Name, f.name_cd AS name_cd from Warehouse.dbo.parameter p, Warehouse.dbo.fact f where p.name_cd = f.name_cd

When the auto-group and summarize is set to no, it will remove the distinct from the SQL statement.

That being said, I find it is more efficient to reference the parameter tables directly. I have an FM package set up specifically for RS users. They have the presentation layer, plus direct access to the tables themselves. Instead of taking the labels and codes from the presentation layer, which would automatically create the joins in SQL, they take them directly from the parameter tables thus only referencing 1 table and removing the need for a costly DISTINCT statement:

Code Select Expand

select p.name AS Name, p.name_cd AS name_cd from Warehouse.dbo.parameter p