To use parameters on creation of a data source, you need to have already created a parameter prior to selecting one.
1. Load the query editor.
2. Click Manage Parameters.
3. Create two new parameters for the SQL Server and Database and add the default values in and click OK.
4. Verify you can see the parameters in the left hand queries pane.
Creating a Parameter – On Getting Data
If your query already contains parameters, you can select or create additional parameters through the below:
2. Select your data source (I used SQL Server for this example). You’ll notice that there is a couple of new drop down boxes…
3. You can select the “New Parameter” option to create (step 3 from previous section) or select “Parameter” and specify an existing one.
When done, you can follow the rest of the wizard and connect to your data source. So what about updating existing queries…?
Updating Existing Queries
In my Part 1 I talked about using a dynamic XML table as a source for my parameters. To update an existing query to use the new parameter method, we need to edit the existing reference variable below.
In the above, you can see we have our variables which currently point to my XML table. To change this to the new parameters all we need to do is the below:
This tells the query that the SCCMServer and SCCMDatabase variables are the relevant parameters and the source then uses these to connect.
If you don’t have a preset variable specified like mine, to amend an existing query, all you need to do it add variables first
Let
<VariableName0> = #”<Parameter Name0>”,
<VariableName1> = #”<Parameter Name1>”,
Source = sql.database(VariableName0, VariableName1, [Query=”
With the SQL parameters now set and tested, all I needed to do was then go through the rest of the queries and do the above then all good again!