cast function on date prompt

[Nandini.t]

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

[HalfBloodPrince]

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

[Nandini.t]

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

[HalfBloodPrince]

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

[Nandini.t]

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

[HalfBloodPrince]

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 .

[Nandini.t]

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