CR11 Report - Finding multiple entries

[gk17]

Hi,

Just joined and hoping someone can help me with a report.

I need to create a report that will look for entries with multiple case #'s (unique) for the same day for the same company. Please see link to Excel file for sample data.

I don't know the best way to do this but was thinking of creating a formula returning true false and then using group for true:
- check to see if there are multiple cases #'s for the same company on the same day
- the formula will return true or the number 1 if a company has multiple cases the same day
- I'm thinking of using the formula for all true values in a group so it will hide all those that return false or zero

The problem is I can't think of a way to do this (formula wise) as I'm still new to Crystal Reports.

In the end, I only want data (same columns above) returned for those days that are true. In this case, it should include all the above sample data except for company A3 and A4 since they each only have one case # on the same day. You may ignore the code column.

I'm doing this in Crystal Reports 11.

Thanks in advance.

 

[Charliy]

Here is one way to do this.
Group by company, split the group footer into two sections.
Suppress Group footer B, the Group Header, and detail.

Create this formula:
//@init
shared stringvar gotdup := 'N';
shared stringvar cases := ' '

place that formula in the Report Header, and Group Footer B

Create this formula:
//@dupchk
shared stringvar gotdup;
shared stringvar cases;
if {table.casenumber} in cases
then gotdup := 'Y'
else (gotdup := gotdup; cases := cases+{table.casenumber}+' '

place that formula in the detail band

right click on the left side of group footer A, and select Section Expert
Near the top of the right side you'll see "Suppress (No Drill Down", click the blue box with the X that's to the right of that.

In the box that opens type:
shared numbervar gotdup;
gotdup = 'N'


What that does is build a string of all the case numbers for a company that will look like:
1234 5678 2345 . . .

Then it looks to see if a case is already part of the string before adding it.

 

[gk17]

Thanks for the reply.

For the Suppress formula (last step), I get a message saying that gotdup needs to be a number.

I actually want it output in the same format outlined in the Excel sample file. So for those companies that do have multiple cases on the same day, I want those columns to be displayed for those only.

 

[pmax9999]

What about if you created a formula that concatenated Company, Date and Case#, such as:

[Code {@GroupByFormula}]
{Table.Company} + ToText(Date({Table.Date}), "yyyyMMdd") + ToText({Table.CaseID},"#')
[/Code]
Group on this formula, and then do a Group Select of:

[Code GroupSelectionFormula]
Count({Table.CaseID},{@GroupByFormula}) > 1
[/Code]

This should return only those records where the a Company/Date/Case# combination appears more than once.

Hope this helps

Cheers
Pete

 

[gk17]

Thanks Pete. That pointed me towards the right direction

I have one more question. Is it possible to have two parameters for a single field using Select Expert? I want to do something like the following:

(({?ChargeCodes1} = {vusrExamChargeCodes.ChargeCodeCode}) or ({?ChargeCodes1} = '') and
({?ChargeCodes2} = {vusrExamChargeCodes.ChargeCodeCode}) or ({?ChargeCodes2} = ''))

It doesn't work which was what I expected. Is there a workaround for this? Basically I want to check that if BOTH values for the ChargeCodes1 and ChargeCodes2 are found, then display the results.

 

[pmax9999]

The simple answer is no. A single record can never have {vusrExamChargeCodes.ChargeCodeCode} equal to both parameters at the same time which is what you are testing for by including it in the Record Selection.

I would tackle it this way:

1.[tab] Create the following formula:

[Code {@Test}]
If {vusrExamChargeCodes.ChargeCodeCode} = {?ChargeCodes1}
Then 1
else
If {vusrExamChargeCodes.ChargeCodeCode} = {?ChargeCodes2}
Then 3
Else 2
[/Code]

2.[Tab] Assuming you are still grouping by the {@GroupByFormula} from the earlier post, create the following Group Selection formula:

[Code Group_Selection_Formula]
Minimum({@Test}, {@GroupByFormula}) = 1 and
Maximum({@Test}, {@GroupByFormula}) = 3
[/Code]

This will return the Company/Date/Case# combinations that have both the Charge Codes entered as the 2 parameters.

Hope this helps

Cheers
Pete

 

[gk17]

Hi Pete,

I tried using the formula and group selection (leaving the original Count Group Selection formula in place). It doesn't pull any data back if I leave those two ChargeCode parameters blank. If I do put in my two values, it returns the ones I want and also those codes that don't match. To try correcting the blank parameters, I tried using the following formula instead which didn't work:

if (({?ChargeCode1} = {vusrExamChargeCodes.ChargeCodeCode}) or ({?ChargeCode1} = '')) then 1
else if (({?ChargeCode2} = {vusrExamChargeCodes.ChargeCodeCode}) or ({?ChargeCode2} = '')) then 3
else 2

Thanks.

 

[gk17]

Not sure if I'm breaking anything by doing this but it looks like the Group Formula is checking for the min to the max. I changed the values around a little so it's between 1 and 2 instead of 1 and 3:

if (({?ChargeCode1} = {vusrExamChargeCodes.ChargeCodeCode}) or ({?ChargeCode1} = '')) then 1
else if (({?ChargeCode2} = {vusrExamChargeCodes.ChargeCodeCode}) or ({?ChargeCode2} = '')) then 3
else 2

I also changed the group formula for maximum to 2.

Will test it out more. I'm still stuck with accepting blank parameters for ChargeCode1 and ChargeCode2. Any help with that will be greatly appreciated.

Thanks.

 

[pmax9999]

The first solution I offered returns record where there was more than 1 CaseID for a given "Company/Date/Case#" combination. The second solution returns "Company/Date/Case#" combinations that contain two specific Charge Codes. I didn't realise the two issues were related.

Unless you can explain more clearly what you are trying to achieve I am not sure I can help any further, particularly in relation to the expected results if one or both parameters are left blank..

Cheers
Pete

 

[gk17]

Sorry for any confusion. I'm thinking we shouldn't need that at all. I'm telling the user to just make sure they enter some value or they will get an error.

Thanks for all the help Pete. The report is working great so far

I have another problem and will open up a new thread for that. Hope you have some time to take a look at that one also.