Parameterizing a SQL Query Source

I have a parameter object called SQLParameters that has a parameter called ImplementationDate that has a default value. I also have a SQL query source called SQLQuerySource1 in which I'm trying to use the parameter. The statement I've entered is:

select * from userfile where user_end_date < $(SQLParameters.ImplementationDate)

I get a message saying the layout cannot be built because the query contains parameters, which is fine - although it would be nice to be able to provide a default value for the parameter if it can't read the default value of the parameter, but when the query source properties are closed there are no columns and there is no where to link the parameter in. The lack of columns I would associate with not generating the layout, but it seems like the root problem is it isn't getting a value for the parameter.

How should this be implemented and what should I expect to see in the UI? I've looked at the documentation, but it's a bit light in details and examples.

thanks

Andrew Ellsom October 18, 2012 20:37

I played with this a bit more and discovered the following:

1. Removing the criteria with the parameter would allow the layout to be generated so I had columns in the SQL Query Source. Once there they stayed.

2. The parameter format was correct and was used when using Preview Data on the SQL Query Source (I had to add quotes for the date, but it did read the parameter value from the parameter object).

3. There is no input to the query source to associate the parameter with the query source object.

In this simple query the removal of the parameter wasn't too much of an issue, but I can see it being a problem in more complex queries, so is this the normal way to implement a parameterised query?

Parameterizing a SQL Query Source

1 comment