Formula to Count Several Fields if They are Null

[tnswalker]

I have never worked in report builder before and I am trying edit a UNIFY Report builder 6.0 qrp file.

I have 10 fields (Ink1 through Ink10). I want to add a calculated field to my report that will count which of those fields are not null or blank.

I just can't seem to get the syntax correct. I keep getting Incomplete or invalid formula.

I've tried =SUM(IIF(Fields!Ink1.Value = nothing, 1, 0)), SUM(NumberIFF(Ink1 IS NULL, 0, 1)) and =Count(iif(Fields!lnk1.Value=nothing,1,0)) and I always get ERR_SYNTAX: Incomplete or invalid formula error.

Thanks,
Stacy

[tnswalker]

No one knows how to count several fields if they are not blank?

[Jeff Luther]

Hmmm, I didn't see your email last month but let me clarify what you wrote:
FIRST, I just reread your initial email and I'm a bit confused. You wrote that you want a formula:

"that will count which of those fields are not null or blank."

Do you mean a SUM of the totals of all of the non-blank fields? (That's what I first thought you meant -- and my FURTHER REPLY... below is based on that interpretation to read that section.)

If instead you mean you want the number of fields Ink1 - Ink10, like 7 fields are null/blank and 3 contain a value, then that' a different issue. If so, then I'd try the RB function: NumberIFF( Number , Number , Number , Number )
Team Dev.'s Help for this RB function states:

Code: Select all

NumberIFF(nInput,number1,number2,number3)
Returns
    one of number1, number2, or number3, depending on the value returned by the nInput parameter.
Parameters
    nInput, number1, number2, number3

If:
    nInput < 0 number1 is returned
    nInput = 0 number2 is returned
    nInput > 0 number3 is returned

In your case then -- and I would suggest you write a small test TD app + qrp to test this -- it's possible that RB counts a null value as equal zero. If so, then I see this syntax for a formula is valid in RB:
NumberIFF( Ink1, 1 , 0, 1) + NumberIFF( Ink2, 1, 0, 1) + ... etc.

That is, NumberlFF() will return a 1 if InkX < 0 or InkX > 0, and returns a 0 if InkX = 0 (or hopefully null/empty as well).

Again, for something like this it's a good idea to write a small test case with TD + RB qrp so you can test this out. And if your test doesn't work correctly, you can always zip the app + qrp and attach it to a forum reply here so others can try it.
(Note, though, that you using v6.0 and this public part of the Gupta Forum is for TD/RB v5.2 and earlier. Not everyone will be able to run your test case if they don't have v6.0 or later installed.)

If this isn't what you want, then read on...

FURTHER REPLY if you really want a SUM/TOTAL of the values of those (non-null) fields:
Thread title: it should really be "Formula to Count Several Fields it They are [NOT] Null", right??

If that's true -- that the title should have NOT in it -- then I can't see why it matters that you need to check whether a field is null or not. I'd think RB would count null/empty as being equal to 0.

As a syntax test at least, I tried this syntax in defining a new RB QRP formula and this is legal syntax in RB:

Code: Select all

Sum( Ink1) + Sum( INk2) + Sum( Ink3) + ... etc

where the 3 QTY_* input items are of type number. RB requires the Sum() to be a number and you aren't allowed to Sum( QTY_ORDERED+SQTY_SHIPPED ) for example, but summing the Sum of each of 2 or more items is legal for a formula.

Suggestion: Be sure to save the formula you define with a good formula name. RB default's to a name of "FormulaXXX". Once you've defined the formula, then you should be able to use that value somewhere else in the report.

[tnswalker]

Thank you for the reply Jeff. Somehow I didn't receive the notification that you had replied and only happened upon it when I was searching to find a resolution to another problem.

Unfortunately the report I am changing belongs to a third party software and I only have access to the QRP so I am unable to do any testing as you mentioned above. I am just trying to add a formula field to the QRP that will sum the ink fields that are not null or blank. My ink fields are of string type. I am having trouble with the syntax to check if the fields are blank and then sum them. Perhaps it isn't even possible to do this on the QRP itself.

Thank you again,
Stacy

[tnswalker]

Thanks Jeff for your reply. For some reason I didn't get an email notification that you had replied to my post. I happened upon it when I was searching for a fix to another problem.

Unfortunately the qrp I am editing is part of a third party software and I only have access to the qrp. I can do no testing as you mentioned above.

Is it possible to add a formula field to the qrp that will count (sum) the ink fields that are not null or empty? Can a formula do both check the value and then count the fields that are not empty? I have 10 ink fields I would need to check and then count if they meet the criteria.

Thanks again,
Stacy

[Jeff Luther]

Is it possible to add a formula field to the qrp that will count (sum) the ink fields that are not null or empty? Can a formula do both check the value and then count the fields that are not empty?

I don't know as I've never written a formula for either of those. (If I had those tasks I'd do the coding on the Team Dev. appl. side -- that's where the data for the ink fields is coming from -- and then transmit the count of empty and/or not null (not empty) fields to the report for that new field of yours.

Since you are trying to do this in RB directly -- and it's language is primitive -- what I'd do is write a small test app + QRP report and look at using the various Str functions in RB to build a formula that will return you what you need.

Gupta has a report.pdf file that lists the Str functions and what they do. I don't see a StrIsNull() function but something like StrIFF() might work, or be part of the solution. Part of what you'll need to code will begin with what the content of each field is if it's 'null' -- that's a number term, so many empty means no string or default value (like zero).