CODEWITHSUNDEEP
sqlcrystal-reportsfiltering
yeahumok
yeahumok

Reputation: 2952

Crystal Reports -- filtering data from a database

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.

Upvotes: 1

Views: 10269

Answers (3)

MartW
MartW

Reputation: 12538

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.

Upvotes: 1

OMG Ponies
OMG Ponies

Reputation: 332571

Put conditional logic in the Suppression portion of the Details section:

  1. Open the Section Expert
  2. Select the Details section in the lefthand pane
  3. Click the weird looking button to the right of the Suppress (No Drill-Down) option in the righthand pane (under the Common tab). Usually, the text on the button changes colour to indicate if there is conditional logic in place - red means there is; blue means there isn't. alt text
  4. Provide a boolean statement to evaluate as true if the department doesn't match your expected department

This will stop output in the Details section of the report. If you've subdivided the details section, you'd have to set this logic for every subsection.

A common misconception I've noticed supporting this stuff in Crystal Reports has been the belief that Suppression/etc need fully flushed out boolean statements with IF ... THEN - it's not necessary, all you need is the actual statement to be evaluated.

This can be done on pretty much any attribute you can place on a Crystal Report.

Upvotes: 3

Carlton Jenke
Carlton Jenke

Reputation: 3025

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

Upvotes: 0

Related Questions