Combining Values from IF Statements

[joeg23]

Hi,

I'm using CR XI with an Excel Database. I've tried a few ways to do this, but I was given an example where this worked, so now this is what I'm trying:

I need to consolidate data from 3 columns. The database must stay in Excel, so a Command Object based on Access is out of the question. The following Formula supposedly works in another report:

if {TestDB_.Major 1} = "Improper APR" then 1 +
if {TestDB_.Major 2} = "Improper APR" then 1 +
if {TestDB_.Major 3} = "Improper APR" then 1

Where TestDB is the table and Major 1-3 are the separate columns. I can get an individual count for each column, but I continue to get a Zero result for a Sum field of this formula.

Ideas?

Thanks,
Joe

 

[lbass]

Try something like the following, and be sure to use parens:

(
if isnull({TestDB_.Major 1}) or
{TestDB_.Major 1} <> "Improper APR" then
0 else 1
) +
(
if isnull({TestDB_.Major 2}) or
{TestDB_.Major 2} <> "Improper APR" then
0 else 1
) +
(
if isnull({TestDB_.Major 3}) or
{TestDB_.Major 3} <> "Improper APR" then
0 else 1
)

-LB

 

[joeg23]

Thanks LB! Your code work...if it's not a bother, would you mind explaining why the negative (if its not null and not = the value then 0, else 1) check works but not the positive one (if is = to value) ???

Thanks so much!

Joe

 

[lbass]

If you don't check for nulls, then the formula assumes there aren't any--then when it hits one, the formula doesn't execute. You must always check for nulls first in the formula, which is why I set it up this way. There are probably other ways of writing this, too, as in:

(
if not isnull({TestDB_.Major 2}) and
{TestDB_.Major 2} = "Improper APR" then
1 else 0
) + //etc.

-LB

 

[joeg23]

Not sure if I should start a new post or continue this one...

I now need to get these values to print out in a group. I have a group of error codes, so it shoudl go something like this:

Error code #
a 5
b 9
c 0
d 2

I have an example report where each individual formula is consolidated in a master formula that looks like this:

{@E1.1}+
{@E1.2}+
{@E1.3}..
{@E1.n}

Could someone explain what's happening here (If my explanation is clear) and how it's supposed to work???

Thanks,
Newbie Joe

 

[lbass]

You need to be more specific in your description. I'm guessing that you are still working on a problem that you posted in another thread, where a specific error code could be in any of several columns. If you use formulas as in my earlier suggestion, you could then insert a running total that performed a sum of that formula, evaluated for each record and then reset on change of group (if you want a group subtotal) or reset never for a report total. You would do one running total per type of error code and per type of total (group or report level). How many error codes are you working with?

-LB