Parameterizing SQL Server Connections

Query Parameters allow you to make the content of a report dependent on one or more parameter values.

If we parameterize a data source, then we can update all our queries using that data source simply by specifying a different parameter value.

In this demonstration, we will parameterize the name of the SQL Server instance which is the source of all the queries in a report.

To begin, we click “Edit Queries” to open the Query Editor.

In the Query Editor, with any of the queries selected, we click on the cog icon next to the Source step in the Queries pane.

The value displayed in the “Server” field is the value that we will be parameterizing.

These are the names of the two server instances that we will be using as values for our parameter.

The first step is to create a parameter: click “Home > Manage Parameters > New Parameter”.

Enter “ServerName” as the parameter name and the brief description shown below.

We set the data type to “Text” and choose “List of values” for “Suggested Values”.

Now, we copy the names of the two servers from NotePad.

And we paste them in as the “List of values”.

Finally, we set the development server as the “Current Value” and click “OK”.

To implement our parameter, we highlight each query and click on the cog icon next to the Source step in the Queries pane.

In the “SQL Server” dialog which pops up, we choose “Parameter” from the dropdown menu on the left of the “Server” field.

Since “ServerName” is the only available parameter, it is automatically selected; so, we can just click “OK”.

We then simply repeat this process for each of the other queries.

To finish, we click “Close and Apply”.

With this mechanism in place, we can now change the server instance to which we are connected at any time. To do this, click “Edit Queries > Edit Parameters”.

Choose the required server from the “ServerName” dropdown we defined when creating the parameter and click “OK”.

To apply the changes made to our queries, click the “Apply Changes” button.

(The other method of changing the parameter value would be to go into the Query Editor, highlight the parameter and choose a new “Current Value” from the dropdown.)