February 1, 2004 at 6:15 am
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.
February 3, 2004 at 9:30 am
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
February 3, 2004 at 10:30 am
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
as
if @DeptID = 0
BEGIN
SELECT FirstName, LastName, Title FROM Employee ORDER BY LastName
END
ELSE
BEGIN
SELECT FirstName, LastName, Title FROM Employee WHERE DepartmentID = @DeptID ORDER BY LastName
END
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
www.SQLScriptSafe.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply