Edit Parameter Field Crystal reports with NOT value?

0
votes

In Crystal Reports, I want to add a WHERE field <> date to filter out dates that have a NULL value from my database in my report.

I'm using a legacy FoxPro database on the backend which generates an SQL statement from my report, but doesn't appear to have anyway of adding a WHERE clause to the generated statement.

When accessing the FoxPro backend directly, dates with psudo-NULL values have a date of 1899-12-30, but when they are pulled from FoxPro through Crystal they appear as 12/30/99 (which is maybe the same date just displayed in MM/DD/YY format).

I noticed that the report had an existing Parameter Field that prompts the user to filter out the original query down to a specific date range. I tried to add my own in addition to the Parameter Field, but discovered that what I needed with my WHERE field <> date is not an available option since there are only 3 types of Field Parameters mainly:

Discrete

  • Accept single and discrete values.

Ranged

  • Accept a lower and upper value in order to select everything in this range.

Discrete and Ranged

  • A combination of the two above

None of these appear able to filter the results of the query using a WHERE NOT type of clause, is there some other way to do this?

2
sqlcrystal-reportscrystal-reports-10
Is the data going to be displayed within a table? If so, I believe that you can do a filter on which rows are displayed that would match this. – badpanda
Yes it's going to be displayed in a table in the detail, is that what you mean? – leeand00
If all you want to do is exclude NULL dates, you'll want to use the select expert rather than a parameter. A parameter is used to give the end-user options to choose from, and the select expert is a filter on the source data. It has a lot of options, including writing a custom formula to filter the data. – EvilBob22
What @EvilBob22 said. You'll need to add something like {table.field} <> date(1899,12,30) to your report's selection formula. – Ryan
Yes. If my memory of Crystal serves me, you should be able to open the table's properties by right clicking on the table or grouping and adding a visibility setting or filter on the actual detail. Sorry about the lack of specifics, I don't have a copy of Crystal in front of me to give you more details. However, what EvilBob said should work too. – badpanda

2 Answers

1
votes

Add this to your record-selection formula:

// remove actual nulls
AND Not(Isnull({table.date_field})) 
// remove old dates 
AND {table.field} <> date(1899,12,30) 
// remove dates not in select parameter value
AND {table.field} IN {@date_parameter}
0
votes

All I really needed to do was add some criteria to the WHERE clause of the SQL statement, simple enough in an SQL client, but when you're doing this in Crystal Reports v10 it's a bit difficult to find, unless you know what you are looking for...

So what I needed to do was:

  1. Select the field to filter by in the report (in the Details section)
  2. Click the Select Expert The select Expert Button in the Experts toolbar button on the Experts toolbar.
  3. In the Select Expert dialog the name of your field should appear in a tab.
  4. Below you can select the WHERE criteria used to filter the records.