Grouping by Week (With a Twist)

[khdavis]

Hi All! I really hope someone can help me. I need to develop a report based on year-to-date data, grouped by week.

For each week I need to count the number of open problem tickets. For instance, if the report runs today and includes a problem ticket that's still open that was originally opened 6 weeks ago, that ticket needs to be counted each of those 6 preceding 6 weeks (not just in the week that it was opened). Hope I properly explained what I'm trying to do.

Can I do this in Crystal? Thanks in advance...

 

[morriskc]

Do you count tickets that were open for several weeks but are now closed or just open tickets when the report is generated?

If I understand you correctly, any ticket that is open today has to be counted for every week since it opened. Therefore, you count the number of open tickets, grouped by open date. Include a running total column.

Please explain in more detail if I don't understand your requirements.

 

[lyanch]

I have similar reports to this. I have never been able get crystal to do this without using a stored proc. I think you could probably do it in CR9 however.

I send in start and end dates, the sP creates week ranges from them, then I get counts where between the two dates

is to start with the total open on the first day and add opens and subtract closes by week in between...

It depends on your datastructure which one would be easier.. and, depending on your structure their could easily be another method that would work better.

Lisa

 

[SmpTraining]

If you create a table with all the weeks defined, you could write a report to run against the week table and have a subreport checking whether there were any open 'problem' tickets during that week.

A bit of variable sharing will let you accumulate the results in the main report and achieve what you are looking for.

In more detail, this would require you to create a new table called WEEKS containing three fields
1. WEEK NUM
2. START DATE
3. END DATE

You would need to populate this with all the weeks you would have. I would guess this would contain just 52 weeks for the current year but if you include a YEAR field, you could have as many years worth of data as you liked.

Create a main report based on the week table where the selection criteria is START DATE < CurrentDate.

The report will show each week number in a different details section.

Now write a sub-report listing your open 'problem' tickets and include a count. Pass in the START and END Dates to the subreport and make sure you only select those problem tickets which fall between the START and END Dates.

Use some shared variables to get the count of tickets returned back to the main report and maintain another variable in the main report to accumulate these values.

Steve Phillips, Crystal Consultant