Add support for at-runtime tokenized parameters to Custom Query reports

[ldennison-bsd]

I would like Sprout Reports to be able to read Custom Queries for tokenized values and automatically provide UI elements to control those values from the report interface similar to how "Settings" are provided. They are used at runtime of the query.

Consider the following simplistic query against some theoretical structure that has orders with a few different status values (e.g. new, processing, shipped, error):

SELECT * FROM orders WHERE status = 'new';

This will show me orders having the 'new' status. But what if I quickly want to see orders of other statuses? I could rely on the JavaScript search field to search for 'shipped', but that's a generic search on the whole data table, not specifically against the status column.

Imagine if I could set the query to this:

SELECT * FROM orders WHERE status = '{{SPROUTVAR:text Status}}';

and then Sprout Reports scanned the query for {{SPROUTVAR:text Status}} to parameterize the value. The text indicates the data type, and the Status is the label given to the variable. Then, in the Settings area of the report (or some similar new area accessible directly from the report results page), Sprout Reports outputs a UI element to control that parameter value with the label it was assigned in the query. For this text type example, it would be a text field for me to type in a specific value to search for. I could type "shipped" in the text field and click Run Query (or whatever button). Sprout Reports would then run the query with my passed in "shipped" value, outputting the results I want.

Additional data types should be supported for convenience. For example, a boolean value (e.g. {{SPROUTVAR:bool Enabled?}}) should be mapped to a lightswitch field type. A datetime value (e.g. {{SPROUTVAR:datetime Date Created}}) should be mapped to a date and time picker. Etc. The goal is to offer convenience of a UI instead of writing SQL-compatible values.

This is a relatively simple way to extensively expand the custom data possibilities of Sprout Reports. I can define as many 'runtime' parameters as I need in my query. I can also include these values in any way in the custom query; Sprout Reports doesn't need to validate anything other than replacing values in a string (i.e. the query). This allows me to parameterize complex joins, function usages, stored procedures, etc. For users familiar with SQL to do advanced reporting, we could translate powerful SQL options to easy-to-use graphical options.

I understand these types of things are possible by adding Settings to reports, but that effort (and the fact it's a code change) is not reasonable for situations where I am simply wanting to run variations of a query with different static search values.

The specific syntax of the token values can be whatever. I just chose something quickly to demonstrate.

[BenParizek]

Notes to self on syntax:

It looks like we can write a query like this:

SELECT {{[users.id](http://users.id/)}}
FROM {{users}}

And the SQL statement will expand to include the database prefixes:

SELECT craft_users.id
FROM craft_users

It doesn't appear that these Yii2 conventions work:
http://www.yiiframework.com/doc-2.0/guide-db-dao.html#quoting-table-and-column-names

And using a % sign for the database prefix also doesn't seem to work:

{{%employee}}