Parameters not being used in report processing

Once again I've spent a whole morning going in the wrong direction with a convincing SQL script. This article is just a note on when using Business Intelligence Development Studio (BIDS) or Report Builder 2.0 and when previewing the report, the parameters are not accounted for (ie. the SQL query creating the dataset does not use the parameters in its query).


Problems:
  • Given a date, the date format was reverting to US format as opposed to European
  • Given a Campus as a text value, the SQL query was ignoring this completely
  • Hardcoding the scalar local variables worked

I have two parameters that will be submitted (GivenDate and GivenCampus). Consider the following script:
copyraw
-- ===============================================================================
-- Description:	Gets the room bookings for a week based on a given date and campus
-- ===============================================================================

DECLARE
	@GivenDate datetime,
	@GivenCampus varchar(100),

	@setId varchar(10),
	@weekNumber int,
	@siteId nvarchar(5),
	@baseDate datetime,
	@specifiedDate datetime,
	@specifiedCampus varchar(25)
	
	SET DATEFORMAT dmy

	--SET @GivenDate = '1/7/2011'
	--SET @GivenCampus = 'Lansdowne Campus'
	
	SET @specifiedDate = (SELECT @GivenDate)
	SET @specifiedCampus = (SELECT @GivenCampus)

	-- Get columns SetId and WeekNumber from the weekstructure table based on the given date
	SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 6, @specifiedDate))
	SET @weekNumber = (SELECT TOP 1 WeekNumber FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 1, @specifiedDate))

        -- Get the SiteID based on the given campus and the setId (based on the given date)
	SET @siteId = (SELECT TOP 1 SiteId FROM SITES WHERE SiteName=@specifiedCampus AND SetId=@setId)
		
	-- Get the start date for the week in question
	SET @baseDate = (SELECT TOP 1 StartDate FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 1, @specifiedDate))
		
	SELECT
		@specifiedDate AS 'GivenDate',
		dateadd(d, tt.[WeekDay] - 1, @baseDate) AS 'Date',
		datename(weekday, dateadd(d, tt.[WeekDay] - 1, @baseDate)) AS 'Day', 
		tt.[SiteId] AS 'Site',
		tt.[StartTime] AS 'Start', 
		tt.[FinishTime] AS 'Finish',
		tt.[RoomId] AS 'Room', 
		sd.[Descrip] AS 'Event Type', 
		sd.[Notes] AS 'Booking Notes',
		c.[Name] AS 'Contact', 
		c.[Company] AS 'Dept', 
		c.[Phone] AS 'Phone'
		
	FROM [CMISdata].[cmisuser].[SLOTDETAILS] sd
	INNER JOIN [CMISdata].[cmisuser].[TIMETABLE] tt 
		ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId]
	INNER JOIN [CMISdata].[cmisuser].[CONTACT] c 
		ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId]
	INNER JOIN [CMISdata].[cmisuser].[WEEKMAPNUMERIC] wm 
		ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId])
		
	WHERE sd.[SetId] = @setId
	--AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @SelectedDate
	AND wm.[WeekNumber] = @weekNumber
	AND tt.[SiteId] = @siteId
	AND tt.[Status]  3		-- Remove cancelled bookings
	AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%'
	
	ORDER BY Date ASC, Room ASC, 
			tt.[StartTime] ASC, 
			tt.[FinishTime] ASC
  1.  -- =============================================================================== 
  2.  -- Description:    Gets the room bookings for a week based on a given date and campus 
  3.  -- =============================================================================== 
  4.   
  5.  DECLARE 
  6.      @GivenDate datetime, 
  7.      @GivenCampus varchar(100), 
  8.   
  9.      @setId varchar(10), 
  10.      @weekNumber int, 
  11.      @siteId nvarchar(5), 
  12.      @baseDate datetime, 
  13.      @specifiedDate datetime, 
  14.      @specifiedCampus varchar(25) 
  15.   
  16.      SET DATEFORMAT dmy 
  17.   
  18.      --SET @GivenDate = '1/7/2011' 
  19.      --SET @GivenCampus = 'Lansdowne Campus' 
  20.   
  21.      SET @specifiedDate = (SELECT @GivenDate) 
  22.      SET @specifiedCampus = (SELECT @GivenCampus) 
  23.   
  24.      -- Get columns SetId and WeekNumber from the weekstructure table based on the given date 
  25.      SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 6, @specifiedDate)) 
  26.      SET @weekNumber = (SELECT TOP 1 WeekNumber FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 1, @specifiedDate)) 
  27.   
  28.          -- Get the SiteID based on the given campus and the setId (based on the given date) 
  29.      SET @siteId = (SELECT TOP 1 SiteId FROM SITES WHERE SiteName=@specifiedCampus AND SetId=@setId) 
  30.   
  31.      -- Get the start date for the week in question 
  32.      SET @baseDate = (SELECT TOP 1 StartDate FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @specifiedDate) AND DATEADD(day, 1, @specifiedDate)) 
  33.   
  34.      SELECT 
  35.          @specifiedDate AS 'GivenDate', 
  36.          dateadd(d, tt.[WeekDay] - 1, @baseDate) AS 'Date', 
  37.          datename(weekday, dateadd(d, tt.[WeekDay] - 1, @baseDate)) AS 'Day', 
  38.          tt.[SiteId] AS 'Site', 
  39.          tt.[StartTime] AS 'Start', 
  40.          tt.[FinishTime] AS 'Finish', 
  41.          tt.[RoomId] AS 'Room', 
  42.          sd.[Descrip] AS 'Event Type', 
  43.          sd.[Notes] AS 'Booking Notes', 
  44.          c.[Name] AS 'Contact', 
  45.          c.[Company] AS 'Dept', 
  46.          c.[Phone] AS 'Phone' 
  47.   
  48.      FROM [CMISdata].[cmisuser].[SLOTDETAILS] sd 
  49.      INNER JOIN [CMISdata].[cmisuser].[TIMETABLE] tt 
  50.          ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId] 
  51.      INNER JOIN [CMISdata].[cmisuser].[CONTACT] c 
  52.          ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId] 
  53.      INNER JOIN [CMISdata].[cmisuser].[WEEKMAPNUMERIC] wm 
  54.          ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId]) 
  55.   
  56.      WHERE sd.[SetId] = @setId 
  57.      --AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @SelectedDate 
  58.      AND wm.[WeekNumber] = @weekNumber 
  59.      AND tt.[SiteId] = @siteId 
  60.      AND tt.[Status]  3        -- Remove cancelled bookings 
  61.      AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%' 
  62.   
  63.      ORDER BY Date ASC, Room ASC, 
  64.              tt.[StartTime] ASC, 
  65.              tt.[FinishTime] ASC 
