SQL Keys

This all seems very well, but what happens if you want to create a custom report that needs to request specific information from the user before running - eg: To filter between two dates, or only show animals of a particular species, etc?

ASM offers SQL preprocessor instructions to allow you to substitute system information, or information requested from the user at report generation time.

• $USER$ - Substitutes the current user's name. This is useful with CREATE TEMPORARY TABLE instructions to ensure that users get their own versions of temporary tables when generating reports. It could also be used to filter reports to only show users records they created, etc.
• $CURRENT_DATE$ - Substitutes today's date in an appropriate string SQL format for the current database. This saves you having to use NOW() or CURRENT_DATE() or various other SQL functions specific to one database.
• $PARENTKEY$ - Substitutes the field value named in the report calling this subreport. For example, a report could pass an Animal ID as the parent key to a subreport for filtering data correctly. When you use this key in your report SQL, ASM assumes that this report is a subreport and will not display it on the menu.