|
SQL 2 Excel Pro allows you to specify parameters for a Workbook. These parameters can then be used in your SQL queries where the values will be substituted at runtime. You can set the default values for the parameters in the Workbook configuration. Parameter visibility can be set for each individual parameter. If you make a parameter hidden then the default value will be used. You can show all your parameters, some of them or none of them to the users - it's all up to you.
The parameters are defined for each Workbook in SQL 2 Excel and the configuration looks like this:
These variables can then be used in the SQL queries, like this:
SELECT PRODUCT, ENGINEER, STATUS, RELEASE_DATE, STOCK, PRICE
FROM jos_company_products
WHERE PRODUCT='{PROD_NUM}'
ORDER BY ID DESC LIMIT 0,{ROWLIMIT}
In this case we define that we want to prompt the user for values. A popup is displayed and the user can select the parameter values when he/she clicks the download link.
If you set the Module to direct the browser to a Preview, then the parameters are shown on the Preview page and the user can play around with the values until the desired results are returned before downloading the spreadsheet.
The data type of the parameters can be set and extensive error checking and filtering will be done to ensure the input data is ok. The final query is also checked for potential dangerous syntax before execution as an extra layer of security ontop of this (standard SQL 2 Excel feature).
The values can even be used to evaluate subsequent Workbook Parameters. See Sequential Evaluation below.
List of Values
You can specify all of some of the parameters as a (static) List of values. Each row should contain two values delimited with a comma. The first value is used as the Value, the second is what the user see's in the drop down list.
See Parameter #2 in the example below.
List of Values - Multi Select - Numeric / String
Similar to "List of Values", but this parameter type allows the user to make multiple selections.
The selected values will be concatenated with a comma separator.
The difference between Numeric and String is that the String values gets enclosed with ticks - which is required for String fields in the SQL query.
Numeric:
1,5,7
String:
'Hello','World','String3'
Example SQL queries which then make use of the selection(s) :
SELECT ID, TITLE
FROM jos_content
WHERE catid IN ({CATEGORIES})
SELECT ID, TITLE
FROM jos_content
WHERE TITLE IN ({MYTITLES})
List of Values - SQL
You can write a SQL query to dynamically build a list of values for the user to choose when the workbook is downloaded. You should write the query so that two values are returned. The first value will be used as the Value when the user selects the choice. The second value is what the user see in the drop down list. See Parameter #1 in the example above. The ID value will be assigned to the substitution variable {MYUSERID} when you select a user in the list. The name if what you see in the list, but the ID value is what gets assigned to the variable and what you use in your query.
You can select the same field twice if you want to have the value/displayed value to be the same, for example:
SELECT ID, ID AS `Label`
FROM jos_users
Please note that the names of the fields has to be unique. You have to rename the fieldname using the " AS " clause as in the example above if you want to use the same field twice.
You can choose which database to run the query on for each SQL based Workbook Parameter. This allows you to do elaborate reports spanning multiple databases and present parameters from the different sources for the users. (New in 1.8.0)
List of Values - SQL - Multi Select - Numeric/String
Multi-select parameters dynamically populated with a SQL query. See the Multi-Select List of Values above for more info on how to use Multi-Select Workbook Parameters.
Sequential Evaluation
The Workbook Parameters are evaluated sequentially. You can use the value of a previous parameter in the next parameter.
Example:
Parameter #1
Variable Name: {myid}
SQL:
SELECT cb_myid
FROM com_comprofiler
WHERE user_id={USER_ID}
Database: Joomla
Parameter #2
Variable Name: {myentries}
SQL:
SELECT *
FROM my_entries
WHERE my_entry_id = {myid}
Database : my_feedback_db
The first parameter will look up a custom user variable from Community Builder, based on which user who is logged in. The value the first query returns gets assigned to the custom WB {myid} parameter.
The second parameter is then evaluated. The SQL query for the second parameter is using the {myid} variable. This will be substituted with the value from the first parameter.
This allows you to create sophisticated lookups based on multiple tables and even across different databases.
Please note: The sequential evaluation does not get dynamically evaulated when you prompt a user for the parameter value. Only the initial values will be evaulated using the sequential method.
Workbook Parameters in Preview
The workbook parameters do show up in the Preview mode ontop of the report. The user can tweak the settings and refresh the preview to see the results of the different parameter value settings before downloading the spreadsheet.
 |
Subscribe to this comment's feed