selecting on a formula

[HowardHammerman]

I created a formula to check for missing data that were critical to another report.
The code was as follows:

numbervar F1 := if isnull({table.field1} then 1 else 0;
numbervar F2 := if isnull({table.field2} then 1 else 0;
local numbervar total := F1 + F2;
Total

When I inserted the field in the report I got the results that I expected.
I then tried to select records based on the formula field. However, the formula field was not in the list of report fields available to select upon. Do the formula need "whileselectingrecords" or some other such prompt?
The report has no sub-reports.
Thanks,

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[pmax9999]

Using the vaiable won't work because it hasn't been evaluated at the time of reading the records.

There is nothing in your formula that really requires the use of a variable - it could be replaced with the following formula to achieve the same thing:

If	Isnull({table.field1}) and
	Isnull({table.field2})
Then 	2
else
If	Isnull({table.field1})
Then 	1
else
If	Isnull({table.field2})
Then 	1
else	0

It is likely this wouldn't be passed to the SQL statement which will make it very inefficient. The better way to do this would be via a SQL Expression, but the syntax would depend on the database you are using.

If it turns out to be too inefficient, let us know and we can probably (depending on db) provide the syntax for the SQL Expression.

Cheers
Pete

 

[HowardHammerman]

The problem is that there are not only two fields that I am testing, there are 21. I just including two in the example for clarity purposes. If SQL is still the answer, please provide the code.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[pmax9999]

You have't told us the db. Assuming SQLServer (and I think the same syntax will work for Oracle, but its a while since I have worked with that), something like this should work (I don't have access to CR or SQL Server for testing so apologies if I have missed something):

Case	When 	table.field1 is null and
		table.field2 is null
	Then	2
	When	table.field1 is null or
		table.field2 is null
	Then	1
	Else	0
End

In the event that simply typing table and field names doesn't work, try adding double quotes around them or better still, picking them from the field listing with the formula explorer window.

Hope this helps
Pete.

 

[HowardHammerman]

Would the following work?

isnull({table.field1) or isnull(table.field2}) or isnull(table.field3}) 
or isnull(table.field4}) or isnull(table.field5}) 
or isnull(table.field6}) or isnull(table.field7}) 
or isnull(table.field8})

Then I would select on the formula field being true.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com

 

[lbass]

Howard,

What are you trying to accomplish? Are you trying to return all rows that have missing data regardless of which field might be null? Or?

-LB

 

[lbass]

On further thought, I think that is what you are trying to do, and your last formula could be entered directly into the selection formula as is (with all 21 possibilities).

-LB

 

[HowardHammerman]

Yep, I am trying to identify all the rows that have missing data in any of the 21 columns. I will enter the formula in the selection formula. Thanks everyone.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support

http://www.hammerman.com