When editing the SQL query, the SQL administrator can create parameters that can later be edited by Standard SQL Users.

To create the parameter, type the SQL query as usual, but substitute @paramName to create a parameter named paramName. The parameter can then be edited by selecting it on the Parameters grid. 

Parameter named @pAccType created in the Parameters grid

You can set these parameters from several different data sources.

To use a single static value in an SQL report, the value can be typed in directly.

To drive the table or slicer from an Excel range, select the range button and then the range.

Select a range in the dialog that appears, and the range reference will appear in the parameters grid.

You can either select an existing slicer or create a new one.

If a Slicer already exists in the workbook, you may select it directly from the dropdown menu in Edit Parameters.

To create a new slicer:

  1. In Edit Parameters, select Add new slicer.
  2. Select the published slicer query (or, for SQL Administrators, design your query).
  3. When you select OK, you can select any parameters if applicable, and to select a location for the new slicer.

You can pass multiple values to a parameter. To do this, ‌select the Multi-Value checkbox and select either an Excel range, or a multi-select slicer.

You must only pass one parameter to the IN clause, which will be expanded when the query is run.

Example of a query using the IN clause

You can pass a table variable to a stored procedure to send multiple parameters at once. To do this, set the variable mutivalue type to Table Parameter in the query designer.

This feature relies on the below type being available in the database. This script may need to be run by your DBA.

create type dbo.XL3ParameterTable as Table( ParameterValue nvarchar(100) );

The stored procedure then takes a parameter of this type. The parameters will be text. If you need numbers you can convert them in the stored procedure.

The stored procedure might look something like this:

CREATE PROCEDURE [dbo].[myProc] -- Add the parameters for the stored procedure here @theIDs as dbo.XL3ParameterTable readonly AS BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Insert statements for procedure here SELECT * from [dbo].[MyTable] a join @theIDs b on a.KeyID = b.ParameterValue END