Data Expression - how to combine multiple items into Case statement

[ry1633]

Hello,

I am trying to figure out how to combine several items into one Case statement for a prompt in Report Studio.   My users have requested to query for any "aquamarine" species in a table for use in a report prompt such as fish, alligator etc.  I don't understand how to have more than one item in a Case statement.   I've tried it several ways and know have worked:

CASE
WHEN [...species field] contains 'Fish' or 'Alligator'
THEN [...species field]
ELSE 0
END

CASE
WHEN [...species field] contains 'Fish'
OR
WHEN [...species field] contains 'Alligator'
THEN [...species field]
ELSE 0
END

CASE
WHEN [...species field] contains 'Fish'
AND
WHEN [...species field] contains 'Alligator'
THEN [...species field]
ELSE 0
END

[Lynn]

Hello,

I am trying to figure out how to combine several items into one Case statement for a prompt in Report Studio.   My users have requested to query for any "aquamarine" species in a table for use in a report prompt such as fish, alligator etc.  I don't understand how to have more than one item in a Case statement.   I've tried it several ways and know have worked:

CASE
WHEN [...species field] contains 'Fish' or 'Alligator'
THEN [...species field]
ELSE 0
END

CASE
WHEN [...species field] contains 'Fish'
OR
WHEN [...species field] contains 'Alligator'
THEN [...species field]
ELSE 0
END

CASE
WHEN [...species field] contains 'Fish'
AND
WHEN [...species field] contains 'Alligator'
THEN [...species field]
ELSE 0
END

I'm not sure where your case statement comes into the picture. Are you attempting to use that as a filter in the query? You really only need an expression that evaluates to true or false in a filter condition such as this:

Code Select Expand


[species field] in ( 'Fish', 'Alligator' )


If it is prompt driven so they can select which critters they want to report on, you can give them a multi-select prompt control and then put a filter in your query such as this:

Code Select Expand


[species field] in ( ?promptSpecies? )


If for some reason you really do need a case statement then all the possible 'then' and 'else' results must resolve to the same data type. You are showing a 'then' clause as the species field which is a string and an 'else' clause that is 0 which is a number.

Code Select Expand


case
  when [species field] in ( 'Fish', 'Alligator' )
  then [species field]
else 'Some other text goes here, not a number zero'
end


[ry1633]

Should have been more clear, my apologies.   This query would reside in a value prompt box - so instead of being able to select all species from the prompt box.  I want the prompt box to only display aquamarine species from the list like "fish" and "alligator" etc.

Currently I have the species in a parameter that just returns all species:

[...species] in ?Parameter2?

[Lynn]

Should have been more clear, my apologies.   This query would reside in a value prompt box - so instead of being able to select all species from the prompt box.  I want the prompt box to only display aquamarine species from the list like "fish" and "alligator" etc.

Currently I have the species in a parameter that just returns all species:

[...species] in ?Parameter2?

Sorry I still don't understand fully. If you have a prompt control that is fed by a query to provide a list of choices for the user to select then just filter that query to only present the appropriate species to choose from, no different than what I described in my earlier post. Does that solve the problem?

[ry1633]

We seem to be missing each other.  I'm not sure if I understand.    Should I be editing the query inside of the value prompt box itself?   You may need to spell it out for me - I apologize for not tracking with you.

[MFGF]

We seem to be missing each other.  I'm not sure if I understand.    Should I be editing the query inside of the value prompt box itself?   You may need to spell it out for me - I apologize for not tracking with you.

Hi,

If you are using a value prompt, the values this displays are usually provided by a query. You can edit this query and add a filter to it so that it only retrieves the species you want to see displayed in the prompt.

MF.

[ry1633]

If I click on the Value Prompt box, here are its properties.   Is it the Query3 that I want to edit?   And if so, how do add a filter to it?  I don't see anywhere to do it in.   Right now it just points to the entire SPECIES in my model.  In this case it's a concatenation --> [datasource...].[qwerysubject...].[SPECIES_ID_CODE+NAME]

And I've already tried this and it doesn't work

[datasource...].[qwerysubject...].[SPECIES_ID_CODE+NAME] contains "Fish"


also tried this but it only returns a "0" in the prompt box:

[datasource...].[qwerysubject...].[SPECIES_ID_CODE+NAME] in ( 'Fish', 'Alligator' )

[Lynn]

If I click on the Value Prompt box, here are its properties.   Is it the Query3 that I want to edit?   And if so, how do add a filter to it?  I don't see anywhere to do it in.   Right now it just points to the entire SPECIES in my model.  In this case it's a concatenation --> [datasource...].[qwerysubject...].[SPECIES_ID_CODE+NAME]

And I've already tried this and it doesn't work

[datasource...].[qwerysubject...].[SPECIES_ID_CODE+NAME] contains "Fish"


also tried this but it only returns a "0" in the prompt box:

[datasource...].[qwerysubject...].[SPECIES_ID_CODE+NAME] in ( 'Fish', 'Alligator' )

It sounds like you've taken on a task without enough training. It is difficult to answer when there are really basic elements you don't seem to have encountered yet. It is as if you've asked how to make a cake but are not yet aware there is a device called an oven and that it is located in a place called the kitchen.

