CODEWITHSUNDEEP
sql-serverreporting-servicesparameter-passingsql-server-profiler
Betty Mcbetbet
Betty Mcbetbet

Reputation: 1

SSRS report parameters not being passed through, work fine when hard coded

I am using Sql server 2017 and visual studio 2019.

I created a report in SSRS and initially used a stored procedure with a single parameter @Date in it and used the data explorer section to set up the required data sources etc and when challenged for the parameter I put in a date but the report returns blank. I can run the same sp in SSMS fine. I can hard code the date either directly in the WHERE clause or in declaration of the variable and it also then runs fine. I tried a number of things around date formats but I don't need to include the detail of that because I started over and wrote a simple query

SELECT id, date FROM [production].[dbo].[products] WHERE id = 10

This runs fine and the report in which I have placed a table containing just the two fields is populated with the correct data.

I replace the WHERE id = 10 with

WHERE id = @productid

...and declare the variable. I run the report and put in the id 10 and the report returns blank.

So, the issue with the initial stored procedure and date is just a more complicated version of this simple example, but in both cases the report parameter does not seem to be being passed through. I have summised as much by using Sql profiler. Looking at where I would expect the parameter to be passed in it instead says N'%'. Now I am not entirely sure what that means but on using the profiler when hard coding the id in, I can see that the id parameter is passed as N'10'.

I am not sure where I am going wrong. I have dumbed it down to a simple query. It contains a parameter. The parameter is declared and can be inspected in data explorer. It is challenged for and then provided but does not seem to get any further. Yet hard coding the same id in either via the WHERE clause or via the parameter declaration works fine.

Am I missing something?

I created a simple sp to illustrated my issue using the simple example:

USE [Production]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[usp_getproduct]
@productid INT = NULL
AS
BEGIN
SET NOCOUNT ON;

SELECT id, date
FROM Production.dbo.Product
WHERE id = @productid

END

This is tabbed properly by the way, it is just not showing here. If I use this sp within an ssrs report and run it I get nothing. If I amend the line @productid INT = NULL to be @productid INT = 10 then the report runs fine. I can't see how to post photos of the parameter setup in SSRS but when I add the sp, refresh fields, check the parameter section it is there and is in as [@productid]...I tried it without square brackets but that made no difference

Upvotes: 0

Views: 3157

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21703

I am assuming the quotes around your code are not actually in your dataset query, if they are then remove them.

Also, in a dataset query, you should not declare the parameter that you are passing in from SSRS, sql 'scripts' as your datasource behave differently then calling a stored proc.

So you dataset query should just be

SELECT id, date FROM [production].[dbo].[products] WHERE id = @productid

No quotes, no declaration or @productid.

If your productid parameter allows multiple values then change the where clause like this

SELECT id, date FROM [production].[dbo].[products] WHERE id IN(@productid)

Upvotes: 0

Related Questions