Print Page - cast function on date prompt

Title: cast function on date prompt
Post by: Nandini.t on 27 Nov 2011 11:51:37 PM

Hi all

I have a my date as ' yyyymm' in the model query item(database ). e.g entrydate.

My filter is like this.
[entrydate] >= ?P_ENTRYDATE?

Obviously it does not work QE-DEF-0387 Invalid format for numeric prompt 'P_ENTRYDATE'.Could you please advise on how to cast/convert the Date Prompt value from Date to 201010 format and where to use it.

Please help.

Thanks,
Nandini

Title: Re: cast function on date prompt
Post by: HalfBloodPrince on 28 Nov 2011 12:20:11 AM

Hi try this
parameter1 is parameter of Date prompt

Create a query calculation new_Date as
cast(extract(year,?Parameter1?),char(4))+cast(extract(month,?Parameter1?),char(2))

then disabled the filter on your date and add this
Your Date column=New_date

only check with the values for the month like its 201105 or 20115 my solution wil work for 20115.
if its 201105 then u have make some extra efforts as

create Year ->cast(extract(year,?Parameter1?),char(4))

create Month as->
If(extract(month,?Parameter1?) >10) then
(cast(extract(month,?Parameter1?),char(4)))
else
('0'+cast(extract(month,?Parameter1?),char(4)))

then create New_Date as [Year]+[Date]

then apply the same filter
Your Date column=New_date

Title: Re: cast function on date prompt
Post by: Nandini.t on 28 Nov 2011 12:37:09 AM

hi prince as per your solution (then disabled the filter on your date) which filter to disable. i dont have any filter for date.

Title: Re: cast function on date prompt
Post by: HalfBloodPrince on 28 Nov 2011 12:39:06 AM

No just mean to say if u currently have added any filter on ur date column for filtering then disable it.else u can skip this step.
Try and let me Know

Title: Re: cast function on date prompt
Post by: Nandini.t on 28 Nov 2011 04:05:59 AM

Hi prince

I have a 'AND/OR' propmt in between the 2 date propmts which have the case statement as shown below.

case when (?Parameter AND-OR?)='AND'

then ((1 in_range ?P_ENTRDATE? OR [entrydate ]in_range ?P_ENTRDATE?) AND

(1 in_range ?P_TRANSACTION_DATE? OR
[entrydate] in_range ?P_TRANSACTION_DATE?))

else
((1 in_range ?P_ENTRYDATE? OR [entrydate] in_range ?P_BOOKING_ENTRY_MONTH?) OR

(1 in_range ?P_TRANSACTION_DATE? OR
[entrydate] in_range ?P_TRANSACTION_DATE?))

end

when i disable this filter iam not getting errors but mismatch of data. but when i enable it iam getting multilpe errors.
dont knw wat exactly is the problem. pl guide me

Title: Re: cast function on date prompt
Post by: HalfBloodPrince on 28 Nov 2011 04:41:40 AM

Hi,
Try to cast the new_date column which we derived from date prompt to integer As Cast([Year]+[Date], Integer). then keep
old filter as [entrydate] >= New_date

then enable Ur 'AND/OR' filter.

Thanks .

Title: Re: cast function on date prompt
Post by: Nandini.t on 28 Nov 2011 06:16:40 AM

Hi prince
i have 2 date prompts, 1 is 'entry date prompt' and other is 'trans date prompt'. my report data depends on And/Or selection prompt between these two date prompts.

little modification in the below case statement pl have a look and guide me further. there are 2 different data items on which 2 different respective prompts depends(P_ENTRDATE >> entrydate, ?P_TRANSACTION_DATE? >> transdate).

case when (?Parameter AND-OR?)='AND'

then ((1 in_range ?P_ENTRDATE? OR [entrydate ]in_range ?P_ENTRDATE?) AND

(1 in_range ?P_TRANSACTION_DATE? OR
[transdate] in_range ?P_TRANSACTION_DATE?))

else
((1 in_range ?P_ENTRYDATE? OR [entrydate] in_range ?P_BOOKING_ENTRY_MONTH?) OR

(1 in_range ?P_TRANSACTION_DATE? OR
[transdate] in_range ?P_TRANSACTION_DATE?))

end

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: Nandini.t on 27 Nov 2011 11:51:37 PM