SSRS Expression - Find First Day of Week From Week value

Question

I've got an SSRS report that returns unique login count to our VDI Pools broken down by week. For example for January it would read:

     Week                # of Logins  
2015_JANUARY_WEEK_NO_1       3  
2015_JANUARY_WEEK_NO_2       49

I'm using the Week column to link to another report that'll display the selected week's logins so I have to get the first day of the selected week and the last day of the select week and pass them to the other report as parameters.

I know how to get pull the year and week # from the Week column.

=Mid(Fields!Week.Value,1,4)

gives me the year and =Trim(Mid(Fields!Week.Value,Len(Fields!Week.Value),Len(Fields!Week.Value)))

gives me the week.

I found this T-SQL that works:

DECLARE @WeekNum INT
          , @YearNum char(4);

SELECT @WeekNum = 2 
     , @YearNum = 2015  
-- once you have the @WeekNum and @YearNum set, the following calculates the date range. 

SELECT DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + @YearNum) + (@WeekNum-1), 6) AS StartOfWeek;   
SELECT DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + @YearNum) + (@WeekNum-1), 5) AS EndOfWeek;

but I cannot figure out how to turn that into an expression that doesn't throw an error.

This is what I've got so far:

=DateAdd("w", DateDiff("w", 6, '1/1/' + (Mid(Fields!Week.Value,1,4))) + (Trim(Mid(Fields!Week.Value,Len(Fields!Week.Value),Len(Fields!Week.Value)))
- 1), 6)

and when I try to run the report in design view it returns an Expression expected error.

Edit Sorry, I guess I should've posted my original query that's populating the report. Here it is below:

SELECT Convert(varchar(20),UPPER(DATENAME(YEAR, Time)))
    +'_'+CONVERT(varchar(20),UPPER(DATENAME(MONTH, Time)))
    +'_WEEK_NO_'+CONVERT(varchar(10),(DAY(Time) 
    + (DATEPART(DW, DATEADD (MONTH, DATEDIFF (MONTH, 0, Time), 0))-1) -1)/7 + 1) as 'Week'
    , Count(DISTINCT SUBSTRING(ModuleAndEventText,LEN('User ') + 2
    , CHARINDEX(' requested', ModuleAndEventText) - LEN('User ') - 2)) as WeekCount
  FROM VE1_UserLogins
WHERE DesktopId = @Pool
AND ([Time] BETWEEN (@StartDate) and (DATEADD(ms, -1, @EndDate +1)))
GROUP BY Convert(varchar(20),UPPER(DATENAME(YEAR, Time)))
    +'_'+CONVERT(varchar(20),UPPER(DATENAME(MONTH, Time)))
    +'_WEEK_NO_'+CONVERT(varchar(10),(DAY(Time) 
    + (DATEPART(DW, DATEADD (MONTH, DATEDIFF (MONTH, 0, Time), 0))-1) -1)/7 + 1),YEAR(Time),MONTH(Time)
ORDER BY YEAR(Time), MONTH(Time), Week

What is your Fields!Week.Value value . What is it's data type ? — Mahesh
How is this Mid(Fields!Week.Value,Len(Fields!Week.Value),Len(Fields!Week.Value)) will work as you are getting Mid from starting from it's end to the length of it is the length of the string — Mahesh
That confused me as well but when I tried codeMid(Fields!Week.Value,Len(Fields!Week.Value)-1,Len(Fields!Week.Value)) it was returning the underscore before the number. — Mike Horton
DATENAME is a built in SQL variable. It automatically turns 1 into January, 2 into February, etc. — Mike Horton

Mahesh Mahesh · Accepted Answer · 2015-01-23T06:33:36

I am assuming your Fields!Week.Value is string in date format like MM/dd/yyyy or something similar so if you want to get the start of that week Sunday as first day and Saturday as last day and you want to get there date then you should use below expression,

For start of week,

  =DateAdd("d",1-  DatePart("w", CDate(Fields!WeekDate.Value)), CDate(Fields!WeekDate.Value))

For end of week,

=DateAdd("d", 7 - DatePart("w", CDate(Fields!WeekDate.Value)), CDate(Fields!WeekDate.Value))

----------

UPDATE Now there are two ways to achieve what you want

1)Calculate start of week and end of week at the sql and get the result to directly display on repott

2) Get time field from the sql and set expression in the SSRS

For the first way to achieve you need to add these two lines in your select sql statement

    DATEADD(dd, -(DATEPART(dw, MIN(Time))-1),MIN(Time)) AS 'StartOfWeek'
   ,DATEADD(dd, 7-(DATEPART(dw, MIN(Time))), MIN(Time)) AS 'EndOfWeek'

Second way to achieve is Update your Sql statement to get the date part and then include that date into the above given expression. So for you your query you need to add,

        ,MIN(Time) AS WeekDate

Then you can use the above expression with using the incoming field WeekDate as input(I have updated the expression).

But, If you have no actual need for the date other than calculating the start and end of week then use the first method to get the data from the sql server as formatted.

SSRS Expression - Find First Day of Week From Week value

1 Answers

----------