Easy Insight Logo
Call 1-(720)-316-8174

Database Connections in Easy Insight

Data Flow

For the server side database connection to work, you'll need to open up your firewall to allow access on the database port to Easy Insight. If you can't allow access through the firewall, you can to download and use the alternate database connection available for download under the connection, hosting that behind your firewall.

You'll need to specify connection credentials and the query. The query defines what fields and data to pull back into Easy Insight. For example, you might do 'select * from table_name', 'select a, b from table_name' or 'select a as A, b as B from table_name where X = Y'.

Once you set up your query, Easy Insight will pull over and store the results from the query. The results from the query are what are then available in the data source for your report development. If you need to do a report across multiple tables and a simple join as above doesn't work, you can pull over each table as a separate data source, then create a combined data source and specify the joins in Easy Insight.

Parameterized Query Sources

You can also define parameterized data sources for more complex queries that you want to handle and pull data on a live basis as opposed to caching in Easy Insight. You can toggle the parameterized query by clicking on the 'Create a Parameter Query' link under the connection.

Specify your parameters in the query by doing {parameter name} where the parameter should go. For example, select * from table where name = {name}. For each parameter, you'll then need to define a mapping for the parameter below the query so that Easy Insight understands how to map in values.

For dates, you can do {date} for a single date, or use {date.start} and {date.end} to set up a start/end date that enables you to control the date range from a single filter on your report.

Once you create a report on the connection, you'll have to create filters for every parameter in the query. If you try to run a report without filters set up, you'll be prompted and helped with filter setup.

Since single and multiple value filters need to pull values from somewhere, if you're doing a report that requires one of those types on a parameter query field, you'll need to use a combined data source that can retrieve values for the filter from another data source.

As an example, let's say you have the following query:

select col1, col2, col3 from order where customer = ?

When you create a single, multiple, or auto complete value filter in Easy Insight, Easy Insight needs to be able to pull the values from somewhere. In order to accomplish this, you'd want to do the following:

    1. Create the parameterized query connection with Customer as a parameter field
    2. Create a standard query connection to the table, in this case customer, containing the possible filter values, something like select name from customer
    3. Create a combined data source joining the two connections, with a join defined on Customer Name to Order Customer
    4. Create a new report and add in a field from the parameterized query connection. The report editor will prompt you to create a filter on customer before the report can run. Choose single, multiple, or auto complete value and choose the Name field from Customer.
Twitter Logo