Creating summaries across data groups 1

[MattBurr]

I want to create a report that looks at the outstanding work across several teams for every day for the last 5 months.

I would like it to look something like:

1/1/2011 2/1/2011

Team 1 10 5
Team 2 5 10
Team 3 7 6

I have the data for each piece of work including the team that did the work, the date the work entered the team and the date the work was completed.

I can set up a report that looks at the work in the team on any particular day fairly simply but can't work out how to have it summarise against every day for the last few months. Work could be in the team for several days so the summarys would count each case more than once.

I've tried using cross tabs and grouping but as I'm tryign to summarise across the group/row/column headings can't make that work.

 

[Madawc]

I'd have thought it was a natural for Crosstab, though you should have the teams as columns and the dates as rows. If your Crystal version is reasonably modern (and it helps to say which you have) then you could create a formula at detail-line level and report it in your crosstab.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[MattBurr]

Thanks for replying. I'm using Crystal Report 2008.

The data I have is in the format:

Team Start Date End Date
Team 1 1/1/2011 2/1/2011
Team 2 1/1/2011 3/1/2011
Team 1 1/1/2011 4/1/2011

So the report I would like to have if I just had these three records would be:

Team 1 Team 2
1/1/2011 2 1
2/1/2011 2 1
3/1/2011 1 1
4/1/2011 1 0

Each record could then appear in several rows as it should count against each team for each date until the work is completed.

I've been working on trying to get each record in the detail section to check against the cross-tab row, or group (i've tried with both) to see if the group/row header is betweeen the start and end dates but can't get it to work.

I can make it work if I'm checking it against a particular date that I could either put directly into the formula or set as a parameter but can't get it to automatically check against every row on the cross tab.

I get the feeling I'm either going about this completely the wrong way or need to have a much better understanding of creating formula's and cross-tab/grouping to make it work.

 

[lbass]

What version of CR are you using? What kind of database? Do you have a table that contains all dates? Are your dates above formatted like "dd/MM/yyyy"?

-LB

 

[Madawc]

Are you able to get tables / datasets added to the database? If you got a table of DATE set up, this could be linked to the activity using LEFT OUTER and you'd still get a slot when there was no activity. (But note that any selection on the second table must do an IsNull test first, otherwise you'll not get the dates with no records, null value for the second record.)

If you can't add a table, you'll be stuck with creating a 'Mock Crosstab'. Crosstabs are fine if you don't mind the absence of rows or columns with no data, but you said you wanted empty slots to show.

A 'Mock Crosstab' is something that looks like a Crosstab, but in fact you define each column yourself, normally as a running total. This would need to go in the report footer, because running totals count as the reports 'run' and they will not be complete until then. Crystal should have included an example along with the Crosstabs.

You can save a little time by doing a paste to a dummy report, changing the name and then pasting back. In Crystal 11.5, you can also duplicate formula fields using the Field Explorer.

Each running total will count the record if it was within the criteria - in your case, date.

Madawc Williams (East Anglia, UK). Using Crystal 11.5 with SQL and Windows XP

 

[MattBurr]

Madawc

Thanks. A mock cross-tab works perfectly.