Report Parameters in a Basic SQL Query
Queries can also be made dynamic using things like report parameters. To insert a report parameter, simply click the Parameter Reference button button ( ) to the right of the query area and select your parameter. This allows for a more dynamic query, since new values can be passed into the parameter at runtime, giving the ability to change the type of machine this query is looking for.
Parameters are inserted directly into a report. This means that the datatype of parameter will affect how it should be referenced in the query. For example, since the parameter MachineType is a string, it will need a single or double quotes around it.
Since an integer does not need quotes around it, if your parameter is a Long, Double, or a Boolean, you can directly place the parameter in your query, without the quotes.
Working with Dates
If your parameter is a date object, then special consideration must be made.
The query will not accept a date object directly, it must first be converted to a string by putting quotes around it. However, database generally prefer datetime objects in very specific formats, such as yyyy-MM-dd HH:mm:ss. This means we need to reformat the date on any report parameters we want to pass to the Basic SQL Query. There are two main approaches to this:
Reformat the Date Parameters
By utilizing the expression language's dateFormat() function, we can simply specify the format of the date.
Create New Formatted Parameters
In some cases, you may wish to leave the original "raw" Date parameter alone, and create a display-friendly version as a string.
To do this, simply make a parameter with type string and use the dateFormat() expression on a date. In the image below, you can see that the StartDate parameter is used in a new StartString parameter. Additionally, an EndString parameter has been created that is using the EndDate parameter. This way, we can bind a calendar component directly to the Start and EndDate parameters and all the formatting will be done automatically in the report.
This format was used with a MySQL database, so your database may take a different format. Refer to your database's documentation for suggested date formats.
Once the new string parameters have been created, we can then reference them in the Basic SQL Query just like a normal string.