IF THEN ELSE and CASE in FILTER

How can i use IF THEN ELSE and CASE statement together in a filter.
I have a condition that if the prompt static value is month then i have to check in CASE which month is it. This expression has to be used in filter.

You cannot use IF or CASE in the Filter expression. IF and CASE return values (text, numbers, dates, etc.) and a Filter expression MUST RETURN TRUE OR FALSE.

For example, a Filter can be [CITY]='CHICAGO'

a Filter cannot be IF([CITY]='CHICAGO') then (12) else (99) wrote:

Hi All,

How can i use IF THEN ELSE and CASE statement together in a filter.
I have a condition that if the prompt static value is month then i have to check in CASE which month is it. This expression has to be used in filter.

Hi,
You cant use if else and case both..you will have to use nested case .. and the expression will be something like...
Month_Namecase when(?parameter? ='month') then(
case('month') when ('Jan') then(1)....
...
abhishek

You are right. Cognos will not let you put it in the filter expression, which is why we often use SQL-based reports. You can use a CASE statement in a WHERE clause, but it has to be in the first position.

We are just making the conversion to C8, so I am intrigued if this is
possible. Like Opher, in C7 I could write conditional statements as data
items, but never as filters. Writing a conditional statement in a filter
will throw errors like this: QE-DEF 0217--Wrong Expression Type.
Thanks for the heads up that this has changed in C8.

All --> the "implied IF" was the only way I could get this
functionality to work.

I was checking to see if the current user was a member of a
certain group in CRN using the CSVIdentityNameList macro, then
applying the filter a different way (power users can see
non-filtered data). So, I did the security stuff in the model
(check to see if the power user group name is in
CSNIdentityNameList and set another data item (Is Power User) to
'True' or 'False' as appropriate), and my filter looks like
this:

This is the normal behaviour of ReportNet. Something about the parsing
algorithm must throw up the "errors found in other places in the report"
error.
If you go with the AND/OR syntax for filters, you should be OK in
future.

We also use IF statements in our filters. We have date parameter
driving all our queries so that people can see sales for a given
timeframe (using a drop-down box). It checks against a calendar
table to find out if we should be looking at the
day/week/month/year or another calendar table to get the same
keys for prior timeframes. It looks like this:

I reviewed the article on relative date prompting in SupportLink
v15n1 and that sounds almost exactly like what we'd like to do.
Only problem is that when we talk month-to-date (we call it
period-to-date) or year-to-date, we're talking about back to the
first day of the current accounting period or accounting year
which does not coincide with the first day of the calendar month
or year. Hence, I cannot take advantage of the SQL dateadd and
datediff functions to determine the relative start dates, but
rather need to look them up in a date dimension table. I'm
struggling to determine whether this is possible and if so how
to go about it. Any guidance would be more than welcome.

I have done similar tasks in the past. The issue is to design the date dimension table in such a way that whatever is needed to be looked up can be found on one row. In one example I can recall right now, the table had these columns:

I essentially have that in place, but my fact table contains only a
surrogate key to the date dimension, so I'm constraining my facts by
constraining Calendar_Date in the dimension table. So essentially what
I need for YTD is:

select sum(f.fact) as YTD
from fact_table f, date_dimension d
where f.date_key = d.date_key
and d.calendar_date between (select year_start_date from
date_dimension where calendar_date = current_date) and current_date

It's a simple enough lookup in SQL, but I'm struggling with how to work
that logic into Cognos reports. Any ideas?

If you always need to work it that way, don't leave it to the report writers - define the join that way in the Framework. That may require multiple aliases for the report writer to choose from. Other than that, user training....

I'm also trying to work with 'if then else' within ReportNet.
The data item expression is: [Daily LAN/WAN Measurements].[Bytes]/1048576 with Bytes1 as data item name.
The report expression used is: if (Bytes1 > 1) then 'Yes' else 'No'.
The result is an empty field. The goal is to display the word 'yes' if the report expression is greater then 1. And 'No' if equal or less.
What am I doing wrong?

Thank you we have a few snapshot type reports with filters using IF statements.

Users select a date option and that selects a date range based on sysdate - I needed to nest an additional clause so that on Monday current week would return previous week as we report from a datawarehouse refreshed nightly and the week begins on Monday so users were having to select previous week on a Monday, not ideal for scheduling either!

Using this makes scheduling a lot easier as we can save a report view with the date group set and whenever run the date range is dynamic.

Here's my very complicated example, the 'implied if' method is used for dategroup 1, notice how I chose to default to a set of data rather than not invoke the filter.

IF (?p_dategroup? = '1')
THEN
(
(_day_of_week ({sysdate},1)=1 AND (_add_days ({sysdate},-7) between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] and [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date]))
OR
(_day_of_week ({sysdate},1)>1 AND ({sysdate} between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] AND [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date]))
)

ELSE IF (?p_dategroup? = '2')
THEN
(_add_days ({sysdate},-7) between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] and [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date])

You can use if then or else statements in a filter. I have plenty of reports based on this idea. The way to do it would be to simply make all the outputs filter statements. And note all includes the else statements. Rather then nested if statements I would use a case though. Have a when for date groups 1 and 2 and make your else the 3rd. Then use static values on your prompt so it's required and they can only choose 1, 2, or 3. So, a cleaner solution to this would be:

case ?p_dategroup?
when 1 then ((_day_of_week ({sysdate},1)=1 AND (_add_days ({sysdate},-7) between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] and [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date]))
OR
(_day_of_week ({sysdate},1)>1 AND ({sysdate} between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] AND [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date]))
)
when 2 then (_add_days ({sysdate},-7) between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] and [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date])

Hey Elena,
Instead of using the If ELSE or CASE statements, you can use the AND/OR to combine the conditions. Kindly send the exact IF ELSE statement, and may be I can help you with the AND/OR statement for the same.

We have used nested if and case statements, usually where we want radio buttons to enable user to have predefined date ranges (current / previous week for example) or a user defined date range.

Example 1:

IF (?p_dategroup? = '1')
THEN
(
(_day_of_week ({sysdate},1)=1 AND (_add_days ({sysdate},-7) between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] and [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date]))
OR
(_day_of_week ({sysdate},1)=1 AND (_add_days ({sysdate},-14) between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] and [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date]))
OR
(_day_of_week ({sysdate},1)>1 AND ({sysdate} between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] AND [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date]))
OR
(_day_of_week ({sysdate},1)>1 AND (_add_days ({sysdate},-7) between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] and [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date]))
)

ELSE IF (?p_dategroup? = '2')
THEN
(_add_days ({sysdate},-7) between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] and [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date]
or
_add_days ({sysdate},-14) between [WIP History Weekly].[Calendar Time - Snapshot Date].[Week Start Date] and [WIP History Weekly].[Calendar Time - Snapshot Date].[Week End Date])

Copyright 1998-2015 Ziff Davis, LLC (Toolbox.com). All rights reserved. All product names are trademarks of their respective companies. Toolbox.com is not
affiliated with or endorsed by any company listed at this site.