We help IT Professionals succeed at work.

Crystal Reports special sort

dwabbott
dwabbott asked
on
I have a problem in sorting in Crystal Reports XI.  I want to sort by a calculation of percentage errors, but not as the last sort item, rather as a sort done between the first and second level groups.
I have the first group, for Institution, and the second, for Error Code, and for each Error Code there is one number for errors made, and another for opportunities for making errors.  Finally there is a calculation of percent, which is errors made / opportunities for errors.
If I use the normal sort for the groups, the report will read
      Institution A     Error code 120     Errors made = 4   Opportunities = 7   Percentage =57%
      Institution A     Error code 125     Errors made = 8   Opportunities = 9   Percentage = 89%  
However, I want to sort in descending order of percentage, so the percentage of 89 would come first and Error Code 125 would be listed before 120.  
I tried making the Error Code group not be sorted, and the Percentage sorted in descending order, the idea being that the sort would be passed to the next level, i.e., the Percentage.  However, the only choice I see for the group sorting, besides ascending, descending and specified order, is original order.  When I select original order for the Error Code group, I find that it doesn’t group at all.  I have the following:
      Institution A     Error code 125     Errors made = 8   Opportunities = 9   Percentage =89%
      Institution A     Error code 120     Errors made = 3   Opportunities = 4   Percentage = 75%  
      Institution A     Error code 120     Errors made = 1   Opportunities = 3   Percentage = 33%  

So the Percentage is correctly sorting in descending order, but the grouping of the Error Code has not taken place and the listing is by individual entries of Error Code.  What is needed is for the Error Code to properly group with all like codes together but otherwise not be sorted, then to sort by percentage.
Can you help?


Comment
Watch Question

Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
How are you calculating the percentages?

The is a GROUP SORT EXPERT you can try to use.

mlmcc

Author

Commented:
by   Error_cnt % opportunity_cnt
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Are those fields, formulas, or variables?

mlmcc

Author

Commented:
Fields.

(As I'm new to this, I didn't realize you had answered with a question and were waiting for me.)
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you upload the report file?

mlmcc

Author

Commented:
No, as I have left that position.  I promised I would send in a solution when I found one.

Author

Commented:
Will you be able to answer me soon on my problem?  
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
I need the report file so I can see how the values are being calculated.

mlmcc

Author

Commented:
I can't give it to you as I have left that position and no longer have access to the report file.  Can we therefore change the request slightly:  Say that instead of the Percentage value I originally had now I have a simple count of errors in each detail entry.  If you do a normal sorting of the data at the group level, and include the number of errors that each separate second-level group totals, the result will be a reported sorted (say) in ascending alphabetical order at each group level.  At the second group level, there will be a total number of errors listed for each group, which will vary randomly.  I want the second level groups to be sorted not alphabetically but in order by the number of errors. If you can provide an answer to this problem instead of the original, I believe it will be what I need.
Thank you for your help.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Look at the Group SOrt Expert under the REPORT menu

You can sort a group based on a summary field in the group

mlmcc
As mlmcc said, there are some group sort options that may give you what you need.  It depends on the exact nature of the summary that you're trying to sort by.  I'm really not sure if you'll be able to use that to do the sort that you want (partly because I don't normally use the group sort options, and partly because I'm not that clear on the calculation/summary that you're doing).

 If that won't work, the best, if not only, option might be to have the report use a manual query (eg. a stored procedure or view in the db, or a CR Command) that calculates that value, so that the report doesn't have to calculate it and can just sort by it.

 James

Author

Commented:
Sorry for the inactivity - I got sidetracked by another project.  I will get back to this as soon as I can.  Thanks for your patience!

Author

Commented:
I apologize for the long delay.  I can better explain my problem now after some study.  
I can almost resolve the sorting problem by using the Group Sort Expert, with selection of All. It works fine for a summary item, but not a calculation. If I have a summary item that I want Group level 2 to sort on, descending, I first insert the field into the group heading or footing area for Group 2, then go to Group Sort Expert, select All, then from the drop-down list I select the desired field, which will be Sum of something, and select Descending.  This works beautifully, but it will not work on a calculation as the calculation does not appear in the drop-down list for 'based on'.   Apparently this will only work if I can find a way to do the calculation before the sorting.  -  Rereading James's comment, I see that he has covered the same area I just did, that the report should not have to calculate the value.  So this is the problem.  Can you suggest ways of doing the calculation first?
What is the calculation (formula)?  It _might_ be possible to change it so that you can sort on it.

 Otherwise, you may have to use a manual query, like a stored procedure or view in the db, or a CR Command.  The query would calculate the value and include it with the other data that's passed to the report, so that CR can just sort by the field that contains that value and doesn't have to calculate it.

 James

Author

Commented:
It is just a division for percentage of two counts:  Error_cnt % opportunity_cnt.  
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What are the counts?

mlmcc

Author

Commented:
Just a numerical field with the number of errors, and the number of opportunities for errors.  This on each entry, which should then be summed at the group 2 level, and the percentage of total errors for the group to total opportunities for errors for the group then calculated.  So an individual entry would be entity 1 no. of errors =4, opportunities for errors = 9   -    entity 2 no. of errors 3, opportunities for errors 6.  Grouping these two entries would give no. of errors 7, opportunities for errors 15, percentage of errors at the group level 47%.   I then want all the total lines for group 2 sorted in descending order of percentage errors.
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
Can you upload the report so we can see how the formulas are being used?

