XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/

Using SQL Parameters

Creating SQL Parameters

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 clicking on the Parameters grid. The following screenshot shows a parameter named @pAccType created in the Parameters grid:

The Design Query form, showing the Parameters grid active.

These parameters can then set them from several different data sources, shown below.

Static Values

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

The Edit SQL Parameters form, showing static value 'Assets'

Values from an Excel range

To drive the Table or Slicer from an Excel range, click the select range button:

The Edit SQL Parameters form, with the Select Range button highlighted

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

The Edit SQL Parameters form, showing a range reference to D23

Values from a Slicer

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

Selecting an Existing Slicer

If a Slicer already exists in the workbook, you may select it directly in the Edit Parameters dialog:

The Edit SQL Parameters form, showing an existing Slicer

Creating a New Slicer

If no appropriate Slicer exists, you may create a new one.

  1. In the Edit Parameters dialog, select the <Add new slicer> option:
    The Edit SQL Parameters form, creating a new Slicer
  1. Select the published slicer query (or, for SQL Administrators, design your query):
The XLCubed Repository, showing available Slicer queries
  1. When you click OK , you will have the opportunity to select any parameters if applicable, and to select a location for the new Slicer.

Passing multiple values

Using the 'IN' clause

As of Version 7.6 you can pass multiple values to a parameter. To do this simply tick 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

Using stored procedure parameters

As of Version 9.1 you can pass a table variable to a stored proc 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 following 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 if this type. The parameters will be text, if you need numbers you can convert in the stored procedure.

Example of a query using a table parameter

The stored procedure might look something like this:

	-- Add the parameters for the stored procedure here
	@theIDs as dbo.XL3ParameterTable readonly
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.

    -- Insert statements for procedure here
	SELECT * from [dbo].[MyTable] a
	join @theIDs b
	on a.KeyID = b.ParameterValue

See Also