Duplicate values getting in prompt for Month-Year

[cognosbi.dwh]

Hi Everyone,

I'm using cognos 10.1 version. In my report studio, I have Timestamp column and converted to Date format as cast([Timestamp] ,Date) and given Dateformat as 'MM-YYYY' in propertie pane for that column, which is giving result as below. May I know how can I remove duplicates and have to do sorting. I need to use this column in value prompt.

[DataItem1]

Feb-2020
Feb-2020
Apr-2020
Aug-2020
Feb-2020
Jan-2020
Jan-2020
Apr-2020
Feb-2020

Can anyone please help me on this.

Thanks,
Swetha

[MFGF]

Hi Everyone,

I'm using cognos 10.1 version. In my report studio, I have Timestamp column and converted to Date format as cast([Timestamp] ,Date) and given Dateformat as 'MM-YYYY' in propertie pane for that column, which is giving result as below. May I know how can I remove duplicates and have to do sorting. I need to use this column in value prompt.

[DataItem1]

Feb-2020
Feb-2020
Apr-2020
Aug-2020
Feb-2020
Jan-2020
Jan-2020
Apr-2020
Feb-2020

Can anyone please help me on this.

Thanks,
Swetha

Hi,

What you are seeing is correct here. Although your display format is MM-YYYY, you still have complete dates held in the item behind the scenes, so although it looks like you are getting duplicates because of your format, you are not, in fact. You will see this if you remove the format and run the report again. If you really want just month and year you're going to have to put those values into the item - ie extract those parts from the complete date.

Cheers!

MF.

[cognosbi.dwh]

Thanks for your Reply!!

I have created Dataitem by using my [Timestamp] column as : cast(extract(Month, [Timestamp]), char(4))+'-'+cast(extract(Year, [Timestamp]), char(4)) which is giving result as

2 -2020
2 -2020
4 -2020
4 -2020
2 -2020
2 -2020

Thanks,
Swetha

[cognosbi.dwh]

Finally it's working after creating one more dataitem with maximum(Timestamp for Month) from above columns with Timestamp and other dataitem. It results giving only Month and Year not getting any duplicates.

Thank,
Swetha