Querying for partial value?

[ry1633]

Hello,

I have a column that I'd like to query and summarize based on a partial value in that column.    I have a column of Establishments that goes something like "189E - some establishment name and info..." and I'd like to be able query for any and all in that column with that identifier of "189E" etc, and then summarize that total in the footer, just above the count for the entire report in the bottom footer.   Is there a way to do this?

[cognos810]

Hello ry1633,
If I understood your problem correctly, I think you should be able to do this by creating a data item that does the summarization for you. I am assuming that the summarization you want is also count, as is the report's bottom footer.
CASE
WHEN [Establishments] begins with '189E'
THEN 1
ELSE 0
END
You can also do [Establishments] like '189E%' or if 189E can appear anywhere in the value for a row then [Establishments] contains '189E'

Then set the roll-up and regular aggregate property for the data item to Total.

Add a list row footer above your existing bottom footer and add this new data item there.

-Cognos810

[ry1633]

yep, that sounds good I will try it and report back.  many thanks -ryan

[ry1633]

Sorry I need some help clarifying the syntax.  I've put a new query calculation into the Footer and have it doing this far:

count (..tablename...[ESTABLISHMENT])

but I'm not sure quite how to write the rest of what you suggest or if I can do that at least partially with drag-and-drop

[cognos810]

Hello ry1633,
Have you created the data item with the expression as suggested earlier?

Can you please attach your report studio XML here?

-Cognos810

[ry1633]

I'm not sure if I can do that, I have it saved as xml but there's a lot of organizationally-sensitive data in there that I don't want to post.   I can try to edit that out...