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:
-- ===============================================================================
-- 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
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:
-- ===============================================================================
-- 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
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

Related Articles

Joes Revolver Map

Accreditation

Badge - Certified Zoho Creator Associate
Badge - Certified Zoho Creator Associate

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 - Valid till 8 May 2022 3QnhmaBX7LQSRsC9hh6Je9rGQKEGNQNfPb
© 2021 Joel Lipman .com. All Rights Reserved.