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,VacationHours,BirthDate',
--                        @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)


vredAsked:
Who is Participating?
 
vredConnect With a Mentor Author Commented:
NO solution Found
0
 
planoczConnect With a Mentor Commented:
Are you using 2005 or 2008?
0
 
EmesCommented:
Are you using a table or Matrix?

are you going to have a constant number of columns returned?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.