Solved

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

Posted on 2004-10-22
1,139 Views
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;
0
Question by:dtleahy
    4 Comments
     
    LVL 28

    Expert Comment

    by:bdreed35
    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.
    0
     

    Author Comment

    by:dtleahy
    bdreed35,

    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.

    Thanks,

    -Dennis
    0
     
    LVL 28

    Accepted Solution

    by:
    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.
    0
     

    Author Comment

    by:dtleahy
    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


    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone. Privacy Policy Terms of Use

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
    There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
    Want to pick and choose which updates you receive? Feel free to check out this quick video on how to manage your email notifications.
    Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

    875 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

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now