IBM Cognos Cloud Date Parameter drill through failure
Issue:
While moving to Cognos cloud, report fails to pass Date parameter.
XQE-DAT-0001
Data
source adapter error: java.sql.SQLDataException: ORA-01830: date format
picture ends before converting entire input string
|
Version: Cognos 10.2.2 FP3/FP7
Database: Oracle 11g
Cause:
Cognos is treating Oracle parameters as Timestamp and cast/to_date function at both end doesn't work.
Solution:
- Create data item:
(extract(year, ?As
Of?) * 100
+
extract(month, ?As
Of?)
) * 100
+
extract(day, ?As
Of?)
2. Pass data item value instead of Parameter value
3. In target report, data item should have below ( to use for filtering) and/or displaying:
case when (length (?Date?)=8)
Then to_date(substring(?Date?, 1,4)||'-'||substring(?Date?, 5,2)||'-'||substring(?Date?, 7,2),'YYYY-MM-DD')
Else
to_date(?Date?,'YYYY-MM-DD')
end