distinct values

[renu123]

I have lookup table having repeating names.I need to create a prompt for choosing names. The prompt comes up with repeating values. Is there a way to display unique or distinct names from this lookup (create dan attribute for this)
Please respond.
Thanks in advance.

 

[Guest_imported]

The lookup table for any attribute should ONLY contain the distinct ID's for that particular attribute. Why do you have repeated names in the lookup table? Maybe the Names (DESC) can be repeated but the ID should be distinct.

 

[renu123]

The ID is distinct. But, DESC has repeating values. Now, if I create an attribute on the DESC itself, I do get a unique list of values to be shown in the prompt , but, the report results get messed up.
If I select name1 from the prompt, I do need to show all the occurances of name1 with their respective values on the report.
Right now, in the prompt, I display ID alongwith the DESC, so that the user gets some clue about the repeating values.
But, for selection purpose, I want to show a distinct list in the prompt and not repeating values.

 

[Guest_imported]

well, i guess i understand what you are trying to do.
however, HOW can you distinguish among the various names if the DESCs are the same but the IDs are the same.

For example if Greg, 1 and Greg, 2 are in the lookup table, how is the sql engine suppose to figure out which 'GREG' to use if it is only joining on the ID. The DESC is not a joining column. Can you give me a little more info? If the user selects 'Greg' should BOTH NAMES (1,2) be returned?

What you could do is create a Filter that only lists the Distinct Names and Use this Filter in a Hierarchy of that Name Attribute. If a hierarchy prompt is used on the report, only the names in the filter should appear.

i need some more information.....

 

[Guest_imported]

Sorry about that.
You can apply a filter to a hierarchy.
If you create a custom/advanced filter that returns the
Distinct NAMES in the lookuptable, then you can apply that filter to a hierarchy. To create the filter you can use a pass through function (Applysimple, ApplyComparison)
You can use something similar to:

("#0 in (select distinct DESC from LU_Whatever)", [Attribute]@DESC)

 

[renu123]

I had tried out the custom filter initially by using ApplySimple. But, it had'nt worked. One thing I had'nt tried was that I didn't apply this filter to a hierarchy.
I'll try doing that.

Thanks !