Jump to: navigation, search
  • Main page
  • Recent changes
  • Random page
  • 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 paramaters

    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

    See Also