Query Runners
Query Runners are designed to allow you to execute query processes multiple times with different parameters. This enables you to call a query process (such as a sql wrapper export, or report) multiple times; once for each setting you want changed, and then be able to download all the generated files at once.
For example this would allow you to run an investor quarter end report, once for each investor, and end up with a report generated pdf for every investor that you can download, or upload to an associated portal.
Query Runner Features
Query Runners simply execute any given SQL Wrapper, and for each result returned they execute all the pre-configured Query Runner Steps.
A Query Runner Step can be setup to run any Query Runner Handler (See API Documentation for more information). This is to allow for extending this functionality through the API. The default Query Runner Handlers in the system are:
- SQL Wrappers
Configuring a SQL Wrapper in a Query Runner Step will result in the query runner producing a csv file for each execution. - Reports
Configuring a Report in a Query Runner Step will result in the query runner executing the configured report and producing a generated file.
All the generated files can then be downloaded anywhere to view.
If all the Step(s) were SQL Wrappers and there is only one Step, or only one row result from the main Query Runner SQL Wrapper, then this can be opened in an excel file (including allowing our Data Templates) with a sheet for each step, or row.
Benefits
This functionality provides an extremely versatile and efficient mechanism to produce multiple versions of reports, or queries at once, all from an integrated and easy to use process within LemonEdge.
It is often the case you'll have professionally produced reports that you want to run for every Investor, Stakeholder, Company, or other entity, every month/quarter/period or whenever. By creating a Query Runner that enumerates every Stakeholder with a Step to run that report with a parameter of the Stakeholder, you can easily solve this in one seamless process. Now a user can simply execute the Query Runner, and it will produce the report for every stakeholder for immediate download.
You can then download all the reports, or automate placing these reports in specified areas, upload them to custom portals, or push them to other 3rd party systems.
As LemonEdge also includes a web version that can run on any browser, you can also configure a role to be setup like a portal to provide those Stakeholders access to download the produced report themselves.
Query Runners main use case is for producing multiple versions of reports depending on your requirements. However given they essentially enumerate a query and run any Query Runner Handler process, they can be used for many other processes too:
- Running multiple different SQL Wrappers (but only once each with custom parameters) and exporting the results into one excel file with a sheet per data set.
- Still into named ranges allowing operability with our Data Templates too.
- Running the same SQL Wrapper, multiple times with different parameters, and exporting the multiple results into one excel file with a sheet per data set.
- Still into named ranges allowing operability with our Data Templates too.
- Implementing your own custom Query Runner Handler (See API Documentation for more information) and using Query Runners to execute it multiple times according to your requirements.
By integrating some of these options with Data Templates, you always have the ability to create templates that also have import named ranges allowing complex sets of data to be exported, modified and re-imported back into the system easily by any user.
Designing
Creating a Query Runner, first involves choosing the SQL Wrapper that will be executed by the Query Runner itself, and will use each result to call every Step configured in the Query Runner.
You should design this SQL Wrapper to return all the parameters you will need to call each Step correctly. Then for each result the system will call each Step according to how it is configured.
While most parameter values are likely provided by the SQL Wrapper for the Query Runner, you can also configure additional parameters that can also be used to provide values to the Steps for each run. Naturally these are essentially global values for the entire run, as unlike any parameters passed from the SQL Wrapper result, they won't change for each row.
Lastly for each step, you are simply configuring the parameters you want to pass to the Query Runner Handler for the step. These can come from the Query Runner Additional Parameter values, the values of the current result from the Query Runner SQL Wrapper, or from specified hard coded, or formula values.
You can then run Query Runners as required, users can easily select a few parameters and have hundreds of reports automatically generated, and (like every Server Task) set them up to run on automated schedules or however you like.
Extending
As with everything in the LemonEdge platform, Query Runners can be accessed, modified, and executed through our complete API.
They are also specifically designed to accommodate easy extension by creating your own entities that implement the IQueryRunnerHandler interface [more info].
Once complete you will be able to select, and configure them, in any Query Runner Step meaning you instantly benefit from them being able to be called easily by users in multiple different ways.
Views
Query Runners are managed using the following views:
