sqlsql-serverreporting-servicesssrs-grouping

Group days by week sql server / ssrs

I'm going to rephrase a question that I prevously had asked.

I want to group dates by week. I currently have two functions: The first of which returns all fiscal weeks of the year and the second all the dates.

I do a left outer join on the Fiscal weeks from the days to get this result set.

 4/4/2010    4/4/2010 
 NULL        5/4/2010 
 NULL        6/4/2010 
 ...
 11/4/2010   11/4/2010
 NULL        12/4/2010

In SSRS, I want to group the days by weeek, so I need to have the Null values having the fiscal week that the dates are part of, any ideas of how to do this?

Solution

  • I found an alternative way to fill the null. This was by using the datepart function within SQL to return the week number.

    SELECT DATEPART(WEEK, specified_date)

    Which returns the number of the week. I then did a further expression in the cell for the week column in SSRS, to make it co-align with orignal fiscal week number and now have.

     1    4/4/2010 
 1    5/4/2010 
 1    6/4/2010 
 ...
 2  11/4/2010

    Which allows me to group by week in the SSRS.