| XLCubed is now FluenceXL. The new wiki can be found here: https://help.fluencexl.com/ |
Difference between revisions of "Using SQL Parameters"
| Line 35: | Line 35: | ||
# When you click {{Menu|OK}}, you will have the opportunity to select any parameters if applicable, and to select a location for the new Slicer. | # When you click {{Menu|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) | + | ==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. | 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. | ||
Revision as of 15:38, 14 November 2013
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.
