parameter map help

[tupac_rd]

Hi Gurus,

We have created a Parameter Map in FM, also created a calculation based on the parameter map, and trying to use it in Report Studio.

The parameter map is not created manually, but instead using the option, "Base the parameter map on existing Query Items." So, Parameter Map is driven by our control table which is dynamic since the ETL process updates it nightly.

The query item we use for "Set as Value" has the Date Time datatype in the database.

So, the calculation we have based on the parameter map is
#sq($Max_Date{'10000'})#

Max_Date is the name of the Parameter Map, and 10000 is the "Set as Key" value. And the expression I get is 'May 31, 2010 12:00:00 AM'.

So, when I drag this calculation directly as a column in a report, I am able to run the report and I can format the column as if it is a date time datatype. But the problem is when I try to use it in a filter expression.

for eg. if my filter expression is

[Model View].[Date Dimension].[Date] = [Date Calculation], I get the error saying inconsistent datatypes timestamp2 and string. So the calculation I created using the parameter map is being considered as string. I tried all different cast functions to make them the same datatype, but it doesn't work.

Please let me know if I am doing something wrong, or if there is a better way of doing it.

Thanks
2pac

[CognosPaul]

#sq()# will wrap an expression in single quotes, thus making it a string. Cognos can take a string and format it on the report level but it is still a string when using it in a query. What happens if you remove the sq?

[tupac_rd]

if I remove sq, the expression is not valid.

I am using a to_timestamp oracle function, and now it is working fine. I didn't know that there was a to_timestamp funciton in Oracle. I was trying to use the Cognos function cast([date],timestamp), but that did not work.

[san_bi]

Hi 2Pac,

I am also facing the same issue that you had in FM while using paramter map for filtering date based column. I tried converting the filter to timestamp but it didnt work.

Can you please provide the exeact change that you did?

My Expression and actual filter are below:

[STAGE2].[ROLLING_DATES_V].[ROLL_DATE]  > to_timestamp(#sq($CustomDates{'MIN6'})#, 'DD-Mon-RRRR HH:MI:SS AM')

[STAGE2].[ROLLING_DATES_V].[ROLL_DATE]  > to_timestamp('Apr 5, 2014 12:00:00 AM', 'DD-Mon-RRRR HH:MI:SS AM')

Based on MIN6 as key the value being returned is 'Apr 5, 2014 12:00:00 AM'.

Error is ORA-01858: a non-numeric character was found where a numeric was expected

Please advise.

Thanks in Advance!