Query parameters not recognized by report designer

No matter how I try to enter an automatic query parameter it just isn't recognized as such. I get missing expression errors from the SQL syntax check and no report variables are generated. There must be something incredibly simple that I am missing....

The most recent query string I've entered is

SELECT DISTINCT WRTE_ROUTE FROM WSMGR.WIPRTE WHERE WRTE_RT_GRP_1 = 'RULE_BASED' AND WRTE_FACILITY = @.Facility

Thanks for anyone who can help..

I am not sure whether this will help but one thing that works is to create the query as a stored procedure in your database and use the Stored Proceudure option rather than Query Text. You simply type in the stored procedure name (no brackets or parameters) and hit the Execute Query (!) button. Reporting Services then creates the required parameters for you and prompts for their values. You have to run the stored procedure at least once in the dataset tab so that you have some columns to choose when you go to the layout tab. Reporting Services doesn't know what columns are returned until you run the query.

You miss out on the Generic Query Designer in Reporting Services but you don't have to remember or type in the parameter names. It's fast becoming my preferred method.

Dick Campbell

|||

I neglected to mention that we were using an Oracle database because I didn't see why it would make any difference. But coworkers now tell me that the automatic query parameter recognition is a SQL Server specific feature. I don't see why this should be, and if that is the case, it certainly should be documented as such.

For Oracle users, the only solution appears to be to enter the SQL query as an expression like...

="SELECT DISTINCT WRTE_ROUTE FROM WSMGR.WIPRTE WHERE WRTE_RT_GRP_1 = 'RULE_BASED' AND WRTE_FACILITY = '" & parameters.facility.value & "'"