Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

distinct values

distinct values

distinct values

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.

RE: distinct values

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.

RE: distinct values

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.

RE: distinct values

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.....

RE: distinct values

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)

RE: distinct values

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 !

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close