Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Date Range Group
- Thread starter TEM3
- Start date
TEM3,
I am unsure what you are asking - uncertain on "independent of any table fields". Is there a date field in your Database which this range would be based on?
If this is the case you could likely create a Formula Field to generate a grouping...?
Hope this helps,
Mike
______________________________________________________________
![[banghead]](/data/assets/smilies/banghead.gif "[banghead] [banghead]")
"It Seems All My Problems Exist Between Keyboard and Chair"
I am unsure what you are asking - uncertain on "independent of any table fields". Is there a date field in your Database which this range would be based on?
If this is the case you could likely create a Formula Field to generate a grouping...?
Code:
IF {table.field} in [Date(2007,03,01) TO Date (2007,03,07)] THEN "Week 1" ELSE
IF {table.field} in [Date(2007,03,08) TO Date (2007,03,15)] THEN "Week 2"Hope this helps,
Mike
______________________________________________________________
"It Seems All My Problems Exist Between Keyboard and Chair"- Thread starter
- #3
Well, perhaps by attempting to explain what I wish to do someone can help me with a plan.....
I have one table {ALL_ASSIGNMENTS} that contains the information about who did what when. There are three series of fields that record activity:
{ALL_ASSIGNMENTS.Analyst Assigned} and {ALL_ASSIGNMENTS.Draft Date} would give who completed an assignment and when.
{ALL_ASSIGNMENTS.Reviewed By} and {ALL_ASSIGNMENTS.Review Date} would give who reviewed an assignment and when.
{ALL_ASSIGNMENTS.Approved By} and {ALL_ASSIGNMENTS.Approved Date} would give who approved an assignment and when.
There are probably 200 people who could have done each of those activities for an assignment. Mid level supervisors would do the reviewing and, perhaps, complete some assignments. Higher level supervisors might do some review, but mostly do the approvals.
The first field in each set would be populated by the 6 character user id for each possible person. Each record in the table represents the activity for on assignment.
I can easily do three reports with three cross tabs to total each activity for each user involved (and have done so). I would like to fashion a report that I could specify a starting date and and ending date, and parse the table for any of the three activities between those dates and count said activities.
The report would state that:
Joe Blow completed 45 assignments, reviewed 25 assignments and approved 10 assignments between date1 and date2.
I would want to do that for each possible analyst/supervisor automatically.
The table that contains the user id, name and other appropriate information for each person is {ANALYST}.
Back in the prehistoric days when I did reports using dBaseIII language I could do something like this very easily!
I have one table {ALL_ASSIGNMENTS} that contains the information about who did what when. There are three series of fields that record activity:
{ALL_ASSIGNMENTS.Analyst Assigned} and {ALL_ASSIGNMENTS.Draft Date} would give who completed an assignment and when.
{ALL_ASSIGNMENTS.Reviewed By} and {ALL_ASSIGNMENTS.Review Date} would give who reviewed an assignment and when.
{ALL_ASSIGNMENTS.Approved By} and {ALL_ASSIGNMENTS.Approved Date} would give who approved an assignment and when.
There are probably 200 people who could have done each of those activities for an assignment. Mid level supervisors would do the reviewing and, perhaps, complete some assignments. Higher level supervisors might do some review, but mostly do the approvals.
The first field in each set would be populated by the 6 character user id for each possible person. Each record in the table represents the activity for on assignment.
I can easily do three reports with three cross tabs to total each activity for each user involved (and have done so). I would like to fashion a report that I could specify a starting date and and ending date, and parse the table for any of the three activities between those dates and count said activities.
The report would state that:
Joe Blow completed 45 assignments, reviewed 25 assignments and approved 10 assignments between date1 and date2.
I would want to do that for each possible analyst/supervisor automatically.
The table that contains the user id, name and other appropriate information for each person is {ANALYST}.
Back in the prehistoric days when I did reports using dBaseIII language I could do something like this very easily!
Time to upgrade. If you had a higher version, you could use a command to create a union statement that would combine the date fields into one, and the user field into one, and which would also set the date to fall within a parameter range.
While it is possible to create a union statement in 8.5 in the Show SQL Query area, you would have to hard code the date range, since you wouldn't be able to build in a parameter.
-LB
While it is possible to create a union statement in 8.5 in the Show SQL Query area, you would have to hard code the date range, since you wouldn't be able to build in a parameter.
-LB
I think you can do it in Crystal 8.5, but not by date selection. Get all of the records details that are needed, along with a lot that you don't need but can't avoid including.
Do running totals, which count or do not count according to a date test in the formula section. Put these running totals in the report footer. Or have a formula field that strings them (as ToText) along with the text you want.
The basics of Crystal totals are explained at FAQ767-6524.
![[yinyang]](/data/assets/smilies/yinyang.gif "[yinyang] [yinyang]")
Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10
Do running totals, which count or do not count according to a date test in the formula section. Put these running totals in the report footer. Or have a formula field that strings them (as ToText) along with the text you want.
The basics of Crystal totals are explained at FAQ767-6524.
Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10 I didn't mean to imply that you couldn't do the calculations in 8.5, because you could use a selection statement uses "ors" like:
draftdate = {?range} or
reviewdate = {?range} or
approvedate = {?range}
This will return many more records than you actually need, but you could as Madawc suggests, use running totals to get the desired results, probably at a group level, where the group is based on the user ID.
However, this does not result in a common date field that you could group by--which was what I was referring when suggesting you upgrade.
-LB
draftdate = {?range} or
reviewdate = {?range} or
approvedate = {?range}
This will return many more records than you actually need, but you could as Madawc suggests, use running totals to get the desired results, probably at a group level, where the group is based on the user ID.
However, this does not result in a common date field that you could group by--which was what I was referring when suggesting you upgrade.
-LB
- Thread starter
- #7
- Status
Similar threads
- Locked
- Question