IF-THEN-ELSE issue

[rhythmz]

I have a report that allows the user to multi-select MAN (displays 'M') or WOMAN (displays 'W') in a prompt (?tot_tm?).

I have 2 calculations:

MAN
total (TABLE_A.TOT_MEN_TM for month)

WOMAN
total (TABLE_B.TOT_WOM_TM for month)

The report will return a result set grouped by each gender with a column returning a total_time value based on:

TOTAL_TIME
if (?tot_tm?='M') then ([MAN]) else if (?tot_tm?='W') then ([WOMAN]) else NULL

My problem now is that when I select BOTH 'M' & 'W' in the prompt, I fail to return any data like I would if I selected just 'M' or 'W'. Ultimately, if the user selects both prompt values, I want the report to show the total_time for each person, grouped by their gender, just as it currently does if they selected just one of the prompt values.

How do I define a column based on a prompt selection if BOTH prompt values are selected?

I am dumbstruck...THANKS!

[uttam.mistry]

if you expect multiple value from prompt you can not use '=' operator
you should use 'in' operator

like
if ('M' in ?tot_tm?)

i am not able to understand your requirement clearly... but can not you use filter?(rather than using if condition)

or

may be you can use
MAN = total(if ('M' in ?tot_tm?) then (TABLE_A.TOT_MEN_TM for month) else null)
WOMAN = total(if ('W' in ?tot_tm?) then (TABLE_B.TOT_WOM_TM for month) else null)
TOTAL TIME = [MAN]+[WOMAN]

or

MAN = total(TABLE_A.TOT_MEN_TM for month)
WOMAN = total(TABLE_B.TOT_WOM_TM for month)
TOTAL TIME = total(if ('M' in ?tot_tm?) then (TABLE_A.TOT_MEN_TM for month) else if ('W' in ?tot_tm?) then (TABLE_B.TOT_WOM_TM for month) else null)

actual expression will be based on you requirement... i m trying to give you some hint.. (in case you dont know this!!)

[rhythmz]

Your advice is greatly appreciated and definately turned on a lightbulb...but unfortunately, did not work. My shop has just deployed 8.4 from 7.3 so I am still learning the ropes of 8.4 Report Studio. However, maybe I should clarify further with background logic. I am building a report for training hrs. performed in a month for men & women (headers & footers for each gender). Each gender has unique minimum hrs. to maintain. I will further get an AVG of each gender and then calculate a standard deviation for a time period of 1 month.

The user will select from a prompt 'Male' or 'Female'. They can mutli-select both of these (to save paper).

Based on this logic, I have a calc for each gender totaling the hrs. for each person within each month.

total(TABLE_A.TOT_MEN_TM for month)
total(TABLE_A.TOT_WOM_TM for month)

However, I am not adding these totals together. I want the correct calc (MAN or WOMAN) to display next to the person under the gender header (i.e. Dan's MAN next to his name, Mary's WOMAN next to her name). I am able to return data if I select EITHER prompt but unable to if I select both prompt values ('Male' and 'Female') with the following calculated column:

if (?tot_tm?='M') then ([MAN]) else if (?tot_tm?='W') then ([WOMAN]) else NULL

I tried your suggestion unsuccessfully of:

if ('M' in ?tot_tm?) then ([MAN]) else if ('W' in ?tot_tm?) then ([WOMAN]) else null

Again, the report returns data if I use either one of the prompt values but not both. This help further explain?

Thanks again for your help because it definately was a great idea.

[jejones75]

I had so many frustrations getting if statements to work, I almost always use case statements now - and they work every time.  Cognos 8 is also very picky about selecting a field (by double clicking on it) versus typing out the name of the field.   

[uttam.mistry]

actually speaking the expression

if ('M' in ?tot_tm?) then ([MAN]) else if ('W' in ?tot_tm?) then ([WOMAN]) else null

does not make any sense, because if user selects both the values, above expression will only give you the sum of [MAN]....

i think, what you want to do here is filter the data based on prompt and use expression
MAN = total(if ('M' in ?tot_tm?) then (TABLE_A.TOT_MEN_TM for month) else null)
WOMAN = total(if ('W' in ?tot_tm?) then (TABLE_B.TOT_WOM_TM for month) else null)
TOTAL TIME = isnull([MAN],0)+isnull([WOMAN],0)

[rlp]

I'd try something like this:

if (?tot_tm?='M') then ([MAN]) else if (?tot_tm?='W') then ([WOMAN]) else (if ('M' in ?tot_tm? and 'W' in ?tot_tm?) then ([MAN]+[WOMAN]) else (null))

Randy