Column appearing blank in SSRS query builder but fine in SQL Management Studio

  • Hi All,

    I've created a column in by sql query where by if i run the report from 27/05/2013 to 30/06/2013, any data occuring during WC 27/05/2013 the column will populate as Week 1, anything during WC 03/06/2013 will populate as Week 2 etc...

    This is so when I put this into SSRS I can create a Pie Chart for Weeks 1,2 etc for the duration mentioned above, plus when I change the dates, Week 1, Week 2 etc will change accordingly.

    DECLARE @DisplayTo as DATETIME

    declare @prevmonth as datetime

    declare @prevweek5 as varchar (50)

    declare @prevweek4 as varchar (50)

    declare @prevweek3 as varchar (50)

    declare @prevweek2 as varchar (50)

    declare @prevweek1 as varchar (50)

    DECLARE @ToDate as DATETIME

    SET @DisplayTo = '2013-06-30 23:59:59'

    SET @ToDate = dbo.fUniversalTime(@DisplayTo)

    set @prevmonth = dateadd(week,-4,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @DisplayFrom), @DisplayFrom))

    set @prevweek5 = CONVERT(varchar(50),dateadd(week,-4,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)

    set @prevweek4 = CONVERT(varchar(50),dateadd(week,-3,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)

    set @prevweek3 = CONVERT(varchar(50),dateadd(week,-2,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)

    set @prevweek2 = CONVERT(varchar(50),dateadd(week,-1,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)

    set @prevweek1 = CONVERT(varchar(50),dateadd(week,0,DATEADD(dd, @@DATEFIRST - DATEPART(dw, @ToDate), @ToDate)),103)

    Here I set my @prevweek parameters to the WC Date of the previous 5 weeks, then...

    case when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek5 then '1'

    when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek4 then '2'

    when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek3 then '3'

    when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek2 then '4'

    when CONVERT(varchar(50), (DATEADD(dd, @@DATEFIRST - DATEPART(dw, cons.StartDate), cons.StartDate)), 103) = @prevweek1 then '5' End as 'RptWeek',

    I match up the WC parameters to the WC Dates I get from my data and label them weeks 1 - 5 which i can then use as a filter in my Pie Charts in SSRS so i can have pir chart just for Week 1's data. This works fine in SQL Studio but when I put this into SSRS the column just appears blank.

    Any help on why this is or know a better way of labeling the preceeding weeks as 1,2,3 etc instead of the acutal week number would be much appreciated.

  • Managed to sort it. I missed out Set Datefirst 1 at the top my query after all that!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply