Print Page - [solved] Slight problem with day_of

Title: [solved] Slight problem with day_of_week
Post by: VisioX on 23 Jun 2008 11:40:47 AM

Hello Everyone,

I am working on a dashboard with several items on it.
One is a list with a filter.
If I set the filter to

Code Select

[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-1)
it works fine, but on Sundays and Mondays I have to subtract 2 or 3 days.

Filter set to

Code Select

if (
1 = 1
) then (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-3)
) else (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-1)
)

works good, but that was just a test, still missing my Sunday/Monday solution.

Now it set the filter to

Code Select

case _day_of_week(current_date,1)
when (1) then ([Dashboards].[UTILSUM].[UTEND] =_add_days(current_date,-3))
when (7) then ([Dashboards].[UTILSUM].[UTEND] =_add_days(current_date,-2))
else ([Dashboards].[UTILSUM].[UTEND] =_add_days(current_date,-1))
end

and get following errors...

Code Select


UDA-QOS-0006 Error
UDA-SQL-0358 Line 20: Syntax error near "=".
QE-DEF-0459 CCLException
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 20: Syntax error near "=".
UDA-QOS-0006 Error
UDA-SQL-0358 Line 20: Syntax error near "=".
QE-DEF-0459 CCLException
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 20: Syntax error near "=".
RSV-VAL-0004 Unable to find query information for the item UTSTR1.
RSV-VAL-0004 Unable to find query information for the item UTEND1.
RSV-VAL-0004 Unable to find query information for the item UTRGN.
RSV-VAL-0004 Unable to find query information for the item UTCMAJ.
RSV-VAL-0004 Unable to find query information for the item UTLMAJ.
RSV-VAL-0004 Unable to find query information for the item UTCCON.
RSV-VAL-0004 Unable to find query information for the item UTLCON.
RSV-VAL-0004 Unable to find query information for the item UTCOWN.
RSV-VAL-0004 Unable to find query information for the item UTLOWN.
RSV-VAL-0004 Unable to find query information for the item UTCOTH.
RSV-VAL-0004 Unable to find query information for the item UTCALL.

No big deal, let's change the code to

Code Select

if (
_day_of_week(current_date,1) = 1
) then (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-3)
) else (
if (
_day_of_week(current_date) = 7
) then (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-2)
) else (
[Dashboards].[UTILSUM].[UTEND] = _add_days(current_date,-1)
)
)

...well, same error code like the CASE version.

Do I miss something essential or is a list not allowed to use a simple _day_of_week function in a filter?

Thanks,
Guido

Title: Re: Slight problem with day_of_week
Post by: blom0344 on 23 Jun 2008 01:00:32 PM

You cannot use an eqaulity sign in if then else or CASE constructs in the then or else part.
For a given data item

[Dashboards].[UTILSUM].[UTEND]

the definition should be:

Code Select


if (_day_of_week(current_date,1) = 1) 
then (_add_days(current_date,-3)) 
else (if (
_day_of_week(current_date) = 7) 
then (_add_days(current_date,-2)) 
else (_add_days(current_date,-1)))

Better use a CASE construct for multiple THEN parts, that is more readable (just a tip)

Title: Re: Slight problem with day_of_week
Post by: VisioX on 23 Jun 2008 01:10:50 PM

All right!

I ended up with this:

Code Select

[Dashboards].[UTILSUM].[UTEND] =
case _day_of_week(current_date,1)
when (1) then (_add_days(current_date,-3))
when (7) then (_add_days(current_date,-2))
else (_add_days(current_date,-1))
end

Works great, thanks!

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: VisioX on 23 Jun 2008 11:40:47 AM