missing months in value prompt

[chinnucognos]

Hello everyone,

I have used data items as
1.[MM-YYYY]--------------------((to_char([Customized Queries].[SALES ADMIN DEPOSIT ACCT REPORT].[CALENDAR_DATE],'YYYY - Month')))
2.[Current year&month]-----(to_char(current_date, 'YYYY - Month'))
3.[last two years]-------------to_char(add_months(current_date,-24))

filters are:[MM-YYYY] <= [Current year&month]
                [MM-YYYY] >= [last two years]

In prompt page,values are displaying skipping some months in last year i.e 2012 months.
I tried in toad, made some changes in query like this:
    "where to_date("T"."CALENDAR_DATE",'DD-MON-YY') <= to_date(current_date) and to_date("T"."CALENDAR_DATE",'DD-MON-YY')  >= to_date(add_months(TRUNC( CURRENT_DATE ), -24))"
but not able to implement in cognos .

Please help on this.....

[MFGF]

Hi,

Take a look at the query retrieving the months for your prompt. If you view tabular data on this query you will see the results it is returning. Is it possible that there are months missing in the data feeding this query?

MF.

[chinnucognos]

Query returning all values, not only previous two  years but also rest of years data.
The query which i have written in toad is working fine with exact two years data
query used in toad:  *******   where to_date("T"."CALENDAR_DATE",'DD-MON-YY') <= to_date(current_date) and to_date("T"."CALENDAR_DATE",'DD-MON-YY')  >= to_date(add_months(TRUNC( CURRENT_DATE ), -24))

but when comes in cognos, iam not able to implement above condition ...
cognos native sql generated : ******* where to_char("T"."CALENDAR_DATE", 'YYYY - Month')<=to_char(TRUNC( CURRENT_DATE ), 'YYYY - Month') and to_char("T"."CALENDAR_DATE", 'YYYY - Month')>=to_char(add_months(TRUNC( CURRENT_DATE ), -24))

[MFGF]

Hi,

Is there a reason why you are doing all the casting to character strings and truncating? Have you tried a detail filter with the following syntax:

[CALENDAR_DATE] <= current_date and [CALENDAR_DATE] >= _add_months (current_date, -24)

MF.

[chinnucognos]

the data in CALENDAR_DATE is in "TIME STAMP", user wants the data in "month-yyyy" format i.e January-2013.....etc
so i used that query....
I have tried ur query, but still three months 2012 is missing and data is throwing morethan two years...
the missing months are somewhere below ain all values...............

[MFGF]

I have tried ur query, but still three months 2012 is missing and data is throwing morethan two years...
the missing months are somewhere below ain all values...............

So you're saying that this query retrieves data that is outside the filter range? How can that be possible? What do you see if you view the generated SQL?

Cheers!

MF.