| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Using SQL Parameters
Contents
[hide]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:
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:
Values from an Excel range
To drive the Table or Slicer from an Excel range, click the select range button:
Select a range in the dialog that appears, and the range reference will appear in the parameters grid:
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:
Creating a New Slicer
If no appropriate Slicer exists, you may create a new one.
- In the Edit Parameters dialog, select the <Add new slicer> option:
- Select the published slicer query (or, for SQL Administrators, design your query):
- 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.
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.
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
