how to group sub report by week?

[ericb123]

using CR11 and SQL2000, I have a report called "productivity" which lists people's productivity, grouped by username, then by date (week). This works great.

Now I'm trying to add in a sub-report called "quality" where I take the shared values from the subreport, an put them on the main report, where the sub report is also grouped by username, then by date (week). The subreport is linked to the main report by username.

However, the subreport values are only showing the value from the last week, not each week, although they work correctly in the subreport if I run it manually.

The subreport, run on it's own looks like this:

NAME QUALITY WEEK
bob 151 9/1
bob 322 9/7
bob 312 9/14

but when I link it in the main report, I get this

NAME PRODUCTIVITY QUALITY WEEK
bob 55 312 9/1
bob 34 312 9/7
bob 31 312 9/14

It's just taking the quality from the last week, and using it on each row. How can I get each week's quality to match up on the main report? Any help is greatly appreciated, thanks!

 

[nobull613]

You'll need to put the subreport in the group header of the week grouping (Group2). Since the subreport is running only once for the username (linked only by username) it returns the last value that it hits (the final week).
You'll need to link by the week or possibly a formula to calculate the week if you're summarizing daily values into the week group.

 

[kskid]

You started on Monday, 9/1 instead of Sunday,8/31, which coincides with 9/7 and 9/14. Was this intentional? If so, how do you want to handle the weekly breakdown when the end of the month does not end on a Saturday?

Secondly, is there a date selection in the subreport that is causing just the last week from being selected?

I haven't tried this before, but if the subreport is located in the weekly group footer, then you should be able to link the weekly date as well. Then in the subreport record selection do

{table.date} in [{?pm-groupdate} to {?pm-groupdate}+6]

 

[ericb123]

thanks for the replies, I only used 9/1 instead of 8/31 as an example, the real data/dates were 8/31

There is a date selection in the subreport, but it's not the same date as the main report.

The main report is called "productivity" and the date range is set for {productivity.date} The subreport is called "quality" and its date range is {quality.date}. So they're two different reports, with data from 2 different tables, but they're for the same employees, so rather than give a manager 2 reports one showing productivity, the other showing quality for the same people, I'm trying to combine them onto 1 report, with 1 row showing productivity and quality for the same employee.

I tried to link the subreport by productivity.date to quality.date, but the resulting shared values are zero, so I don't think they can be matched together. It's more trying to match the group?

Any help is greatly appreciated, thanks!

 

[nobull613]

You could put a formula into both the main report and the subreport to calculate the 1st day of the week for the productivity date and quality date. Change your 2nd group to the formula and use it in your subreport link. The formula to calculate the 1st weekday (assuming you want Sunday's date) is, for the Productivity:

If Dayofweek({productivity.date})=1 then {productivity.date} Else
If DayofWeek({productivity.date})=2 then DateAdd("d",-1,{productivity.date}) Else
If DayofWeek({productivity.date})=3 then DateAdd("d",-2,{productivity.date}) Else
If DayofWeek({productivity.date})=4 then DateAdd("d",-3,{productivity.date}) Else
If DayofWeek({productivity.date})=5 then DateAdd("d",-4,{productivity.date}) Else
If DayofWeek({productivity.date})=6 then DateAdd("d",-5,{productivity.date}) Else
If DayofWeek({productivity.date})=7 then DateAdd("d",-6,{productivity.date})

Simply switch out the {productivity.date} with {quality.date} in the subreport.

This should supply total productivity numbers and total quality numbers for employee for the week.

 

[lbass]

Create a formula in the main report {@week}:

datepart("ww",{productivity.date})

Create the same formula in the subreport, but substituting the quality date. Then add a subreport link that links the two {@week} formulas to each other (in addition to the link you already have).

-LB