Technical Questions: How to Zero out values from a line graph

Print Page | Close Window

How to Zero out values from a line graph

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2022
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14219
Printed Date: 19 Apr 2025 at 1:00pm

Topic: How to Zero out values from a line graph

Posted By: moontide
Subject: How to Zero out values from a line graph
Date 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

Replies:

Posted By: DBlank
Date 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?

Posted By: moontide
Date 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!

Posted By: DBlank
Date 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

Posted By: moontide
Date 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!

Posted By: DBlank
Date 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

Posted By: moontide
Date 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!

Posted By: DBlank
Date 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.

Posted By: moontide
Date Posted: 30 Aug 2011 at 9:11am

Ok Thanks. Let me see what I get.

Posted By: moontide
Date 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

Posted By: DBlank
Date 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

Posted By: moontide
Date 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.

Posted By: DBlank
Date 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)

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.

Posted By: moontide
Date 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

Posted By: DBlank
Date 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

Posted By: moontide
Date Posted: 01 Sep 2011 at 2:17am

I added several extra rows for table2 but its still not working.

Posted By: DBlank
Date 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?

Posted By: moontide
Date 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.

Posted By: DBlank
Date 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?

Posted By: moontide
Date Posted: 01 Sep 2011 at 5:27am

Yes, the 'date' field is above the table descritpion field in the 'change by'.

Posted By: DBlank
Date Posted: 01 Sep 2011 at 5:36am

you are stumping me on this one...

lets try 2 formulas

lets convert the date field to the first of the month (call it "DateConverted")

dateserial(year(table.date),month(table.date),1)

Let's convert the tabledescription to another text (call it "NewText")

if table.tabledescription = "table1" then "SALE" else

if table.tabledescription = "table2" then "RETURN" else 'ERROR"

in your chart on change of use @DateCOnverted set to monthly and then @NewText under that

in the Show Values continue to use Struc_ID set to count

Does this give you anything better?

Posted By: moontide
Date Posted: 01 Sep 2011 at 7:19am

I used all the instructions you put forth. Now, in the legend box I see a green line saying 'Sale', a yellow line saying 'Return' and a blue line saying 'Error' and the lines are again, all over the chart.

Posted By: DBlank
Date Posted: 01 Sep 2011 at 7:30am

OK.

Go into the select expert and add in a criteria

Not (isnull(table.date))

that should get you down to only have 2 Legend Entries and the 'ERROR' label should be gone.

Let's make another formula and call it "counting"

if isnull(table.struc_id) then 0 else 1

now in the chart expert replace your Show Value with the new formula field @counting set as a SUM (should defualt to that)

Does this give you what you wanted/expected?

Posted By: moontide
Date Posted: 01 Sep 2011 at 7:53am

Ok I did all of that, but I'm getting the same results as before. No change. I see all three lines again.

Posted By: DBlank
Date Posted: 01 Sep 2011 at 8:50am

I'm stumped at this point.

Posted By: moontide
Date Posted: 01 Sep 2011 at 9:08am

I was stumped myself and tried everything, nothing worked. I may just have to do seperate charts for each count and work it from there which is the only other option, but thanks for the effort you put in to help..really appreciate it!!..THANKS again !.

Print Page | Close Window