Date prompt not filtering

[Kid_in_Cognos]

Hi,

I have the data as below. I want to filter the claim id based on the selected CLM_OPN_DT and CLM_CLOSE_DT from the prompt page.


      CLM_OPN_DT                        CLM_CLOSE_DT                 CLAIM_ID
Jan 13, 2014 12:00:00 AM        Jan 8, 2014 12:00:00 AM       50378
Jan 16, 2012 12:00:00 AM        Jan 28, 2012 9:10:05 PM       50486
Jan 16, 2014 12:00:00 AM        May 27, 2014 4:02:11 AM       50500
Jan 20, 2015 12:00:00 AM        Apr 24, 2015 9:39:32 PM       50554
Jan 21, 2014 12:00:00 AM        Oct 17, 2014 11:03:31 PM       50593


I used 2 date prompts for CLM_OPN_DT and CLM_CLOSE_DT in the prompt page. In the report page I have used all 3 data items. But the filter is not happening. Report is fetching data for all the years.

If use the filter, [CLAIM_ID] BETWEEN ?CLM_OPN_DT? and ?CLM_CLOSE_DT?, I am getting empty data. Can you let me know Where I am doing the mistake?. Curious to know

Thanks

[Nagendar]

Hi Kid_in_Cognos,
Better to use Date time prompts.
try with < and  > operators. And also time might be the reason for not displaying the data.


Thanks
Nagendar

[Kid_in_Cognos]

Hi Nagendar,

Thanks for your reply.
I tried with Date Time prompt and used < and > operators already . Sorry as I not mentioned this in my earlier post.

[HalfBloodPrince]

 Give a Try
CLM_OPN_DT >=?CLM_OPN_DT? AND CLM_CLOSE_DT  <=?CLM_CLOSE_DT?

[Binway]

Not sure if this helps but using this data as is in a sql server database
CLM_OPN_DT                        CLM_CLOSE_DT                 CLAIM_ID
Jan 13, 2014 12:00:00 AM        Jan 8, 2014 12:00:00 AM       50378
Jan 16, 2012 12:00:00 AM        Jan 28, 2012 9:10:05 PM       50486
Jan 16, 2014 12:00:00 AM        May 27, 2014 4:02:11 AM       50500
Jan 20, 2015 12:00:00 AM        Apr 24, 2015 9:39:32 PM       50554
Jan 21, 2014 12:00:00 AM        Oct 17, 2014 11:03:31 PM       50593
I notice that there are a couple of Claim Close Dates that are before the open date which might give you some data errors.
I also found that the dates seem to be viewed as a string in Report Studio so I created some new calculations in the Framework Model and cast them using the following code.
cast(day([great_outdoor_sales].[KidInCognos].[CLM_OPN_DT]),char(2))||'/'||cast(month([great_outdoor_sales].[KidInCognos].[CLM_OPN_DT]),char(2))||'/'||cast(year([great_outdoor_sales].[KidInCognos].[CLM_OPN_DT]),Char(4))
So the data in the cognos report looks like this table

CLM_OPN_DT   CLM_CLOSE_DT   CLAIM_ID   NW_CLM_OPN_DT   NW_CLM_CLS_DT
Jan 13, 2014 12:00:00 AM   Jan 8, 2014 12:00:00 AM   50378   13/1 /2014   8 /1 /2014
Jan 16, 2012 12:00:00 AM   Jan 28, 2012 9:10:05 PM   50486   16/1 /2012   28/1 /2012
Jan 16, 2014 12:00:00 AM   May 27, 2014 4:02:11 AM   50500   16/1 /2014   27/5 /2014
Jan 21, 2014 12:00:00 AM   Oct 17, 2014 11:03:31 PM   50593   21/1 /2014   17/10/2014

The date prompts then seemed to work for me.

[raj_aries81]

Hi,

I have the data as below. I want to filter the claim id based on the selected CLM_OPN_DT and CLM_CLOSE_DT from the prompt page.


      CLM_OPN_DT                        CLM_CLOSE_DT                 CLAIM_ID
Jan 13, 2014 12:00:00 AM        Jan 8, 2014 12:00:00 AM       50378
Jan 16, 2012 12:00:00 AM        Jan 28, 2012 9:10:05 PM       50486
Jan 16, 2014 12:00:00 AM        May 27, 2014 4:02:11 AM       50500
Jan 20, 2015 12:00:00 AM        Apr 24, 2015 9:39:32 PM       50554
Jan 21, 2014 12:00:00 AM        Oct 17, 2014 11:03:31 PM       50593


I used 2 date prompts for CLM_OPN_DT and CLM_CLOSE_DT in the prompt page. In the report page I have used all 3 data items. But the filter is not happening. Report is fetching data for all the years.

If use the filter, [CLAIM_ID] BETWEEN ?CLM_OPN_DT? and ?CLM_CLOSE_DT?, I am getting empty data. Can you let me know Where I am doing the mistake?. Curious to know

Thanks

Hi Kid_in_Cognos,

If its not timestamp sensitive, I would recommend to cast/convert the CLM_OPN_DT & CLM_CLOSE_DT to Dates and then give it a try, like Binway mentioned,  pls make sure its a timestamp not a string.

Thanks
Raj

[Kid_in_Cognos]

Hi all ,

Thanks for your replies.

And it Worked as Binway suggested  :) :) :) :)

Cheers !!!