Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 329
  • Last Modified:

Search for the first "good" value

I have a DB field that contains NULL values.

I want to display the first NON-NULL value in my group header.

I've tried to use the 'while' clause and NextIsNull without success.

Is there a way to do it ?

(CR9, over ODBC connection to Sybase)

0
dekt
Asked:
dekt
  • 2
1 Solution
 
AnnetteHarperCommented:
So, you have a report that is grouped and within each group there are records that are NULL for a certain field, but it is not the group by field? And, I assume that you are sorting inside the group so that all of the NULL values come first?

You have to remember that database queries are set-oriented and not sequentially processed (for the most part). If you wanted to display the maximum or minimum non-null value within the group, that would be easy, you would just create a summary field.

Crystal formulas are computed with the current row's data. The exception being running total formulas. If you wanted to display the first non-null value in the group footer, that would be relatively easy, too. You would use a running total field to store the first non-null value and then display it in the footer. But you can't display it in the header.

I don't think exactly what you need can be done. If you were using Oracle 9i, I could give you a query that would return the value you desire using the new analytic functions, but I don't believe Sybase has equivalent functions.
0
 
dektAuthor Commented:
If I understand correctly, there is no way in Crystal to have a code inside a "regular" formula (not running total) to run in scope larger than the current row ?

"running total" is not an option since this field must be in the "header" of the group.
0
 
AnnetteHarperCommented:
That's correct, except for running totals, and aggregation functions like SUM which have an optional scope-type parameter, formulas in Crystal refer to the fields in the current record. Running totals, either built-in or manually created, can imitate row-by-row procedural logic, but their value is determined by their position in the report, and you can't display the desired result before the rows it uses are displayed.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now