dynamic query in Reporting Services

  • Hello:

    Does anyone have experience about implementing the “dynamic query” function in “Reporting Service”? I am studying the packet through the Reporting Services online book tutorial. The edition of the Reporting Services is evaluation edition. I totally follow these steps and try to implement the dynamic query. However, I cannot success in the following step:

    In Data view, select the Employees dataset, and then use the generic query designer to replace the original query with the following expression:

    ="SELECT FirstName, LastName, Title FROM Employee" & IIf(Parameters!Department.Value = 0,""," WHERE (DepartmentID =   " & Parameters!Department.Value & ")") & " ORDER BY LastName"


    I put the whole expression in one line. But I cannot run the express if I keep the ‘=’ symbol. If I delete the ‘=’, an error message will occur while running. Any suggestion? Thank you for your kind answer.

  • You cannot RUN this kind of query in Reporting Services.  You will need to PREVIEW the report to see the data.

    What I have done is, build the query with the parameters populated to design the report and then replace the parameters as needed then preview the report to check for the desired results.

    Hope that helps.

    Steve Hughes, Magenic Technologies

  • Kenneth,

    I would recommend using a stored procedure instead of your dynamic query syntax.  I am assuming that you want to bring back all departments if the parameter is 0 and only the department that is selected otherwise.  Use the following stored procedure as your dataset and it will do the same thing you are trying to do with dynamic query.  Here is what the stored proc would look like:

    Create Proc spGetEmployeeByDepartment

    @DeptID int


    if @DeptID = 0


    SELECT FirstName, LastName, Title FROM Employee ORDER BY LastName




    SELECT FirstName, LastName, Title FROM Employee WHERE DepartmentID = @DeptID  ORDER BY LastName


    Michael Finley

    Consultant,  MCSD

    Empowered Software Solutions

    200 S. Frontage Rd.  Ste. 300

    Burr Ridge, IL  60527

    630-654-8907 x234 W

    630-915-6420 M


    Mike Finley

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply