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

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

 

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

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...