CODEWITHSUNDEEP
sql-serverreporting-servicesssrs-2008ssrs-2008-r2ssrs-tablix
Arpit Sharma
Arpit Sharma

Reputation: 138

Passing SSRS parameter into Query as parameter

Recently there is one situation in SSRS , Where I am not able to figure it out correct logic for my requirement ,can someone please help me out what is the problem in my logic.

I am trying to pass the parameter value into SSRS dataset query. Please refer the query below. When I am trying to execute the query manually it runs but when I pass parameter in it, not providing me the desired output please refer the screenshot where I executed the query in SSMS.

DataSet Query

Select * from(
SELECT     @Defaulter_Type AS Priority_Count, Priority
FROM         Quality_Portal_Data_Fact_Graph)  temp

SQL Query

SELECT [NEW_PID_2],[Priority_1] 
  FROM [OneWindowPRD].[dbo].[Quality_Portal_Data_Fact_Graph]

Although, I figure it out one thing that when I am trying to pass parameter in it. It is passing the value with single cort because of which it is showing data is below mentioned form.

output of the query when I pass parameter

Can someone please suggest What will I do to remove these single cort. Please suggest any other method or logic to select the column of the query which is given by user

I already tried SUBSTRING its not working. Please refer the screenshot

SELECT QUOTENAME(SUBSTRING(@Defaulter_Type, 2, LEN(@Defaulter_Type) - 2)) AS Count_Priority FROM Quality_Portal_Data_Fact_Graph

enter image description here

enter image description here

enter image description here

The parameter I am trying to pass in the query is the column_name which is passed by user, it is single value column. Please refer screenshot is someone have any suggestion or other approach

Upvotes: 0

Views: 12913

Answers (2)

craig.white
craig.white

Reputation: 427

If you need the value of a parameter in a dataset but it is not being used as a parameter in a Where clause, then you don't need it in your SQL dataset, simply add it as an additional column on your SSRS report. If, however, you need it as a parameter, you can address it as @Defaulter_Type in your WHERE clause. [New addition for clarity) @arpit-sharma I'm afraid I'm unclear on what you are trying to achieve. Can you try something basic like this in the back end (SSMS):

Declare @Pid2 VarChar(10)
Set @NewPid = '0'

SELECT
    [Priority_1]
    ,[New_PID_2]
    ,[Project_Ending_3]
    ,[PSU_Status_4]
    ,[Project_Profile_5]
    ,[eUPP_Status_6]
    ,[iPMS_Setup_7]
    ,[PM_EO_8]
FROM [OneWindowPRD].[dbo].[Quality_Portal_Data_Fact_Graph]
WHERE [New_PID_2] = @NewPid

The PID column looks like what you are trying to filter on, and if so, the SQL above should do it, provided a valid value in the variable.

Upvotes: 0

Chris Mack
Chris Mack

Reputation: 5208

Are you trying to do something like:

DECLARE @sql varchar(1000) =
'
Select * from(
SELECT     ' + QUOTENAME(@Defaulter_Type) + ' AS Priority_Count, Priority
FROM         Quality_Portal_Data_Fact_Graph)  temp
'

EXEC (@sql)

This uses dynamic SQL to build a query string using your parameter as a column name. It then executes that query to produce your result set.

Your query as it currently is will just select the value of @Defaulter_Type as the value of the field Priority_Count for every row.

Additionally, if your parameter is encased in quotes that you want to remove, use:

DECLARE @sql varchar(1000) =
'
Select * from(
SELECT     ' + QUOTENAME(SUBSTRING(@Defaulter_Type, 2, LEN(@Defaulter_Type) - 2)) + ' AS Priority_Count, Priority
FROM         Quality_Portal_Data_Fact_Graph)  temp
'

EXEC (@sql)

Upvotes: 1

Related Questions