CR 8.5 Crystal Reports chart (charting, chart expert) legend question.

I am having a difficult time trying to figure out how to do a specific chart in Crystal Reports 8.5. I have the data that I want to use, but can't figure out how to make the pie chart, and especially the legend, look the way I want.

Sample data (returned from Access parameterized query, ordered using a SQL "order by" clause like this: ORDER BY Sold DESC, Fruit):

Fruit         Sold
====     ====
Oranges      5
Plums         5
Kiwi            4
Apples        2
Peaches      2
Pears          2

I want a pie chart. Each slice is a different fruit. (That I can do.) I want the legend to show the data just as the above sample data (except with percentages, rather than Sold.) So the legend *should* look like this:

Oranges    25%
Plums       25%
Kiwi          20%
Apples      10%
Peaches    10%
Pears        10%

The problem is that the legend shows the fruit in alphabetical order, not in order by the highest percentage. How do I get the legend to display the fruit in the proper order, by the highest value first?


You may not need this, but...
I realize that sometimes when I try to give a simple example, the example may be oversimplified to the point where it is hard to get a grasp on the actual problem. So, just in case, here's the actual parameterized query from Access (which contains a subquery.) I'm actually looking between a date range for groupID that has a minimum number of hits:
PARAMETERS [Date1] DateTime, [Date2] DateTime, [MinHits] Long;
SELECT tblSession.dtmSessionDate, tblGroupName.strGroupName, TotalHits
FROM (SELECT tblSession.dtmSessionDate, tblGroupName.strGroupName, Count(tblSessionDetail.pk_lngGroupID) AS TotalHits
FROM tblSession INNER JOIN (tblGroupName INNER JOIN tblSessionDetail ON tblGroupName.pk_lngGroupID = tblSessionDetail.pk_lngGroupID) ON tblSession.pk_lngSessionID = tblSessionDetail.pk_lngSessionID
WHERE (((tblSession.dtmSessionDate) Between [Date1] And [Date2]) AND ((tblGroupName.pk_intGroupNameID)=1))
GROUP BY tblSession.dtmSessionDate, tblGroupName.strGroupName
ORDER BY Count(tblSessionDetail.pk_lngGroupID) DESC)
WHERE (TotalHits >= [MinHits])
ORDER BY TotalHits DESC, strGroupName;
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

I would group your report the same way you are grouping your query, and then add the sorts in Crystal as well.

If that doesn't work, I know we can do this using the Group Sort Expert, but that could cause some reworking of the chart.
dtleahyAuthor Commented:

Thanks for the reply. I have Crystal Reports Developer 8.5, and (as far I I can tell), each group in Crystal can be based only on one field value. If there is a way to set Crystal to group like this

GROUP BY tblSession.dtmSessionDate, tblGroupName.strGroupName

then I don't know how to do it. Are you thinking of creating a second group? The database records are already returning a COUNT of the raw records that fulfilled my SQL WHERE clause, so I don't want to use an additional SUM or COUNT within Crystal as a criterion for grouping. Maybe I need to write the SQL in Crystal {egads! did I really sat that!} and let it do the sorting and grouping...

I also may not realize how to use the Group Sort Expert to achieve my desired result. I'm happy to start over from scratch on this report; this is very typical for me with Crystal. (I save all of the nit-picky formatting until after all the nuts and bolts are worked out.)

A little more input, er handholding, may be required here to step me through the process, if this can be done in v8.5.


You can concatenate the fields together in a formula and then group on the formula.

{tblSession.dtmSessionDate} &  {tblGroupName.strGroupName}

Try putting the above in a formula, then grouping on it and sorting descending.
If that doesn't do it, then we will cross that bridge then.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
dtleahyAuthor Commented:
I think you're getting close, but I also think that the limitations of the Chart Wizard are beginning to become apparent. I created this formula:
{qry_7smActiveSessionGroupsDateRangeMinHits2.TotalHits} & {qry_7smActiveSessionGroupsDateRangeMinHits2.strGroupName}
and the legend looks like this: 3.00Apples  .5%

So, I tried this:
Int({qry_7smActiveSessionGroupsDateRangeMinHits2.TotalHits}) & ",  " & {qry_7smActiveSessionGroupsDateRangeMinHits2.strGroupName}
and got this: 3.00, Apples  .5%

So, I tried this:
Round({qry_7smActiveSessionGroupsDateRangeMinHits2.TotalHits},0) & ",  " & {qry_7smActiveSessionGroupsDateRangeMinHits2.strGroupName}
and got this: 3.00, Apples .5%

So, I tried this:
{qry_7smActiveSessionGroupsDateRangeMinHits2.TotalHits} & " Hits:  " & {qry_7smActiveSessionGroupsDateRangeMinHits2.strGroupName}
and got this: 3.00 Hits: Apples .5%

So, I went to File/Options/Fields/Number/Number and set the default for all report numbers to be integers. Then I saved the report, and closed it. When I reopened it, I got this: 3 Hits: Apples .5%

which is getting very close. I would like to suppress the integer, such as "3", and end up with just "Apples" because the legend already shows the percentage of the total. However, I guess I could live with the preceeding example, except for the fact that the legend simply shows whatever it can fit in a single column, and simply drops any other items that should have been on the legend. Then it "adds" whatever percentages are shown, to make it 100% (even though the percentages shown might actually add up to quite a bit less than 100%; only 20% in my real world case.) Changing the size of the chart on the page, or the position of the legend does not solve this issue.  A chart legend with missing info is an unworkable solution.

I forgot to mention that using labels outside the pie wedges is a total formatting disaster too, with many labels crammed and overwriting each other.

I have to conclude that this chart would look just fine only if I have a relatively small set of data to chart (such as about a maximum of 30 types of fruit.) Any more than that and the chart is an ugly mess, with missing data in the legend. Since my users will not know in advance just how many items will be returned by the query, it is not an acceptable solution to offer them. This data set is well suited for a pie chart, but only if there are not excessive pie wedges. I wasn't sure how a 100 slice pie would look; now I know, and it aint purty. Maybe I should count the rows returned by the query, and suppress the chart if greater than 30.

All of the foregoing detail is for the benefit of anyone else who stumbles into this thread...  bdreed35 was correct about concatenating fields together into a formula, and grouping on that formula. So, I am accepting the answer and awarding the points. Any further notes by bdreed35 or anyone else regarding workarounds to the charting limitations would still be VERY greatly appreciated!

-Dennis Leahy

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.