Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win


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

Posted on 2004-10-22
Medium Priority
Last Modified: 2010-05-18
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;
Question by:dtleahy
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 28

Expert Comment

ID: 12382987
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.

Author Comment

ID: 12385723

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.


LVL 28

Accepted Solution

bdreed35 earned 2000 total points
ID: 12386191
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.

Author Comment

ID: 12387340
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


Featured Post

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Video by: ITPro.TV
In this episode Don builds upon the troubleshooting techniques by demonstrating how to properly monitor a vSphere deployment to detect problems before they occur. He begins the show using tools found within the vSphere suite as ends the show demonst…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…
Suggested Courses

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question