Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: How to Zero out values from a line graph Post Reply Post New Topic
Page  of 3 Next >>
Author Message
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Topic: How to Zero out values from a line graph
    Posted: 29 Aug 2011 at 10:06am

I have been banging my head trying to figure out this impossible problem with the lin echart. I have two types date fields.
 Date1 is straight from the datasource and Date2 is a Crystal formula based field that has two other date fields embedded in it.
Now, Date1 has a range between 01-01-2010 to 12-31-2010 and the formulate Date2 has a range from 01-01-2010 to 06-15-2010.
I am doing a count of a structure field {struc_ID} based on the two above date fields.
The problem is I am trying to do a cumulative count of the {struc_ID}, one that counts based off the Date1 and the other count based off Date2.
Now to combine the two date fields  I wrotea a formula to be placed in th chart expert "change by"

@DateComparison:

if {Date1} in date{2010,01,01} to date{2010,12,31} then {Date1} else
if {Date2} in date{2010,01,01} to date{2010,06,15} then {Date2}

Then I sort it by Date1.

I do a running total for the {struc_ID} that will count for Date1

Then I do a second Running Total for the {struc_ID} that will count for Date2
(in this case I use a formula :
 @Date2Count :
 if {Date2} <= {date1} then {struc_ID} --- or if isnull({Date2}) then {struc_ID} = ""
)

then I apply both the running totals in the "Values" box in the chart expert. I chose a line chart to display the values.

I am assuming the date range is from 01-01-2010 to 12-31-2010 , but it seems like count for Date2 is extening till 12-31-2010,
 which it should not, it should stop at 06-15-2010 because there are no values after this date, so how do I force the count for Date2
 to show no value in the chart after 06-15-2010? I have tried several methods to come up with a solution ove the past few weeeks, but nothing worked!!!
Does anybody have any expert knowledge about how to contain a line when there are mutliple counts in a line chart within different date ranges along the
x-axis?
Please, I really do need some serious help!!...its been weeks I've been working on this!!!

 Here is some sample data


struc_ID         Date1

38944893        1/1/2010
63943849        1/28/2010
34709374        3/8/2010
44973949        3/18/2010
93493439        4/12/2010
45435334        4/24/2010
35354666        5/5/2010
65464456        5/23/2010
46545454        6/11/2010
56765643        7/25/2010
89898998        9/12/2010
89808993        11/13/2010
46929020        12/25/2010
74389398        12/30/2010

--------------------------------------
struc_ID        Date2

53535354     1/13/2010
54754456     1/28/2010
46554446     3/8/2010
46464344     3/18/2010
35435333     4/12/2010
45435334     4/24/2010
64644646     5/5/2010
84466464     5/23/2010
84464644     6/11/2010
84456544     6/14/2010
68434574     6/15/2010

Thanks

 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Aug 2011 at 10:42am
I can't quite follow how you wanted your sample data to end up in the line chart.
Is your line chart supposed to be showing each week. each day, each month?
Is you sample data 2 tables that are unioned together?
are you trying to show 2 overlaid lines on one chart?
IP IP Logged
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Posted: 29 Aug 2011 at 11:03am
Sorry about the sample data. Yes, for the sake of simplicity I used the sample data to test the line chart and Yes, they are two tables (I tried inner join/left outer join, but it didnt work) and I am trying to see each week for both the counts and want to see both lines on top of each other but one of the lines should stop at the '06-15-2010' mark which I cant get it to do..if this can be resolved then I can apply this on my actual report. Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Aug 2011 at 11:23am
i find charts in crystal can get a little squirrelly so I am not 100% sure you will get to exactly what you want.
first i think you need to union your tables together as it does not sound like they should be joined.
In your union process address for table 2 which date to be using (of the 2 from your table).
Also when you union them togther insert an identifier string for each row to let you know which table it came from (use a nice description that expalins what each table represents as this will show in your chart).
You will end up with a final table like

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

Now in your chart set this to
'Line CHart' (with markers if youwant)
Data = On change of as Date field (Set to weekly) and unders that Tabledescription field
Show value as COunt of Struc Id


Edited by DBlank - 29 Aug 2011 at 11:26am
IP IP Logged
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Posted: 30 Aug 2011 at 2:51am
Ok. So you ar saying I should write a SQL query that unions the two tables and I didnt quite understand what you meant by  "Also when your union process address for table 2 which date to be using (of the 2 from your table)." and
" insert an identifier string for each row to let you know which table it came from (use a nice description that expalins what each table represents as this will show in your chart). " Could you give me an example of what you meant in those statements..I'm confused.
 
Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Aug 2011 at 3:47am

yes to the sql query (you can use a crystal command).

When you write it include in your process for determining which of the 2 fields to use as your date form table 2 (what you described in your original post as 'Date2 is a Crystal formula based field that has two other date fields embedded in it').
 
the description field is for you to know where each row of data came from, table 1 or table 2. If table 1 is data for sales call it sales, if table 2 is for orders call it orders.
 
You will end up with something like
 
select struc_id, date, 'Table1 is Sales' as description
from table1
 
union
 
Select struc_id, case when date1 <date2 then date1 else date2 end as date, 'Table2 is Orders' as description
from table2
IP IP Logged
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Posted: 30 Aug 2011 at 4:30am
Thanks for the clarification.  I will try it and see if it works. One more question about the sample tables is that they are excel sheets, because I cant create tables, since its only a read only database with an ODBC connection, I am trying to figure out how to union the two excel spreadheet tables using SQL because I have to make two seperate connections to access both Table1 and Table2, so now I am stuck with another dilemma of how to do a union for excel spreadsheet data tables. Thanks!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 30 Aug 2011 at 4:35am

never tried that so not sure.

You could create a 3rd spreadsheet doing the same thing as the union and jsut use that as your source for the report.
IP IP Logged
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Posted: 30 Aug 2011 at 9:11am
Ok Thanks. Let me see what I get.
IP IP Logged
moontide
Groupie
Groupie
Avatar

Joined: 23 Feb 2011
Online Status: Offline
Posts: 78
Quote moontide Replybullet Posted: 30 Aug 2011 at 10:24am
Now, since the dates have merged. Now how do I do two seperate counts because I want to see two lines in the chart, one for Date1 and one for Date2, so how is the union criteria helping to achieve that and date2 has a smaller range than Date1 so the query shoould be
Select struc_id, case when date2 < date1 then date2 else date1 end as date, 'Table2 is Orders' as description
from table2
 
Has anybody come across a line chart in Crystal reports where there are two lines across a date range and one of the lines stops in the middle of the chart because the count ends in the middle? Does anybody have any experience with a chart like that? Below is an illustration of what I want to see. The 'Star' shaped line is the count for Date2 and the 'flat' line is the count for Date1 which extends into december. I have been asking all the experts, seems like they dont have a solid answer whether it can be done or not, I'm sure some people have come across this problem...if somebody knows,  please let me know. Thanks!
 
|
|
|
|
|      
| ********************
|    ------------------------------------------------------
|------------------------------------------------------------
   Jan  Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
IP IP Logged
Page  of 3 Next >>
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.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.