Parameters not being used in report processing

Category: SQL Server Reporting Services
Hits: 24015
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

Add comment

Your rating:

Submit

Credit where Credit is Due:


Feel free to copy, redistribute and share this information. All that we ask is that you attribute credit and possibly even a link back to this website as it really helps in our search engine rankings.

Disclaimer: Please note that the information provided on this website is intended for informational purposes only and does not represent a warranty. The opinions expressed are those of the author only. We recommend testing any solutions in a development environment before implementing them in production. The articles are based on our good faith efforts and were current at the time of writing, reflecting our practical experience in a commercial setting.

Thank you for visiting and, as always, we hope this website was of some use to you!

Kind Regards,

Joel Lipman
www.joellipman.com

Latest Articles

Accreditation

Badge - Zoho Creator Certified Developer Associate
Badge - Zoho Deluge Certified Developer
Badge - Certified Zoho CRM Developer

Donate & Support

If you like my content, and would like to support this sharing site, feel free to donate using a method below:

Paypal:
Donate to Joel Lipman via PayPal

Bitcoin:
Donate to Joel Lipman with Bitcoin bc1qf6elrdxc968h0k673l2djc9wrpazhqtxw8qqp4

Ethereum:
Donate to Joel Lipman with Ethereum 0xb038962F3809b425D661EF5D22294Cf45E02FebF

Joes Word Cloud

Please publish modules in offcanvas position.