|
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}
Results in a header, like this:
SQL Query Example
The query can be made dynamic by putting substitution variables in it. This query returns a list of articles that the current user has created. The variable {USER_ID} is substituted with the current users ID at the time of execution.
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.
 |
Subscribe to this comment's feed
