Crystal Reports -- filtering data from a database

Question

I have a crystal report that is connecting to a database. The datatable from which this information is coming from is rather large and I need to know how to filter information.

I have a field in my report that needs a list of requirements. The datatable i'm pulling from has all these extraneous requirements for other departments that I do not need.
For example:

I only need to see NY IT Office service information. The column in my datatable shows everything such as kitchen services, laundry, parking, IT dept info, and finally Office Services. Sometimes the entries that I pull from involve all of these departments--however I ONLY care about my dept.
How then can i place a filter on my field to show ONLY what i need to see for my specific dept and to not display the other info? I tried using the Select Editor...but it seems if I was to tell it to not display Kitchen services for example, it would not show the entire entry (even if my dept. was involved).

I don't know if this is clear or not--but some help using this damned Formula editor would be much appreciated.

Answer 1

You could access the data through a stored procedure, building the filter logic into it.

Answer 2

If you have any version of Crystal after 8.5, you can use pretty much any SQL you want as a Command source for a report, removing the necessity of creating any DB objects.

What is stored in this requirement field? It sounds a bit like you have multiple values per row.

EDIT : Ok, as the field is indeed multiple values, there are various things you can try.

1) If 'Office Services' is definitely unique (ie, there's no entry for 'Main Office Services' as well as 'Office Services', for example), then you can use a simple LIKE in your record selection formula :

{tblData.MultipleValueField} LIKE "*Office Services*"

2) If you can't make that assumption, then you can use an array to split the values out and then test for the presence of just 'Office Services' :

"Office Services" In Split({tblData.MultipleValueField}, ",")

Option 1 will probably run quicker, as the filter will be done server-side and fewer records will be sent over the network. Option 2 will involve Crystal doing the filtering and more network traffic. I hope one of these meets your requirements.