group by day of week, total # opened, # closed within week

[mmm704]

Hi I am trying to do something that probably is easy but I can't figure it out. How can I group records by week, with totals of #opened within that week and #closed within the same week. I can group by # opened and choose 'by week' and that works, I can get my totals of # opened within each week, but if I embed another group date closed under it, it will only count those records here that were both opened and closed within the same week. So I'm thinking I don't group on any field in the db, just by WEEK in general..and do my counts within that group of # opened, # closed. But can't figure out how to set up a group by just week?

 

[beacon5]

This is just a guess because I'm not at my computer where I have my Crystal Reports license, so you'll have to test this out.

I think you could still group by 'opened' and also create a formula for closed that returns a 1 if it's closed and a 0 if it's not.

if isnull({table.closed}) then
     0
else
     1

Then I think you can put the formula in the group header and do a summary in either the group footer or report footer, wherever you are totaling the numbers.

The only problem that you may run into, which I've run into before and had difficulty with, is if you want to show weeks that don't have any opened or closed in a week, but you still want to show the zero total.

 

[kenhamady]

You can't do this by grouping, because a single record could be in two different weeks at the same time, but a record can't be in 2 groups at the same time.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/