bgbissell
asked on
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
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
nicde
Add the formula @NoBurglary to the details section. You can hide it but it should be there.
ASKER
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.
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}
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}
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
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
ASKER
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
Very thorough and patient. I learned a few things from James' answers.
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
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
ASKER
good idea - thanks!
No problem.
James
James