• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1732
  • Last Modified:

Group Selection/Record Selection question

I have  a Crystal Report that queries an SQL database. It looks like this:

GH1:  Squad#
GH2:  Officer#
GH3:  Case#,
Details:  Crime Type, Crime Code

And for an officer with two cases it would look like this:
Squad1
  Officer 0288
    Case 12-00012345
        Burglary, 200
        Assault, 300
    Case 12-00012346
        Assault, 300

Notice a case can have more than one Crime Type. The purpose of the report is to tally all the non-Burglary cases. So, in the example above case 12-00012345 with burglary and assault would NOT be counted.
I saw a similar question  (ID27312776) and the answer was to write a formula and use it in the group selection.  This isn't working for me (though I think it should).

This is my modified formula called NoBurglary

If ({Crime Code}) = 200 then
1
Else
0

And in the group selection
Sum ({@NoBurglary}) = 0

It didn't say to put the formula anywhere in the report so it only exist in the Field Explorer.
Results: I can see case numbers in the 'tree', but there is NOTHING in the body of the report. Secondly, it is still returning cases with Burglary.

Though I think I understand the premise of Group Selections, I'm not experienced in using them. So that might be part of the problem.

Any ideas are appreciated!

BG
0
bgbissell
Asked:
bgbissell
  • 5
  • 4
  • 2
1 Solution
 
nicdeCommented:
Add the formula @NoBurglary to the details section. You can hide it but it should be there.
0
 
bgbissellAuthor Commented:
Thanks for your suggestion, nicde. I added it to the Details section. No change. Like before,  I can see the reports in the Tree, but nothing on the actual report page  - which is odd in itself and probably is indicative of something I've done incorrectly. Also it still contains cases with Burglary.
0
 
nicdeCommented:
What is the value of the formula for a Burglary record ?

Also edit the formula and replace {Crime Code} with the field in your table - just double click on the field in order to add it
The field format is usually : {TableName;1.Crime Code}
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
James0628Commented:
You were close.  Your group selection formula is wrong.  Sum ({@NoBurglary}) will give you the total for the whole report.  As long as you have some burglary records in the report, the total will not be 0, so no groups will be selected (ie. every group will be suppressed).

 Your group selection formula should be checking the total for the group in question.  Something like:

Sum ({@NoBurglary}, {case field}) = 0


 FWIW, @NoBurglary does not need to be in the detail section.  Using the formula in the summary should be enough to get CR to evaluate it for each record.

 James
0
 
bgbissellAuthor Commented:
James - Yippee! Progress!
I altered my group selection formula. When I ran it, the report was populated and the Burglary cases did not display. Very good.
However, the cases containing Burglary, although they are not showing, they  are still being counted. Referencing the example I gave initially, currently the distinct count of cases is calculating as 2 for officer 0288 and it should be 1 (just the assault case).  How do create a summary formula to calculate the distinct count of cases that don't contain burglary?

Based on your suggestion of Sum ({@NoBurglary}, {case field})
Here's how I altered the Group Selection formula:
Sum ({@NoBurglary}, {LECase.CaseNumber})

Can you help me understand the 'meaning' of the formula.
I am 'translating' it as "Sum the formula NoBurglary for each case number."  Is that right?
Thanks so much!
BG
0
 
James0628Commented:
You're interpreting the formula correctly.

 The problem is that group selection is really just suppression.  CR is still reading all of the records for every group.  It has to, in order to know whether or not a group should be shown on the report.  It's just not showing some of the groups to you (based on the group selection formula).  If the group tree is visible, it probably still shows all of the groups (case numbers).  They're just not visible on the report itself.

 Since CR is still reading all of those other records, they're included in any CR summaries.

 If you need summaries that only include the selected groups, there are two basic ways to handle that.

 Change the data so that group selection is no longer necessary.  Instead of reading the tables directly in the report, you could create a query (eg. a view in the db, or a CR Command, which is just a query that's created in CR and stored in the report) that filters the data before it gets to the report, so only the desired groups (cases) are included.  Then the report could just use regular summaries.


 Use formulas to accumulate the desired summaries in variables.  For example, to get a count:

 Create a formula like the following (call it whatever you like) and put it in the report header, to create the variable:

WhilePrintingRecords;
Global NumberVar nbcount;
""

 The "" at the end is just so the formula doesn't produce any visible output on the report.  You could also just suppress that field, or the section that it's in (unless you're running a _really_ old version of CR).


 Create a formula like the following (call it whatever you like) and put it in the CaseNumber group header:

WhilePrintingRecords;
Global NumberVar nbcount;

if Sum ({@NoBurglary}, {LECase.CaseNumber}) = 0 then
  nbcount := nbcount + 1;
""


 That just increments the count by 1 for every CaseNumber group with no burglary records.  Put the formula in the group header (or footer), so that the count is only incremented once for each group.


 Create a formula like the following (call it whatever you like) and put it in the report footer to get a grand total:

WhilePrintingRecords;
Global NumberVar nbcount


 You can, of course, change the variable name from nbcount to something else if you like.  Just make sure that it's the same in each formula.  CR doesn't try to do any kind of cross-checking on variable names.  If you use nbcount in one formula and ncount in another, CR just creates two separate variables.


 If you want other summaries (eg. some kind of total) that don't include the burglary cases, you can handle them in a similar fashion.  The details would depend on the kind of summary.

 If you need totals at some other level besides grand total (eg. for each officer), you can handle them in the same fashion.  You'd need a separate variable for each total, and you would need to use a formula to reset each subtotal for each new group (eg. have a formula in the Officer group header that sets the officer total variable to 0).

 James
0
 
bgbissellAuthor Commented:
James, thanks for the great help!  The formulas worked. The report is complete and has been emailed to the requestor :)

I appreciate all the time you took to explain the formulas and take me step-by step -  that helped a lot. I was able to set up the sum by officer based on what you said.

I know about the Command but have never used it. Isn't that straight SQL code?

Thanks again!

BG
0
 
bgbissellAuthor Commented:
Very thorough and patient. I learned a few things from James' answers.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 Yes, a Command is just a query that you create "manually" in CR, as opposed to selecting the tables, fields, etc. and letting CR generate the query for you.  That gives you control over the query.  FWIW, in a situation like this where you have a report that's already reading the tables, I would start by going to Database > "Show SQL Query".  That should show you the query that CR is currently using.  You could copy that and paste it into the Command to get things started.

 James
0
 
bgbissellAuthor Commented:
good idea - thanks!
0
 
James0628Commented:
No problem.

 James
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now