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,
FROM (SELECT tblSession.dtmSessionDate,
FROM tblSession INNER JOIN (tblGroupName INNER JOIN tblSessionDetail ON tblGroupName.pk_lngGroupID
WHERE (((tblSession.dtmSessionDa
GROUP BY tblSession.dtmSessionDate,
ORDER BY Count(tblSessionDetail.pk_
WHERE (TotalHits >= [MinHits])
ORDER BY TotalHits DESC, strGroupName;
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,
FROM (SELECT tblSession.dtmSessionDate,
FROM tblSession INNER JOIN (tblGroupName INNER JOIN tblSessionDetail ON tblGroupName.pk_lngGroupID
WHERE (((tblSession.dtmSessionDa
GROUP BY tblSession.dtmSessionDate,
ORDER BY Count(tblSessionDetail.pk_
WHERE (TotalHits >= [MinHits])
ORDER BY TotalHits DESC, strGroupName;
ASKER
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,
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
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,
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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_7smActiveSessionGroup
and the legend looks like this: 3.00Apples .5%
So, I tried this:
Int({qry_7smActiveSessionG
and got this: 3.00, Apples .5%
So, I tried this:
Round({qry_7smActiveSessio
and got this: 3.00, Apples .5%
So, I tried this:
{qry_7smActiveSessionGroup
and got this: 3.00 Hits: Apples .5%
So, I went to File/Options/Fields/Number
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
{qry_7smActiveSessionGroup
and the legend looks like this: 3.00Apples .5%
So, I tried this:
Int({qry_7smActiveSessionG
and got this: 3.00, Apples .5%
So, I tried this:
Round({qry_7smActiveSessio
and got this: 3.00, Apples .5%
So, I tried this:
{qry_7smActiveSessionGroup
and got this: 3.00 Hits: Apples .5%
So, I went to File/Options/Fields/Number
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
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.