datagears
asked on
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.
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.
ASKER
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.
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 ?
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.
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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?
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
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
The reference you are looking for is
http://support.businessobjects.com/library/kbase/articles/c2002147.asp
Since it is a WhilePrintingFormula you will also need to reference
Charting on Print Time formulas
http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_charting_print_time_formulas.pdf.asp
mlmcc
http://support.businessobjects.com/library/kbase/articles/c2002147.asp
Since it is a WhilePrintingFormula you will also need to reference
Charting on Print Time formulas
http://support.businessobjects.com/communityCS/TechnicalPapers/cr9_charting_print_time_formulas.pdf.asp
mlmcc
ASKER
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.
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.
Excellent. That is probably better than creating a permanent table with all possible dates.
Glad i could help
mlmcc
Glad i could help
mlmcc
Hope this helps.