How to count group 2 based on group 5 information 1

[Sange]

Hi, I am developing a report using CR10 and it is proving to be a lot more difficult than I expected.

The purpose of the report is to count how many offenders have had all their order conditions contracted.

Each offender can be subject to one or more orders. Each order will contain at least one condition. Each order condition may or may not have a referral contract attached and unfortunately not all referral contracts will be relevant to the condition it is attached to – (not quite sure why this is permitted but anyway…)

I am currently grouping the report by offender location, then offender id. The third group is the order id, fourth group is order conditions and fifth group is the referral contract (if there is one).

I have then created a formula {@FlagContract} at the detail level to flag whether a referral contract was present and if there was one, that the referral contract was in fact relevant to the order condition ie. if relevant then 1 else 0. I then inserted a Max of {@FlagContract} at group header 5, and if Max of @FlagContract = 1 then that particular order condition contains a relevant contract.

Basically every group 5 header will have to have a Max of {@FlagContract} = 1 for the offender to be counted and this is where I’m stuck. I’m not sure how to actually count this. I thought a running total would work but I was wrong.

I hope this all makes sense and if so, any suggestions would be appreciated.

Thank you.

 

[Madawc]

I assume your problem is that you can't test a "Max" in a Running Total. That's a limit in Crystal: it has a fixed processing cycles and will not allow summaries of summaries.

You might get the method to work using your own variables, using formula fields placed in the group footers.

Another possibility is a Crosstab, which can do its own counts independent of the report grouping. And you can place them at group level. I've not done this so I can't tell you exactly how this would work or if it would work. But if you don't already know Crosstabs, it is well worth getting familiar with them for future use.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[lbass]

I think you could create a formula something like this:

//{@relevantcontract}:
if not isnull({table.referringcontract}) and
{table.ordercondition} = "ABC" then 1 //whatever the condition is for relevance

Then go to report->selection formula->GROUP and enter:

sum({@relevantcontract},{table.offenderID}) = distinctcount({table.referralcontract},{table.offenderID})

If isn't what you are looking for, please show the actual content of your {@FlagContent} formula and of any formulas nested within it. Also show some sample data with group levels and fields labeled.

-LB

 

[Sange]

Hi Madawc and lbass, yes the issue is testing the max in my running total and that I can't seem to do it.

lbass, your solution was great in that it was able to just return those uncontracted offenders. I was hoping this would be enough however the customer wants to be able to see contracted offenders so they can compare the totals between uncontracted / contracted offenders.

I was hoping to attach a PDF of some sample data but I couldn't work out how to do it so will try to display it here:

Group 1 - CCS Location
Group 2 - Billy Bloggs (Offender Name)
Group 3 - Order No. 12345
Group 4 - Community Work (order condition)
Group 5 - Attend Community Work every Wednesday (program contract)
Group 4 - Alcohol Testing (order condition)
Group 5 - Attend Testing every Friday (relevant program contract)
Group 5 - Supervision (not relevant program contract for this order condition)

In this example offender Billy Bloggs would be counted as contracted because both order conditions contain at least one relevant contract

Group 2 - Sally Fields (Offender Name)
Group 3 - Order No. 12258
Group 4 - Community Work (order condition)
Group 5 - Attend Community Work every Wednesday (program contract)
Group 4 - Psych Assessment and Treatment (order condition)
Group 5 - Attend for drug testing on Thursdays (not relevant program contract for this order condition)

In this example offender Sally Fields would not be considered contracted because the order condition Psych Assessment and Treatment does not contain a relevant contract

There are also instances where an offender could have more than one order id:

Group 2 - John Somebody (Offender Name)
Group 3 - Order No. 789456
Group 4 - Community Work (order condition)
Group 5 - Attend Community Work every Wednesday (relevant program contract)
Group 4 - Psych Assessment and Treatment (order condition)
Group 5 - (NO program contract entered)
Group 3 - Order No. 725899
Group 4 - Community Work (order condition)
Group 5 - Attend Community Work every Wednesday (relevant program contract)
Group 4 - Alcohol Testing (order condition)
Group 5 - Attend Testing every Friday (relevant program contract)

In this example offender John Somebody would not be counted as contracted as Order No. 789456 is missing a relevant contract for order condition, Psych Assessment and Treatment.

As previously mentioned, I created a formula that would determine whether a pogram contract was relevant to the order condition and then insert a max at the order condition level. I was then hoping to somehow count this. The formula condition is an if then else. eg: if isnull(program contract) then 0 else if order condition = A and program contract in [1, 2, 3, 4] then 1 else if order condition = B and program contract in [5, 6, 7, 8] then 1 else 0

Sorry about how long this post is, hopefully it makes a bit more sense.

 

[lbass]

You can still use my solution, just remove it from the group selection area, and create a formula like this to place in the offender ID group header:

if sum({@relevantcontract},{table.offenderID}) = distinctcount({table.referralcontract},{table.offenderID}) then
"Contracted" else
"Uncontracted"

-LB

 

[Sange]

Thanks lbass, this solution would have worked perfectly but then I discovered the following:

My report contains duplicate records at the detail section may which means the sum({@relevantcontract} will also count duplicates. I have done my best to eliminate duplicates but some continue to remain there. To make matters worse, it looks like the same contract type can be ordered to more than one order condition type. Eg: a contract for an offender to commence psych treatment can be added to order condition "Psych Assessment and Treatment" as well as order condition "Other". This is making the distinct count difficult as well. I'm trying to come up with a work around but not sure how I will go. Would you have any suggestions by any chance?

 

[Sange]

Sorry lbass, I forgot to add that I also need to add totals for each location ie. total offenders & total offenders contracted. Obviously the total offenders is easy enough but am also having trouble with calculating the total offenders contracted.

Thanks again.

 

[lbass]

If there is a unique field (ID?) per "referring contract", you could change the formula to:

//{@relevantcontract}:
if not isnull({table.referringcontract}) and
{table.ordercondition} = "ABC" then
{table.ID} else
tonumber({@null})

...where {@null} is a new formula that you open and save without entering anything. If the ID field is a string, remove the tonumber(). You can then change your formula to:

if distinctcount({@relevantcontract},{table.offenderID}) = distinctcount({table.referralcontract},{table.offenderID}) then
"Contracted" else
"Uncontracted"

Before addressing any totals, I think you need to get this working first.

-LB

 

[Sange]

Hi lbass, great news your suggestion was spot on and it looks like it's now flagging contracted / uncontracted records correctly. I have also been able to add the formula into a running total to count which records are contracted and that looks like it's working too so thank you very very much.