Summary on two ranges of dates? 1

[Laurelanwyn]

Using Crystal 8.5 and a MS SQL database
I am trying to create a report that does a count of total number of jobs and a summary of the cost of the jobs. I need to have two columns for each category of jobs. One for the last week (or another parameter) and one that is all jobs since the begining of data collection. I do not know how to create summaries based on the date ranges with out doing a subreport. Since this report is already a subreport of another report, I can't use the subreport feature.
Does any one have any ideas?

Thanks!

 

[Thadeus]

Is it possible to align two subreports next to each other in the container rather than try to do two queries from within one subreport?

~Thadeus

 

[Laurelanwyn]

Unfortunatley, not really. They want to see the categories tiled across and then down on the page. They want the new totals for each category next to the cumulative totals.

 

[lbass]

You should be able to do this with formulas to be placed in the detail section:

//{@cntjobslastwk}:
if {table.date} in lastfullweek then 1 else 0

//{@cntjobsbeflastwk}:
if {table.date} < minimum(lastfullweek) then 1 else 0

//{@costlastwk}:
if {table.date} in lastfullweek then {table.cost} else 0

//{@costbeflastwk}:
if {table.date} < minimum(lastfullweek) then {table.cost} else 0

Insert a group on {table.jobcategory} and then right click on each of these formulas and choose "Insert summary" (SUM for all of these formulas, not count) and choose for all groups and the grand total. Then suppress the details so that the results are aligned in the group/report footers.

-LB