title: SSRS Expression - Find First Day of Week From Week value
body: <p>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:  </p>
  
  <pre><code>     Week                # of Logins  
  2015_JANUARY_WEEK_NO_1       3  
  2015_JANUARY_WEEK_NO_2       49
  </code></pre>
  
  <p>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.</p>
  
  <p>I know how to get pull the year and week # from the Week column. </p>
  
  <pre><code>=Mid(Fields!Week.Value,1,4) 
  </code></pre>
  
  <p>gives me the year and 
                                                                                                    <code>=Trim(Mid(Fields!Week.Value,Len(Fields!Week.Value),Len(Fields!Week.Value)))</code></p>
  
  <p>gives me the week.</p>
  
  <p>I found this T-SQL that works:   </p>
  
  <pre><code>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; 
  </code></pre>
  
  <p>but I cannot figure out how to turn that into an expression that doesn't throw an error.  </p>
  
  <p>This is what I've got so far:  </p>
  
  <pre><code>=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)
  </code></pre>
  
  <p>and when I try to run the report in design view it returns an Expression expected error.</p>
  
  <p><strong>Edit</strong> 
  Sorry, I guess I should've posted my original query that's populating the report. Here it is below: </p>
  
  <pre><code>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
  </code></pre>