Here is perhaps a better description:

 

I have multiple columns (that I simply represented as "{table1.a}, {table1.b}, and {table1.c}" above). I need the formula to iterate through each one and check if it is null or not. Then I need it to return a string that I designate (represented as "a, b, and c" above) which I can group on. Ideally, I need it to return the string even if it is null but crystal seems to have some quirks when it comes to that.

Once I've grouped on the formula I can run distinct counts (and percentages) on two unique IDs (this in particular scenario I have {client_id} and {visit_id} where clients can have multiple visits and I need to count how many clients that outcome is true for and how many visits that outcome is true for).

In short: I need to create a grouped list of multiple {table.columns} and then run distinct counts of ids on them like you would with one {table.column}.

The original code of
'if {table1.a} <> "" then "atext"
else if {table1.b} <> "" then "btext"
else if {table1.c} <> "" then "ctext"
...'
does basically what I need it to except it stops at the first IF statement that is true (and doesn't list the values that are not true). I think what I need is functionally a 'if - then, if - then, if- then..." list or something wacky.

Please let me know if this doesn't make any sense and specify what you would like to see. I might be going at the report the wrong way...

 

Thanks,

-Matt

I think there is an easier way.

You can use Running Totals to conditionally include or exclude rows.

For your patients make a Running Total

Right click on RT and select NEW

name=PatientCount

Field to sumamrize= patientID

Type=DistinctCount

Evaluate=Use a formula

//Need to tell me the logic for include or exclude here//

Reset=Never

Place in report footer

 

Same thing for visits but change it to the visitID

Question, what kind of formula are you suggesting for the 'Evaluate' section?

 

I was thinking about doing a Reset on group (since I need to display counts for every outcome) but the issue I'm running into is that I don't have a single column to group by. Each outcome is its own column and I'd need to combine multiple columns into one group (which I think was the idea behind the original formula). And by that method I'd need some way to force crystal to go through each IF statement. However, that is probably the wrong way to approach it.

 

Thanks for the replies,

-Matt

If so don't group on it or it will screw up your numbers.

 

Can you give me a real example of soemthing that needs to be counted and some data rows and I can write a formula for a RT to count them.

 

Client_ID, Visit_ID, OC_1, OC_2, OC_3, OC_4

01,              v01,    123,    null,    789,    null

01,              v02,    123,    null,    null,    221

02,              v03,    null,    null,    789,    221

03,              v04,    123,    456,    null,    221

 

*each outcome(OC) has a code associated to it.

 

Results would show as:

Type                  Clients    %   Visits   %

Outcome_1             2      66%     3    75%

Outcome_2             1      33%     1    25%

...

 

This is basically what I'm trying to accomplish, let me know if you need more information.

 

 

Thanks!,

-Matt

in the RT use an evaluate formula of:

NOT (isnull(OC_1) and isnull(OC_2) and isnull(OC_3) and isnull(OC_4))

 

Is that the count you are after here?

Note that RTs have to be placed after the last record evaluated to see teh total (don't use them in headers) so in this case it needs to be in the report footer.

You can place it on a details line and see the actual counting process...

It will stop evaluating on Nulls so that was part of the issue you originally had.

If you use Nulls in the formula you have to use them first. You can also flip the formula option (or report option) from 'using Nulls' to 'Use default values for Nulls' to help avoid that problem. That being said sometimes you still need to know the difference between Null, "" and not "" so it is nice to turn the feature on and off.

In response to your earlier reply: unfortunately no, In your example it counts the total clients if any Outcomes are not null. I need to count the number of clients for each Outcome where that Outcome is not null for a client.

 

For example, if I was to hard code it I would manually write out

"Outcome_1" and place distinctcount of the formula for the client count

 

[Formula code: OC_1_ClientCount]

if not isnull({OC_1})

then {Client_ID}

else mailto:%7b@null - {@null }         *  

 

*  mailto:@null - '@null' is a blank formula placed to remove the +1 count.

 

And then do the same for OC_2, OC_3, etc. down the list.

 

Does that make sense?

 

Thanks for your help with this wacky issue,

-Matt

 

 

and the formula to

NOT isnull(OC_1)

 

then create another for OC_2 etc.