Eliminating duplicate values in the prompt

[leon]

Hi All,
I would like to remove duplicate values in the prompt: specifically 'year'.
Let's say i am having years in the prompt as:

2009
2009
2009
2010
2010
2011
2012

and it needs to be distinct values only. I have a feeling to use either 'distinct' or 'unique' function, but i am not sure how.
is anyone could you help me?

Would like to add that source is dimentional.

Appreciate your help,

         Leon

[Francis aka khayman]

classic symptom of dimensional source. the businesskey of those values are actually different but the caption are the same. that is why three items, which seems the same, are displayed. this might help:

http://cognosm.fm3online.com/dimensional/stripping-dimensionality-of-a-member/

[leon]

Thank you, khayman.
But i am not sure why i specified that datasource ias dimentional, if it is relational.
Still looking for answer...
Thanks,
          Leon

[Francis aka khayman]

if the source is relational, it is still possible that the prompt use values are different while the display values are the same.

there are also other causes. for example the source query might have been set to Auto Group & Summarize = No, or the one data items have the same description but another data item in the query has unique value (year id? and year description?) or there are duplicate entries in the prompt static choices, to name a few.

[leon]

Thanks again, khaiman. But here is the details: in reality the data populating into the prompt should look like this:

May 10, 2010 12:00:00 AM
Mar 28, 2001 12:00:00 AM
Dec 1, 2008 12:00:00 AM
Jun 1, 2007 12:00:00 AM
Apr 28, 2013 12:00:00 AM
Jun 2, 2013 12:00:00 AM
May 6, 2013 12:00:00 AM

But because of the convertion (to_char([Model Correction].[BMS R20].[ACTUAL_START_DT],'YYYY')), the data in the prompt looks like this:

2010
2001
2008
2007
2013
2013
2013

The goal is to eliminate duplicate values. Actually it is also a second goal to sort the values (from lower year to higher). I hope it is clear now. If not, please let me know.

[Francis aka khayman]

the query used in your prompt should have:

[Date]                                                  [Year]
May 10, 2012 12:00:00 AM               extract(year,[Date])

in your prompt you use [Year] as the Use Value.
For sorting, you can set the sorting property to [Year] as well.

[leon]

Sorting works perfectly.
But unfortunately the original issue giving me the error when it is trying to pass value to the report (prompt values look like this:
2,009
2,010
2,011
2,012
2,013
2,014
- no duplicates):
Need to resolve prompts. XQE-PLN-0105 Invalid format for prompt 'P_year'. Expected format for datetime: CCYY-MM-DDThh:mm:ss.
if I'll follow your instructions. Any recommendations?
Thank you for your help,
   Leon

[Francis aka khayman]

you can eliminate the comma by setting the data type property of Year.

the error you mention is because now, after making your prompt display a distinct year, your prompt is passing number as the chosen value (2009) while the query expects datetime.

so let us take a step back here... in your previous example, you have three 2013 values (april 29, june 2 and may 6). you wanted to display only one 2013 in your prompt. so now if the user selects 2013, which value do you want to pass to your query? is it april 29, june 2, may 6?

[leon]

I guess it should be all 3 of them. I believe it is the way how it should work. Isn't?

         Leon

[Francis aka khayman]

Yup looks like it.

[leon]

Thank you, Khayman.
So, what is the solutiion for resolving this situation? It appears now you understand the details.
Thank you for your help,

            Leon

[leon]

Anyone has an ideas? I was under impression that this forum is so powerful that could help to resolve any issues within Cognos.

Appreciate you respond.

            Leon

[Francis aka khayman]

wait... what is it you want to accomplish?

from what have been discussed you have no choice but to show the three values if you want to pass one of them to your report filter.

[MFGF]

Anyone has an ideas? I was under impression that this forum is so powerful that could help to resolve any issues within Cognos.

Appreciate you respond.

            Leon

Leon,

The forum is a place where Cognos users and developers can help each other if they have time and if they happen to have an idea that might help. It's not a place where you can demand an answer or make statements like this when you don't get a solution given to you by someone else. Please be courteous to other forum members who are giving up their own time to read your questions and help if they can.

MF.

[Lynn]

I guess it should be all 3 of them. I believe it is the way how it should work. Isn't?

         Leon

If I understand correctly you have transformed a list of dates to display distinct years to use in a prompt control. Now you want to apply the selected year as a filter against your date data item to pick up any data where the date is in the year selected. Is that it?

Khayman already gave you the keys to the castle. You converted your dates to years in the prompt query by using extract(year, [Date]) so just do the exact same thing using the date data item against which you want to apply the filter.

Your filter expression would end up looking something like ?P_year? = extract(year, [YourDate])

[leon]

MG,
I am not sure I understand what part of my post appears to be abusive. If so - my apologies. I didn't mean to demand anything since it is pretty clear that it is a voluntary forum, not a paid support . I really appreciate 'khayman' who's been great trying to help me by giving the bunch of possible solutions.
I just asked open forum (as within any real open forum) if anyone has any other ideas since solution provided was not working. (with expectation of 'no respond' meaning that solution given is the only possible). I don't believe this question somehow violates your forum's policy. If it is, my apologies again.

Lynn,
That is exactly right: the cause of my issue is in the filter – the data type that parameter expects (date/time) not consistent with data format (year) getting passed from the prompt. That's where conflict is - it should be one way or another.
Khayman provided me the solution that I should be using as a main line, but since I am not a Pro in Development I didn't get the message. Now it makes more sense.

Anyway, I greatly appreciate you guys for your help and support.

Thank you,
   
   Leon