Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Column appearing blank in SSRS query builder but fine in SQL Management Studio Expand / Collapse
Author
Message
Posted Thursday, July 25, 2013 5:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:56 AM
Points: 15, Visits: 68
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.


Post #1477440
Posted Thursday, July 25, 2013 6:13 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 11, 2014 7:56 AM
Points: 15, Visits: 68
Managed to sort it. I missed out Set Datefirst 1 at the top my query after all that!
Post #1477451
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse