Passing SSRS parameter into Query as parameter

0
votes

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

2
sql-serverreporting-servicesssrs-2008ssrs-2008-r2ssrs-tablix
Define "it is not working". - Larnu
Query executed successfully but the data from backend not coming means because of single corts it is not working - Arpit Sharma
What is a "single cort"? If the query executed successfully why is it "not working",are you not getting the results you are after? If you aren't, why aren't they correct? We can't see what you see, or read your mind, so you need to explain (in detail) what the problem is. - Larnu
not working means the query is not giving me desired output, it is giving me the output that is shown in my screenshot. As per my understanding I think the problem is coming in this situation is that the parameter value is coming with corts, is there anyway by which I can remove them - Arpit Sharma
Are you trying to say "quotes"? - Steve-o169

2 Answers

1
votes

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)
0
votes

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.