I even separated off the @GivenDate and @GivenCampus and re-assigned them mid-script which made no difference whatsoever.

In case you've been stuck for ages (as I have), I found out you cannot declare the parameters used by the software within the SQL script... So the above should look more like this:
copyraw
-- ===============================================================================
-- Description:	Gets the room bookings for a week based on a given date and campus
-- ===============================================================================

DECLARE
	@setId varchar(10),
	@weekNumber int,
	@siteId nvarchar(5),
	@baseDate datetime,
	@specifiedDate datetime
	
	SET DATEFORMAT dmy

	-- Get columns SetId and WeekNumber from the weekstructure table based on the given date
	SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 6, @GivenDate))
	SET @weekNumber = (SELECT TOP 1 WeekNumber FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 1, @GivenDate))

        -- Get the SiteID based on the given campus and the setId (based on the given date)
	SET @siteId = (SELECT TOP 1 SiteId FROM SITES WHERE SiteName=@GivenCampus AND SetId=@setId)
		
	-- Get the start date for the week in question
	SET @baseDate = (SELECT TOP 1 StartDate FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 1, @GivenDate))
		
	SELECT
		@GivenDate AS 'GivenDate',
		dateadd(d, tt.[WeekDay] - 1, @baseDate) AS 'Date',
		datename(weekday, dateadd(d, tt.[WeekDay] - 1, @baseDate)) AS 'Day', 
		tt.[SiteId] AS 'Site',
		tt.[StartTime] AS 'Start', 
		tt.[FinishTime] AS 'Finish',
		tt.[RoomId] AS 'Room', 
		sd.[Descrip] AS 'Event Type', 
		sd.[Notes] AS 'Booking Notes',
		c.[Name] AS 'Contact', 
		c.[Company] AS 'Dept', 
		c.[Phone] AS 'Phone'
		
	FROM [CMISdata].[cmisuser].[SLOTDETAILS] sd
	INNER JOIN [CMISdata].[cmisuser].[TIMETABLE] tt 
		ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId]
	INNER JOIN [CMISdata].[cmisuser].[CONTACT] c 
		ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId]
	INNER JOIN [CMISdata].[cmisuser].[WEEKMAPNUMERIC] wm 
		ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId])
		
	WHERE sd.[SetId] = @setId
	--AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @SelectedDate
	AND wm.[WeekNumber] = @weekNumber
	AND tt.[SiteId] = @siteId
	AND tt.[Status]  3		-- Remove cancelled bookings
	AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%'
	
	ORDER BY Date ASC, Room ASC, 
			tt.[StartTime] ASC, 
			tt.[FinishTime] ASC
  1.  -- =============================================================================== 
  2.  -- Description:    Gets the room bookings for a week based on a given date and campus 
  3.  -- =============================================================================== 
  4.   
  5.  DECLARE 
  6.      @setId varchar(10), 
  7.      @weekNumber int, 
  8.      @siteId nvarchar(5), 
  9.      @baseDate datetime, 
  10.      @specifiedDate datetime 
  11.   
  12.      SET DATEFORMAT dmy 
  13.   
  14.      -- Get columns SetId and WeekNumber from the weekstructure table based on the given date 
  15.      SET @setId = (SELECT TOP 1 SetId FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 6, @GivenDate)) 
  16.      SET @weekNumber = (SELECT TOP 1 WeekNumber FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 1, @GivenDate)) 
  17.   
  18.          -- Get the SiteID based on the given campus and the setId (based on the given date) 
  19.      SET @siteId = (SELECT TOP 1 SiteId FROM SITES WHERE SiteName=@GivenCampus AND SetId=@setId) 
  20.   
  21.      -- Get the start date for the week in question 
  22.      SET @baseDate = (SELECT TOP 1 StartDate FROM WEEKSTRUCTURE WHERE StartDate BETWEEN DATEADD(day, -6, @GivenDate) AND DATEADD(day, 1, @GivenDate)) 
  23.   
  24.      SELECT 
  25.          @GivenDate AS 'GivenDate', 
  26.          dateadd(d, tt.[WeekDay] - 1, @baseDate) AS 'Date', 
  27.          datename(weekday, dateadd(d, tt.[WeekDay] - 1, @baseDate)) AS 'Day', 
  28.          tt.[SiteId] AS 'Site', 
  29.          tt.[StartTime] AS 'Start', 
  30.          tt.[FinishTime] AS 'Finish', 
  31.          tt.[RoomId] AS 'Room', 
  32.          sd.[Descrip] AS 'Event Type', 
  33.          sd.[Notes] AS 'Booking Notes', 
  34.          c.[Name] AS 'Contact', 
  35.          c.[Company] AS 'Dept', 
  36.          c.[Phone] AS 'Phone' 
  37.   
  38.      FROM [CMISdata].[cmisuser].[SLOTDETAILS] sd 
  39.      INNER JOIN [CMISdata].[cmisuser].[TIMETABLE] tt 
  40.          ON tt.[SlotId] = sd.[SlotId] and tt.[SetId] = sd.[SetId] 
  41.      INNER JOIN [CMISdata].[cmisuser].[CONTACT] c 
  42.          ON c.[ContactId] = sd.[Contact] and c.[SetId] = sd.[SetId] 
  43.      INNER JOIN [CMISdata].[cmisuser].[WEEKMAPNUMERIC] wm 
  44.          ON (wm.[SetId] = tt.[SetId] and wm.[WeekId] = tt.[WeekId]) 
  45.   
  46.      WHERE sd.[SetId] = @setId 
  47.      --AND dateadd(d, tt.[WeekDay] - 1, @baseDate) = @SelectedDate 
  48.      AND wm.[WeekNumber] = @weekNumber 
  49.      AND tt.[SiteId] = @siteId 
  50.      AND tt.[Status]  3        -- Remove cancelled bookings 
  51.      AND upper(sd.[Descrip]) not like 'ADDITIONAL TIMETABLE%' 
  52.   
  53.      ORDER BY Date ASC, Room ASC, 
  54.              tt.[StartTime] ASC, 
  55.              tt.[FinishTime] ASC 
Importantly, note the following:
  1. SET DATEFORMAT dmy returns the date in European format irrespective of the regional settings of either the client or the reporting server.
  2. Removed @GivenDate datetime, from the SQL script
  3. Removed @GivenCampus varchar(100) from the SQL script
Category: SQL Server Reporting Services :: Article: 315

© 2024 Joel Lipman .com. All Rights Reserved.