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,
Better to use Date time prompts.
try with < and > operators. And also time might be the reason for not displaying the data.
Thanks
Nagendar
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.
Give a Try
CLM_OPN_DT >=?CLM_OPN_DT? AND CLM_CLOSE_DT <=?CLM_CLOSE_DT?
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.
Quote from: Kid_in_Cognos on 27 May 2016 05:21:49 AM
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
Hi all ,
Thanks for your replies.
And it Worked as Binway suggested :) :) :) :)
Cheers !!!