vred
asked on
Dynamic query in SSRS for Dynamic Reports
Hi all,
I have a scenario to create a Dynamic report using a query nor a stored proc due to performance issues. I am half way done through it. I am ready with the Dynamic query but i am not able to figure out how can create a report in SSRS using this query. I tried copy pasting my dynamic query in the Text part of the wizard but i dont think thats the right way to do... I am also attaching my dynamic Query here. Please help...
Use AdventureWorks
DECLARE @SQLString nvarchar(4000),
@GroupByString nvarchar(4000),
@OrderByString nvarchar(4000),
@ParamList nvarchar(4000),
@EmployeeID1 varchar(20),
@ContactID1 varchar(20),
@EmployeeID int,
@BEGIN varchar(12),
@END varchar(12),
@ContactID int,
@COLUMN_LIST varchar(4000),
@Order_By varchar(4000)
--select @EmployeeID = 100963,
-- @BEGIN ='07/31/1996',
-- @END ='07/01/2003',
-- @ContactID = null,
-- @COLUMN_LIST = 'EmployeeID,Title,Gender,V acationHou rs,BirthDa te',
-- @Order_By = 'BirthDate,Gender'
set @SQLString = 'Select top 10 '
set @GroupByString = ' Group By '
set @OrderByString = ' Order By '
set @EmployeeID1 = convert(varchar, @EmployeeID)
set @ContactID1 = convert(varchar, @ContactID)
set @SQLString = @SQLString + @COLUMN_LIST
set @SQLString = @SQLString + ' from Humanresources.Employee '
set @SQLString = @SQLString + ' where 1 = 1'
if (@BEGIN is not null and @END is not null)
set @SQLString = @SQLString + ' and HireDate BETWEEN ''' + convert(nvarchar(20), @Begin) + ''' AND ''' + convert(nvarchar(20), @End) + ''''
if (@ContactID is not null)
set @SQLString = @SQLString + ' and ContactID = ' + @ContactID1
if (@Order_By is not null)
set @SQLString = @SQLString + @OrderByString + @Order_By
--print @SQLString
exec(@SqlString)
I have a scenario to create a Dynamic report using a query nor a stored proc due to performance issues. I am half way done through it. I am ready with the Dynamic query but i am not able to figure out how can create a report in SSRS using this query. I tried copy pasting my dynamic query in the Text part of the wizard but i dont think thats the right way to do... I am also attaching my dynamic Query here. Please help...
Use AdventureWorks
DECLARE @SQLString nvarchar(4000),
@GroupByString nvarchar(4000),
@OrderByString nvarchar(4000),
@ParamList nvarchar(4000),
@EmployeeID1 varchar(20),
@ContactID1 varchar(20),
@EmployeeID int,
@BEGIN varchar(12),
@END varchar(12),
@ContactID int,
@COLUMN_LIST varchar(4000),
@Order_By varchar(4000)
--select @EmployeeID = 100963,
-- @BEGIN ='07/31/1996',
-- @END ='07/01/2003',
-- @ContactID = null,
-- @COLUMN_LIST = 'EmployeeID,Title,Gender,V
-- @Order_By = 'BirthDate,Gender'
set @SQLString = 'Select top 10 '
set @GroupByString = ' Group By '
set @OrderByString = ' Order By '
set @EmployeeID1 = convert(varchar, @EmployeeID)
set @ContactID1 = convert(varchar, @ContactID)
set @SQLString = @SQLString + @COLUMN_LIST
set @SQLString = @SQLString + ' from Humanresources.Employee '
set @SQLString = @SQLString + ' where 1 = 1'
if (@BEGIN is not null and @END is not null)
set @SQLString = @SQLString + ' and HireDate BETWEEN ''' + convert(nvarchar(20), @Begin) + ''' AND ''' + convert(nvarchar(20), @End) + ''''
if (@ContactID is not null)
set @SQLString = @SQLString + ' and ContactID = ' + @ContactID1
if (@Order_By is not null)
set @SQLString = @SQLString + @OrderByString + @Order_By
--print @SQLString
exec(@SqlString)
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
are you going to have a constant number of columns returned?