SELECT PRODUCT, ENGINEER, STATUS, RELEASE_DATE, STOCK, PRICE
FROM jos_company_products
WHERE PRODUCT='{PROD_NUM}'
ORDER BY ID DESC LIMIT 0,{ROWLIMIT}

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.

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})

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)

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.

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.

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.