Solved

# Charting Dates

Posted on 2006-05-16
411 Views
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
Question by:datagears

LVL 16

Expert Comment

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 Comment

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

Expert Comment

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

LVL 16

Expert Comment

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 Comment

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

LVL 100

Accepted Solution

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

LVL 16

Expert Comment

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

Expert Comment

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

LVL 9

Expert Comment

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

LVL 100

Expert Comment

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 Comment

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

LVL 100

Expert Comment

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

mlmcc
0

## Featured Post

### Suggested Solutions

There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
how to add IIS SMTP to handle application/Scanner relays into office 365.
This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.