Sorting in Chart
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=1902
Printed Date: 28 Apr 2024 at 10:08pm
Topic: Sorting in Chart
Posted By: AnthonyJ
Subject: Sorting in Chart
Date Posted: 17 Dec 2007 at 11:53am
|
Hi,
I have a chart that has Build ID and Area. I also have a Date that is not being used in the chart. The chart displays the Build ID in the X-axis and Area as the metric. I would like to sort the Build ID based on the Date. I have tried specifying the Chart Sort Order for Build ID as "In Original Order" and included the Date in the Record Sort Expert as ascending. But still, this didn't give me the result I am expecting.
Here is a sample data for reference
Build ID Area Date
--------------------------
A 0.16 11/01/07
B 2.30 12/05/07
C 4.6 11/29/07
D 2.9 10/12/07
E 3.4 09/08/07
Expected result is below. The Build ID is sorted in the order of Date in ascending. Note that Date is not part of the chart.
Build ID Area
--------------------------
E 3.4
D 2.9
A 0.16
C 4.6
B 2.3
------------- XIR2 on Unix Solaris CRXIR2, Oracle 9i/10g |
Replies:
Posted By: Lugh
Date Posted: 18 Dec 2007 at 4:53am
OK, first of all, you are likely to get frustrated. Any time I have to do custom sorting in a chart, I end up really frustrated at least once.    You are going to have to put the date into the chart somehow, somewhere. Otherwise, you just can't sort on it. However, it may be possible to hide it. Here's the "easy" solution. Create your bar chart on both the Date and Build ID fields, with Date first. This will do your sorting properly. Then, go into Chart Options > Legend. At the bottom, you should see a tick box for "Swap Series/Groups." Go ahead and select this. You probably want to turn off the legend at this point, as long as you're in here. This will now show your data by ID in order of Date. (Make sure you're using a stacked bar chart, or you will get a lot of blank space.) This method tends to fail, however, if you have more than one ID for a given date (well, not fail really, but you'll get multiple bars that are the same color), or a single ID appearing on multiple dates (in which case you will see stacked bars, with the area for each date). Depending on your data, this may or may not be an issue for you. Here's another possible solution. It requires some manual intervention on your part, generally. However, I use it frequently for simulating groups where none actually exist. Create a formula, that would look like: {MyReport.BuildDate} & " " & {MyReport.BuildID} Now you can simply create your chart on this. Then, you can go in an manually edit each axis label to delete the date portion. (I have seen a tip in Brian's book that you could use HTML tags to modify the string, and set the color of the Date portion to be color white, and thus be invisible. I have never tried this myself, however.) This will do what you want perfectly, but it is a manual, tedious process each time you run the report. |
Posted By: Lugh
Date Posted: 18 Dec 2007 at 5:05am
|
As a note, I just tried the HTML trick. It will work in the main body of the report. But, I can't find off-hand how to get the chart to interpret the HTML tags. |
Posted By: AnthonyJ
Date Posted: 18 Dec 2007 at 9:10pm
|
Thanks for your suggestions Lugh. I kinda did it the same way as you suggested but tweaked it a bit. I changed the order of the Build ID in the chart as "Original" and created a formula that concatenated the Date and Build ID fields. I then used this formula in the Record Sort Expert and set it to ascending order. Select the "Draw Groups in Reverse Order" checkbox from the Scales tab of the Group X Axis settings. This worked well !
------------- XIR2 on Unix Solaris CRXIR2, Oracle 9i/10g |