You should encourage your employer to provide professional report studio training. The user guide is also a good resource. A link for all the 10.2.2 documentation is below. You can google to find whatever version is applicable for you.

Your particular question is addressed in chapter 12, but there are concepts in Chapter 10 that would be important for you to also understand with regard to filtering. Before heading to those topics, take a look at Chapter 2 which describes a lot of basic concepts plus a whole section on understanding the interface.

The short answer is that you need to navigate to your query that feeds the prompt (yes, it is Query3) and add a detail filter there to limit the choices that are returned in your value prompt. I strongly encourage you to read through the user guide rather than just hacking around with simplistic answers from people on the forum who cannot see everything you see.

http://www-01.ibm.com/support/docview.wss?uid=swg27042003

[MFGF]

If I click on the Value Prompt box, here are its properties.   Is it the Query3 that I want to edit?   And if so, how do add a filter to it?  I don't see anywhere to do it in.   Right now it just points to the entire SPECIES in my model.  In this case it's a concatenation --> [datasource...].[qwerysubject...].[SPECIES_ID_CODE+NAME]

And I've already tried this and it doesn't work

[datasource...].[qwerysubject...].[SPECIES_ID_CODE+NAME] contains "Fish"


also tried this but it only returns a "0" in the prompt box:

[datasource...].[qwerysubject...].[SPECIES_ID_CODE+NAME] in ( 'Fish', 'Alligator' )

Ok - so the properties of the prompt are telling you that Query 3 is the query which is retrieving values for the prompt to display.

You need to go to Query 3 (from the Query Explorer) and drag your calculation (the item containing the concatenation) from the Data Items area into the Detail filters area, then add contains 'Fish' to the expression for the filter

The resultant expression should look like

[Your calculated item] contains 'Fish'

Notice 'Fish' is in single quotation marks, not double quotation marks

MF.

[ry1633]

I've already taken 2 professional-level Cognos classes (one for Framework Manager, and one for Report Studio), have been through user docs ad nauseum, and have been doing this for a full calendar year.  So yes I have had *some* professional training.  Apparently I'm not as good as some of you on this forum, and I apologize for my lack of skills.     

Thank you for making me aware of my shortcomings.    (I've been in IT for 20+ years and am not a young man any more, my memory and ability to retain knowledge has lost a step or three, unfortunately)  It may well be that I've hit on this topic before in my studies,  I just don't remember it.  But hey, I can barely remember my own name somedays.  :)   Fact of the matter is that I forget more than I remember these days, but oh well, that's the way the ball bounces.

Thanks for the info everyone, I'll figure it out from here.  Just got a new set of testing requirements from my users, sounds like I may be removing this particular field in the future anyway.

[bdbits]

For what it's worth, I am probably older than you are and can totally relate to the joys of growing older.   8)
I would not take anything too personally here. We get a lot of people here with varying levels of ability. The biggest contributors here are sometimes frustrated by people who want everything spoon-fed to them (NOT saying that is you) without so much as looking at the documentation or getting any training. So as is often the case with written communication, it can seem a bit ... tense at times.

I think you are definitely looking for a filter on the query, as has been suggested. Cognos builds some stuff (like queries) behind the scenes when you drop things on a report or prompt page, so I can understand how you may not have been aware of this. I hope you now have enough information to solve your issue.

Take care, hang in there, Cognos is really a good product even if it is sometimes confusing.

[ry1633]

Two questions going forward:

1. First of all, I tried this in the expression of query 3 [SPECIES_NAME] contains 'Fish'  - this works just fine in that it doesn't throw any errors.  BUT.... when I go back to the report, in the Value Prompt for SPECIES it only has a '0' and '1' for the options and obviously returns no data in the report when I select either of those.   There is no 'Fish' in the Value Prompt box at all.

2.  How can I string more than one value into the expression so I can get more than one species into the Value Prompt?   like 'Fish' and 'Alligator'?   I have not seen that in the docs.   And I've tried it both a number of ways such as below.  None have worked see below;  they throw QE-DEF-0261 QFWP - Parsing errors

[SPECIES_NAME] contains 'Fish' or 'Alligator'

[SPECIES_NAME] contains 'Fish' and 'Alligator'

[SPECIES_NAME] contains ('Fish', 'Alligator')

[Michael75]

@ Ryan

I don't claim to have followed this whole thread. I'm just replying to your latest post.

If you want to select only records which contain BOTH Fish AND Alligator (a situation which I hope you never encounter in your production data :) ), you want:

Code Select Expand

([SPECIES_NAME] contains 'Fish') and ([SPECIES_NAME] contains 'Alligator')

I've included the brackets for legibility. They're probably not required by Cognos. If, on the other hand, you're filtering for records which contain one or the other, i.e. you want a result set which contains fish records + alligator records, you want:

Code Select Expand

([SPECIES_NAME] contains 'Fish') OR ([SPECIES_NAME] contains 'Alligator')

FWIW, in the oldie stakes, I bet I can trump both you and bdbits  ;D

HTH
Michael

[ry1633]

