Cognos FM error QE-DEF-0260

[cjpaisley]

Hie guys,

i quite new to cognos, so i am trying to create a time dimension, but there is no time dimension in my reporting database so i'm trying to manually create it from the dates the system stores.

i intend to create a week day query item, which will give the day of the week the date is, e.g Monday, Tuesday etc. I thus intend to achieve this by first getting the day of week value using the _day_of_week function of Cognos, then i hv created a parameter map which maps the returned key to a value, e.g. 1 should then return Monday, 2 Tuesday etc: i have thus edited my definition as follows:

#$Weekday_lookup{_day_of_week ([OkMart].[DAY_PLU_SALES].[DT],1)}#

but i get this error:

QE-DEF-0260 Parsing error before or near position: 33 of: "#$Weekday_lookup{_day_of_week (["

Can someone pliz help...

Rgds

 

[blom0344]

No need to use such a complex way.
All you need is to use a CASE construction to re-code:

CASE
_day_of_week([OkMart].[DAY_PLU_SALES].[DT],1)
WHEN 1 THEN 'Monday'
WHEN 2 THEN 'Tuesday'
...
...
WHEN 7 THEN 'Friday'
ELSE null
END

You can use such a defintion WITHIN the dimension itself (for _memberCaption ) or define it within the relational layer as a seperate query-item.

My advice: If you can solve issues using SQL defintions, then so much the better!!

Ties Blom