| Author |
Message |
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
   Posted: 30 Aug 2011 at 10:31am |
in the line chart you set "the change of" with 2 values (fields)
first you use the date field (set it to monthly )
second use the new text description that I asked you to insert to identify table1 rows vs. table 2 rows.
in the Show values use the struc_id field set as a count
Edited by DBlank - 30 Aug 2011 at 10:32am
|
 IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 31 Aug 2011 at 4:06am |
I really appreciate you trying to help, but I tried using your idea and its not working. The tables have to be linked or else millions of records are being pulled with just a union in the SQL and it slows down to almost crashing the report. Were you able to test this out to see if it showed what was expected provided with the sample data?...the actual report has 15 fields to pull from and several join statments and with the union coming in, further complicates the report fields. It seems like crystal reports charting does not allow the type of charting that I am expecting.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Aug 2011 at 4:18am |
I believe it can be done.
The Union of two tables should never exceed the total rows of the 2 tables combined. Joining on the other hand can have a cartesian product and make millions of rows from much fewer rows in the 2 joined tables.
Make a test spreadsheet of the sample data we discussed like this (just make axtra rows to mmic a wider data set)
Struc ID Date TableDescription
89898998 9/12/2010 Table1
89808993 11/13/2010 Table1
46929020 12/25/2010 Table1
74389398 12/30/2010 Table1
64644646 5/5/2010 Table1
84466464 5/23/2010 Table1
84464644 6/11/2010 Table2
84456544 6/14/2010 Table2
68434574 6/15/2010 Table2
use this test spreadsheet as your source and make the graph from it as I suggested earlier to see if this is what you wanted. If it is then you just need to figure out how to get your real dat in that format.
|
|
IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 31 Aug 2011 at 5:42am |
Well I just used the spreadsheet with the sample data you mentioned as he source and then added it and then plotted the line chart and inserted in the 'change by' the date field and the table description field and in the 'show value(s)' inserted the Struc_ID set to count. The chart lines are displaying over the place, red, green, blue, yellow lines..I dont know whats going on here...next, I wrote two seperate formulas to seperate table1 from table2, named it table1 which has dates for table1 and table2 which has dates for table2, now when i place the two formulas (table1 and table2) in the 'change by' the chart lines go hay wire. I dont know what im doing wrong.
@table1:
if {tabledescription} = "Table1" then Date
@table2:
if {tabledescription} = "Table2" then Date
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 31 Aug 2011 at 10:29am |
in teh sample data add one more fake row (to giove you a value in 2 different months for table2 data)
68434575 5/15/2010 Table2
in the chart
select the on change of and the date field you entered
click on the Order button
cahneg it to 'For each Month'
save the changes
YOu shoeld
|
|
IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 01 Sep 2011 at 2:17am |
I added several extra rows for table2 but its still not working.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Sep 2011 at 3:36am |
1. the chart has to be in a report header or the report footer. If you place i in a group header it limits the data to that group.
2.in the chart expert you have
-the type set as 'Line Chart
-the data set to advances
on change of first using the spreadsheet date field and the order set to group monthly, then under that you have the spreadshee table.description field
-in the show value you have the strucId field set to a disticntcount
IS that all correct, but you are getting lots of lines with different colors?
|
|
IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 01 Sep 2011 at 4:25am |
|
I did all of that, but I get multiple colored lines. In the legend box, I see table2 with 3 different colors, red, yellow and blue, and a green for table1, so there are 4 lines all over the chart. The date range on the x-axis is showing right from Jan to Dec, but the count is way off and I dont know why there are 4 jagged lines.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 01 Sep 2011 at 4:55am |
it is critical that you make the date field the primary 'on change of' and the tabledescription the secondary on change of.
are you sure these are in the correct order in your chart design?
|
|
IP Logged |
|
moontide
Groupie
Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
|
Posted: 01 Sep 2011 at 5:27am |
|
Yes, the 'date' field is above the table descritpion field in the 'change by'.
|
|
IP Logged |
|
|
|
