Multiple Selection Criteria (Arrays?) in Crystal help

[jcalli]

Hi,

Using Crystal 9 and am trying to build a report whose columns use a different set of selection criteria than the other columns. The search parameters are the same for the report, but again, each summary column needs to select results based upon seperate selection criteria. Should I use some type of array function, or subreports?

Column1 and Column2 work correctly. When I get to Column3, I can't change selection criteria for the column so I can't add more than two columns.

Ex:
Column1 = search parameter = substring, 3 digit zip range
Column2 = count based on selection criteria.
Column3 = count based on selection criteria.
Etc.

Report should look like this:

Report
ZipCode Col2 Col3 Col4 Col5
370 2 2 4 9
371 5 2 6 7
372 2 0 4 13

How do I get results for columns 3,4,5 based upon different selection criteria than Column 2?????


Thanks

 

[Turkbear]

Hi,
Depending on what you are counting, you can create formulas to determine what values to add up for a count..
( assumes your selection criteria returns all rows needed.)
for example:
If (field1 = new condition) then 1 else 0

Sum the formulas for your counts..

I cannot be more specific with the info you provided, but you may get the idea..

 

[jcalli]

Thanks,

The problem now is that I have duplicate records and I want a distinct count. This formula will assign all rows with a 1, including duplicates.

Example:

John Smith Meets Criteria multiple times and I only want to count him once.

How do we apply this formula to a database with multiple values to get a distinct count? (Null?, etc?)

 

[synapsevampire]

You hadn't mentioned anything about names in your previous post, it shows a zip code report. Supplying meaningful example data and the required output tends to get fast, accurate responses here.

I would sugget using Running Totals, and in the evaluate->Use a Formula place the criteria for each.

You can also select distinct count and point at the name field.

Hard to say though, as your requirements changed quite a bit, and introducing a name without ny idea of how the report is to be grouped, etc., makes it hard to advise you.

-k

 

[jcalli]

Ok,
Group by state and zip The column total should be the count of distinct provider that meets this criteria. I am looking for hospitals in column 2. Physicians in column three. Clinicians in column 4 and so forth. The selection criteria is as follows :

Column 2
{provider.active} = "T" and {ptype.code1} = "I" or
{provider.reason} = "P" and {ptype.code1} = "I" or
{provider.reason} = "F" and {ptype.code1} = "I" or

{provider.active} = "T" and {ptype.code2} = "F" or
{provider.reason} = "P" and {ptype.code2} = "F" or
{provider.reason} = "F" and {ptype.code2} = "F" or

{provider.active} = "T" and {ptype.code2} = "U" or
{provider.reason} = "P" and {ptype.code2} = "U" or
{provider.reason} = "F" and {ptype.code2} = "U" or

{provider.active} = "T" and {ptype.code3} = "PH" or
{provider.reason} = "P" and {ptype.code3} = "PH" or
{provider.reason} = "F" and {ptype.code3} = "PH"

column 3
{provider.active} = "T" and {ptype.code4} = "026" or
{provider.reason} = "P" and {ptype.code4} = "026" or
{provider.reason} = "F" and {ptype.code4} = "026"

column 4
{provider.active} = "T" and {ptype.code4} = "065" or
{provider.reason} = "P" and {ptype.code4} = "065" or
{provider.reason} = "F" and {ptype.code4} = "065"

By using the formula field with the the "if then" statement I get the dups for the specific matches. I get the correct information, but I need to count the specific provider only once in the column total

ZIP Correct column2 Formula field column2
356 2 8.00
357 3 9.00

The correct column2 is using the distinct count to get the correct number 2. The formula field finds 8 matches on the criteria, but in fact it is the same 2 providers counted for each match

Correct column 2 works because we have a record select statement, but as I stated in my previous post I can't use it for all the columns.

Any help is greatly appreciated,
Thanks.

 

[lbass]

You should remove the record selection criteria that allows column 2 to work--otherwise the other columns will not work. Then create a running total for each column using the expert, and choose distinctcount of {table.ID} (whatever you are counting), evaluate using a formula->add your formula for a particular column into the formula area->reset on change of group (zip). Place the running totals in the group footer.

I also think you should set off your "or" statements in each column formula with parentheses so that it is clear how you want the clauses clustered.

-LB

 

[synapsevampire]

Right, a Running Total makes more sense here.

Also allow me to simplify your formula a bit:

This:
"
{provider.active} = "T" and {ptype.code1} = "I" or
{provider.reason} = "P" and {ptype.code1} = "I" or
{provider.reason} = "F" and {ptype.code1} = "I" or
"

is the same as:

{provider.active} in ["T","P","F"] and {ptype.code1} = "I"

I can simplify a bit more, but this should increase readability and simplify maintenance enough.

... you get the idea

-k