May 9, 2017 at 2:37 pm
I am using "Microsoft SQL Server Report Builder 3.0" on "SQL Server Reporting Services 2012".
From a main report main.rdl I am calling a drillthrough report detail.rdl
The main.rdl report has a table and each text box of this table is clickable and drillthrough action. When user clicks on any text box the detail.rdl report is called. One parameter that is passed to detail.rdl report is used as a column name in the SQL. This SQL fetches data needed for detail.rdl report
When I run the SQL query for detail.rdl report that is built using parameters, it is not showing any error but doesn't fetch any data either. I think what is happening is that as the parameter has data type Text, it's replaced in SQL with quotes which might be causing it not to fetch any data.
When I run the same SQL directly on DB after replacing the column name with parameter value (without quotes) it does fetch data.
Is there any specific way to pass parameters that will be used as column names that I am missing here?
May 10, 2017 at 12:21 pm
Is there a way to check the final SQL query that war run against the database from Report Builder 3.0? I am almost sure that quotes around the parameter is causing this issue as column name should not have quotes.
May 10, 2017 at 2:00 pm
SQL that I am trying from report builder:
SELECT
table_name.column1
,table_name.column21
,table_name.column15
,table_name.column6
,table_name.column9
,table_name.column2
,table_name.column19
,@column /*the value in this variable is an existing column name from table table_name*/
FROM
table_name
WHERE
table_name.column1 LIKE @var_1
AND table_name.column21 LIKE @var_2
AND (@column is null OR @column = '' OR @column = 'N/A' OR @column = 'Unknown');/*I want to use same variable here as well*/
I got a SQL solution but unfortunately "Microsoft SQL Server Report Builder 3.0" is not able to execute this SQL as it asks for value of variable @SQL even though it's value is defined.
DECLARE @columnName sysname = @column
DECLARE @SQL varchar(max) = 'DECLARE @company_name varchar(10) = @company
DECLARE @platform_name varchar(10) = @platform
SELECT
table_name.column1
,table_name.column21
,table_name.column15
,table_name.column6
,table_name.column9
,table_name.column2
,table_name.column19
,' + @columnName +
' FROM
table_name
WHERE
table_name.column1 LIKE ' + '@company_name' + '
AND table_name.column1 LIKE ' + '@platform_name' + '
AND (' + @columnName + ' is null OR ' + @columnName + ' = '''' OR ' + @columnName + ' = ''N/A'' OR ' + @columnName + ' = ''Unknown'')'
exec (@sql)
I did try declaring and setting the value of @SQL in same line as:DECLARE @SQL varchar(max) = 'DECLARE @company_name varchar(10) = @company
But report builder still asks for value of @SQL and report doesn't run. Is there any way to get this done?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply