You can use substitution variables in your Header / Footer and you SQL query to create dynamic reports. For instance, you can select things from your database depending on who is currently logged in. There is a long list of variables that you can use. Please see the SQL 2 Excel - Variable Substitution - Reference for details.

Header Example

Let's say we want to add a date to the header so that you can tell when the report was generated. This header specification will dynamically fill in the date/time when it gets executed:

Latest Articles as of {M} {j} {Y} - {H}:{i}:{s}

SQL Query Example

SELECT ID, TITLE
FROM jos_content
WHERE created_by={USER_ID}

Please note that if this report is executed by a user who is not logged in then the value of {USER_ID} is 0 => no results are returned.

Turning Off Variable Substitution

The variable substitution is on by default. You may in some cases want to turn off if the variables clashes with actual data/strings that you wish to output. You can turn off variable substitution by going to the SQL 2 Excel Component in the Administrator. Click on the Parameters button in the SQL 2 Excel Toolbar.

This brings up a small dialog where you can turn off variabale substitution in the Header/Footer and in the SQL Query.

Comments (5)

Subscribe to this comment's feed

wtfbrb said:

	... For anyone out there struggling with substituting a string it is best to put the quotes in the query: Select memberID,Address,City,State,Zip from my_members where state='{state}' You can also add some wild cards as well in the example below to search for part of a title: SELECT ID, TITLE FROM jos_content WHERE TITLE='%{SEARCH_TITLES}%'
	report abuse vote down vote up

July 30, 2009
Votes: +0

wtfbrb said:

	... Another thing that throws an error on your worksheet but I find very handy is for the multi select you can use the following: SELECT * FROM 201007_awa WHERE b_state IN ({s_state})
	report abuse vote down vote up

August 03, 2010
Votes: +0

Mats @ Jommla-R-Us said:

	... @wtfbrb: The reason it throws an error in the syntax check is because the value of the Workbook Parameter substitution variable is not known within the context of a Worksheet. It could be a number, a string or a list of values etc etc. A Worksheet could possible be used in multiple Workbooks and what value the Workbook parameter should have is not assigned until you download a Workbook.
	report abuse vote down vote up

August 03, 2010
Votes: +0

John McCall said:

	... Is there any way to substitute the results of a query in a header. I report for an event and define a substitution variable {EV_ID} to use in the query. How can I get the event name in the header? It would be defined by something like SELECT event_name FROM jos_events WHERE event_id = '{EV_ID}' If this can't be done, might it be added?
	report abuse vote down vote up

November 23, 2010
Votes: +0

John McCall said:

	... Please ignore the above - I figured it out NICE Product!!!
	report abuse vote down vote up

November 23, 2010
Votes: +0