wow this is odd.... and somewhat embarrassing. :)   I think I have figured it out just by tinkering.   I single-clicked the Value Prompt box itself.  Went up to the Filters Icon --> Edit Filters and wrote a new expression as follows:

[SPECIES_NAME] contains 'Fish'
OR
[SPECIES_NAME] contains 'Alligator'

Then clicked OK, and set the filter to Optional.   Now it works like a charm.  The Prompt Box has Fish and Alligator in it, and it you can select either or both, and the report runs like a charm.  Sorry to seemingly give everyone the run-around, and then proceed to tinker around and get it right myself.  wow.   :-[  ::)  :)

[ry1633]

sorry Michael, didn't see your new post until right now.  Thanks for help though. :)

[Michael75]

sorry Michael, didn't see your new post until right now.  Thanks for help though. :)

Ha! It looks as if we were both typing independently, at the same time, and (probably) from opposite sides of the Atlantic there. I guess thought transfer is something which works more effectively for the silver surfer generation . . .

[bdbits]

Glad you got it figured out. Believe me, we've all been there at one time or another, or another, or another, or ...  8)

Michael75 - If 75 is the year you graduated high school or college, then you would have me beat. If it is the year you were born, sorry but you are a young fellow to me.  :D

[Lynn]

ry1633, I'm sorry I reached the wrong conclusion and therefore didn't provide the right advice. I'm very glad you got there in the end.

You are certainly not alone in the age department and there are many of us who can relate. I've been working in this field for 30+ years and I'm still learning new things all the time. Or maybe I'm just relearning stuff I forgot   ;D

[MFGF]

Two questions going forward:

1. First of all, I tried this in the expression of query 3 [SPECIES_NAME] contains 'Fish'  - this works just fine in that it doesn't throw any errors.  BUT.... when I go back to the report, in the Value Prompt for SPECIES it only has a '0' and '1' for the options and obviously returns no data in the report when I select either of those.   There is no 'Fish' in the Value Prompt box at all.

2.  How can I string more than one value into the expression so I can get more than one species into the Value Prompt?   like 'Fish' and 'Alligator'?   I have not seen that in the docs.   And I've tried it both a number of ways such as below.  None have worked see below;  they throw QE-DEF-0261 QFWP - Parsing errors

[SPECIES_NAME] contains 'Fish' or 'Alligator'

[SPECIES_NAME] contains 'Fish' and 'Alligator'

[SPECIES_NAME] contains ('Fish', 'Alligator')

Hi,

The issue here is that you tried coding this in the expression of the Species item in Query 3. That was the wrong place. The expression of the Species item should simply be the item itself - read from the data source. By changing the expression of the item to [SPECIES_NAME] contains 'Fish', the item then returns a Boolean result - it's either true or false. These two values are represented in your prompt as 1 and 0, since there are rows in your table for which the expression is true and there are rows for which the expression is false.

The expression belongs in a detail filter for Query 3, not in the item itself. In my post above, I suggested you go to Query 3 and drag the species item into the detail filter section, then add contains 'fish' to the expression for the filter. This should (and would) have given you the desired result for displaying Fish species in your prompt. Instead, it looks like you edited the expression of the data item itself?

The next logical step on from this is to extend the expression to accommodate multiple species. You would do this by adopting the following approach:

[SPECIES_NAME] contains 'Fish' or [SPECIES_NAME] contains 'Alligator'

It looks like you stumbled on this yourself, albeit from a different place. By clicking on the prompt, you put the prompt (and therefore its query) in focus. Pressing the Filter button then allowed you to create/edit a filter for this query (Query 3). If you now navigate to Query 3, you will see your filter in the Detail Filters area of Query 3 - exactly as we described above. :)

Hopefully this explains why you saw what you saw, and will help you where you need to do similar things in future.

Cheers!

MF.

[ry1633]

Thanks everyone for your help - I do really appreciate it.  Cognos is still a relatively new thing for me in that I don't have deep enough skills to call on yet with it.  I came to IT from the side door - I have a music composition and theory background - so I my thought processes might be a tad different when compared to other developer folks.  (as an aside, most of my academic music friends when I was in school all ended up in IT/developer gigs because we figured we still had to eat!) :)     

Thanks for steering me in the right direction, sometimes my brain get so far down one road that I have hard time seeing other paths.  ::)

[bdbits]

That's interesting... true story told to me by one of my college professors. He used to work for Rockwell on mainframes back in the day. He said that in the 1960s, IBM had to hire a bunch of people for their new mainframe/OS (360 series I think). Anyway, there were few if any computer degrees back then, and they mostly hired people with one of two degrees: mathematics, and music. Since that time I have read quite a few articles talking about the link between certain cognitive skills and music. Apparently this works out to a high percentage of IT people who have musical inclinations. It is so common that the areas that attract IT talent (e.g. Silicon Valley) have a phenomenon known as "geek bands", mainly technical people who have bands that gig on the weekends.  8)

I have a musical background myself. My father was a traveling professional musician the first 10-15 years of adulthood. I started college intending to become a studio musician myself, but through a long and winding road ended up in IT. But I do play with a local band. Go figure.