Help with expression to display a per-week daterange value!

dvang

Here is the table I¡¯m working with:

dbo.Reject

Rejections	Amount	Date
5	12	1/4/07
9	3	1/6/07
1	6	1/23/07
0	0	2/11/07
7	8	2/20/07

Is it possible to write an expression for RS2005 in the details row in a table that will separate and add the rejection and amount field values for each calendar week and lay them out to look like below:

Week	Rejections	Amount
1	14	15
2	0	0
3	0	0
4	1	6
5	0	0
6	0	0
7	0	0
8	7	8

I¡¯m trying to make a report that shows the number of rejections and amounts on a week to week basis. So I need RS2005 to add the rejections and amounts for each week date range and display it line-by-line automatically on a per-week basis.

Week 1 is 1/1/07-1/6/07 according to DATEPART(WEEK,Date) and so on¡­

Thanks in advance!

Jarret

Hello,

If you add a grouping to your table on =DatePart("ww", Fields!date.Value), you will get rows where there is data for that week. Then you can just hide your Details and Group Footer rows.

Looks like this:

Week	Rejections	Amount
1	14	15
4	1	6
7	0	0
8	7	8

Hope this helps.

Jarret

dvang

Thanks for the help, it worked well. I had to add SUM(Fields!Rejections.Value) and SUM(Fields!Amount) to add the rejections and amounts up for each line per week, but in all, it did work! I bet you assumed I knew that, but I didn't, but I did figure it out.

Now my issue is how do I make it so it shows all the weeks Refer to my goal example above. I was able to replicate Jarret's results, but I'd like it to show the missing weeks of 2,3,5,6 with 0 values. Keep in mind there are no data that falls into these weeks, I just want to display the weeks and show 0 as values for both rejections and amounts.

What do i need to put into the expression to place so-called "dummy" weeks on the report

Jarret

Hello,

Yes, sorry about that, I should have put the SUM()'s in my post.

In order to get all the weeks, your SQL query will have to return them all, whether or not they have reject data. Then you can replace the NULL's with 0. One way to accomplish this i:

1. Create a table in your database (you can actually do this from your dataset, as shown below) that holds the possible values for any date using DatePart on the week (1 - 53).

2. Modify your SQL query to join this table and make sure all these records are returned (LEFT/RIGHT OUTER JOIN).

3. Use isnull() in your query to replace with 0.

I don't know your query, but here's what I think your dataset should look like:

Code Snippet

declare @DatePartWeeks table (WeekNumber tinyint)

declare @i as int

set @i = 1

while (@i < 54)

begin

insert into @DatePartWeeks (WeekNumber) select @i

set @i = @i + 1

end

select dpw.WeekNumber, isnull(r.Rejections, 0), isnull(r.Amount, 0), r.Date

from dbo.Rejects r

right outer join @DatePartWeeks dpw on DatePart(ww, r.Date) = dpw.WeekNumber

By doing this, you can change your table grouping to group on the WeekNumber instead of the DatePart calculation (since you've already done it in the query). And, of course, leave the SUM()'s in your group header/footer.

Hope this helps.

Jarret