I don't think we need the data just the structure.

mlmcc

Author

Commented:
I can't give it to you as I have left that position and no longer have access to the report file.  

The way the formula is used is that I set up the formula as  <  Error_cnt % opportunity_cnt  >, inserted it into the report page on the Detail line (for the 'Error Code') after the fields for Error_cnt and Opportunity_cnt, then with a grouping on the next higher level (for the 'Institution') I list the sum of the Error_cnt, the sum of the Opportunity_cnt, then the percentage of the sum of errors vs the sum of opportunities.  (Note that I would not display the detail line in the actual report.)

 I am not sure now if I did a correct representation of the percentage, seeing that this is one sum being a percentage of another.  That can be part of my question:  how this calculation should be done at the group level.

Is the above of any help?  

Author

Commented:
I still have need of an answer.  Can someone give me further help on this one?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
If both Error_cnt and opportunity_cnt are fields in the databse this will work.  If they are formulas then it depends on the formula.  

If formulas and they use summaries to get the counts, you can't use a summary on a summary so it won't work.

mlmcc

Author

Commented:
In fact, both Error_cnt and Opportunity_cnt are fields in the data base.  The only formula is dividing one into the other on the detail line (not displayed) and dividing the sum of one into the sum of the other at the group level, then doing a sort on the result.  Can this work?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
The problem is ou are using a formula that uses 2 sums an dis therefore not considered as a summary so it isn't available for sorting the groups.

mlmcc

Author

Commented:
I see. If there were subfiles available as were used by Cognos PowerHouse, you could do the grouping normally, send the result to a subfile, then sort the subfile.  I would need a way to do this first step further back, but I don't know of any.  What we have here is a stored procedure derived from a data base.  The stored procedure is of course used in many other combinations in other reports.  Perhaps I could suggest that the people who did the stored procedure come up with another one that would group at this level strictly for this report,  otherwise abandon this requirement.
Thanks - let me know if you can think of any other solutions.

Author

Commented:
Further thoughts:  in my last comments I recalled that in Cognos PowerHouse you could resolve this easily by first sorting normally and sending the results to a subfile, then sorting the subfile in the desired way.  Could not the same effect be achieved by the 'While writing records' step?  I'm not well versed in this area, but it seems as though it ought to work like a second pass at the data.  Before this last step, the data would be sorted simply in alphabetical order on both first and second levels, but if you get another pass at the data, could you not say somehow or other 'While printing records, sort at the second group level by the Percentage Errors'?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
No.  Sorting is done is an early pass through the records but your summary values won't be available until the last pass.

mlmcc

Author

Commented:
OK - too bad.  It looks like the only hope is to have someone do the summing in a stored procedure.  Unless there is a solution in what james0628 suggested:  have the report use a manual query (eg. a stored procedure or view in the db, or a CR Command) that calculates that value, so that the report doesn't have to calculate it and can just sort by it.  Can you tell me more about the CR command?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013
Commented:
A CR command is much like a view or stored procedure in the database.  You write it in much the same way.

mlmcc

Author

Commented:
Where would the CR command have to be?  Can this be done without going into the existing stored procedure or creating a new one?
Mike McCrackenSenior Consultant
Most Valuable Expert 2011
Top Expert 2013

Commented:
What is the current data source?
Did you select tables?, use a database view or stored procedure?

mlmcc

Author

Commented:
Currently it is a stored procedure that was made available to us.
A CR Command is just a manual query that you create in CR and it's stored in the report.  If you can get a copy of the query that's used in the stored procedure (Can you view the contents of the stored procedure?), you might be able to take that, make some modifications, and use that as your CR Command.

 If you create a new report and select your db, is there an "Add Command" option?  That's where you would enter the query.

 James

Author

Commented:
I apologize for the delayed answer.  This has been a very busy period plus I just started in a new position that is taking a lot of time.
The problem is developing into one with no set solution, and it is complicated by my no longer being at the work place where the problem exists.  
To answer James, I'm sure there is an Add Command option, but I do not have the experience to know what query to enter.  Can you help me with this?
Thanks, and happy new year!
In theory, the query would essentially be a copy of the stored procedure.  At least, that would be your starting point.  If the stored procedure has any parameters, they would be replaced by parameters that you create in the CR Command editor.  Once you (or whoever) got that working, you could see about making changes to the query in the Command, to add the figures that you need.

 Forgive the obvious, but it might be easier if someone still at the company (with access to the report and stored procedure) could join in this discussion.  I'm guessing that there is some reason that they have not already done so, but I just had to mention it.

 James
Commented:
James,
   Of course, you are right that someone from the company should be involved.  I had hoped to find a solution that would be straightforward enough to simply phone it in and be done with it.  I believe what I should do now is to let them know what I have learned from these exchanges and suggest that they follow the route you describe and complete this on their own.  I could ask if they wish to pursue this further with me still involved and with your help.  Give me a couple of days to contact them.
Thanks!

Author

Commented:
This problem was not really resolvable but I appreciate the assistance.