Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Sorting in Chart Post Reply Post New Topic
Author Message
AnthonyJ
Newbie
Newbie
Avatar

Joined: 20 Nov 2007
Location: United States
Online Status: Offline
Posts: 37
Quote AnthonyJ Replybullet Topic: Sorting in Chart
    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
 


Edited by AnthonyJ - 17 Dec 2007 at 11:55am
XIR2 on Unix Solaris
CRXIR2, Oracle 9i/10g
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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.  OuchCryAngry

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.


IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet 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.


IP IP Logged
AnthonyJ
Newbie
Newbie
Avatar

Joined: 20 Nov 2007
Location: United States
Online Status: Offline
Posts: 37
Quote AnthonyJ Replybullet 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 !
 


Edited by AnthonyJ - 18 Dec 2007 at 9:28pm
XIR2 on Unix Solaris
CRXIR2, Oracle 9i/10g
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.