• Status: Solved
• Priority: Medium
• Security: Public
• Views: 421

# Charting Dates

I have a data set that is fairly simple but forhte life of me can't figure out how to chart it the way the client wants. This has me rather fustrated because i feel like i am missing something fairly obvious.

Dataset of Students (who have dropped out of a program) with their last day of attendance.

Student Id     LastDate
100001         5/1/2005
100012         4/24/2005

Need a calendar graph to indicate trends of when in the term most of them are dropping. I would expect this would have a X axis as the date range of the term a datapoint for each week or day or month (some would have 0) and the Y would be the numbers.

In crystal it would seem a scatter chart would do this well, but I can't figure out how to get the date range for the X axis.
0
datagears
• 3
• 3
• 3
• +2
1 Solution

Commented:
In theory... you want to create a formula that does the max date for each student.  This would be his last attendance record.  Then in the chart, you want to use the formula in the "On Change of", and Show value would be the count of students.

Hope this helps.
0

Author Commented:
I have the last date of attendance stored in the dataset. "ON change of" for date field "LDA" gives me a chart but not one proportional to a calendar (i.e. there are no X datapoints that are zero) just a count for each date that someone dropped. Not helpful if I am trying to trend to see if drops are happening at a certain point in the month.
0

Commented:
The X-axis will depend on the date range you want to compare against.  You mentioned that you wanted to create the chart to determine when in the term most of the dropped the class.  If you X axis was the period equal to the term of the class(es) the Y axis would plot the point at which they dropped the class.  It sounds like you not so much concerend with identifying the student, just trying to discover a pattern.  Is this correct ?

0

Commented:
You may want to switch to a bar or line graph. In addition if your column contains a datetime you will want to roll it up to the day.  Under On change of, there is a Order button.  You will want to change The section will be printed area to day.
0

Author Commented:
jaysin144 -

Yes that is exactly what i want BUT i can't figure out a way to spcify a date range to the X axis in Crystal. That woudl be the simple thing I think i am missing.

wykabryan -

that option was greyed out and doesn't address needing to specify a date range for the X axis.
0

Commented:
THe only way to get what you want is to ensure all the dates are there.  In your data set there are missing dates since there are dates when nobody dropped.

The usual way to get this is to create a table of the dates then use it as the master table and left join the pother tables to it.

mlmcc
0

Commented:
Ok.. so not every day does some one drop out of a class.  mlmcc suggestion would be the only way to ensure that you get all of the dates.  There might be another way where, in the report you create the dates where you are missing the dates.  I saw it once up here, but did not book mark it.
0

Commented:
What if you created a incrementing formula with say the start of the semester as the initial value and the end date of the semester. Use that as the x axis?
0

Commented:
wykabryan:

What you meant can be done by adding any table with enough rows to your report.
Create a formula like

CDate(RecordNumber + x)

where x is an offset in days from december 30, 1899. x = 36526 would get you started at 01-01-2000.

Then link the formula to the datefield from the table that contains the needed data.

--
Outin
0

Commented:
The reference you are looking for is

Since it is a WhilePrintingFormula you will also need to reference
Charting on Print Time formulas

mlmcc
0

Author Commented:
The crystal example is clunky at best and not well written. But mlmcc get me thinking in a alternate direction. The solution (which worked amazingly well in the end) was ....

To write a stored procedure that took a date range and to create an output table on thy fly that had a count for every date in the date range (by doing a loop, capturing the count, then adding a day to the date with datadd() and repeating until the end of the range was reached) then end result was a table for every day in my date range with a count. The crystal report then used the stored procudure as it's datasource.

I think sometimes Crystal does to much for you that you forget the more simple things that you might need to program for.
0

Commented:
Excellent.  That is probably better than creating a permanent table with all possible dates.