Unique report name

Long text that describes the report

SQL that creates the result rows. See SQL / JINJA2

Special Variables

When formatting SQL these special variables are available:

Row styles

Use a column named row_style to apply a HTML style to a row. This style value must parse into valid JSON. Example of changing the background color of a row:

select id, setting_type,
case
when setting_type = 'cam' then '{"background":"#F9CBCB"}'
when setting_type = 'page' then '{"background":"#CBF9E1"}'
when setting_type = 'device' then '{"background":"#F0F8F8"}'
end as row_style

Type

Select the type of output:

• result - Report fields and output section, with tabular, graph or card output.
• panel - Multiple sections with differing output and formatting per section
• template - Free form HTML output using JINJA2

Cluster

The connection cluster to use to connect to a database.

Active

Check to allow use.

Run Immediate

Run immediately after the report is opened.

Cluster Strategy

A cluster strategy is how the connections for a cluster are used by the report to get results. It can be one of:

• Balance - The default, connects to one connection for results.
• Consolidate - Join up the results from multiple connections to one result.
• Distribute - Get results from multiple connections and show them separately.

Distribute

Each result from each database/connection of the cluster is shown as a separate section in the result.

Consolidate

Results from each active connection of a cluster is joined together in one big result.

Total result fields

Secure Reporter can consolidate results from multiple databases that share the same cluster. When a report is run all database connections that have the same cluster name are used to run the same sql. As long as each database has the same or very similar tables the queries should return the same columns and value types.

The databases do not have to be the same type. You can mix MSSQL, Sqlite, Postgres and MySql as your heart desires.

Secure Reporter will join all the query results together to show a single view of multiple databases.

The order of querying is not guaranteed, so use the sort_by setting value to ensure a certain order.

Sort by

A column name to sort the consolidated results by. Column values can be numeric or textual. Numeric is attempted first

Only ascending sort is supported. Adjust the column value to sort in other ways.

NOTE: Only enabled when distribute is the Cluster Strategy and more than one database is in the cluster

Total by

When operating in a multi database reporting mode this report setting is used to sum and consolidate columns/fields that need to be summarized across databases.

NOTE: Only enabled when distribute is the Cluster Strategy and more than one database is in the cluster

eg.

1,total_accounts,sum_money,avg_money='{0:.2f}'.format(sum_money/total_accounts*100.0)

A list of field names and formulas to total a report by.

First field is the number of key fields starting form the first columns of the SQL result. Fields/columns after that count need to be generated by the Secure Reporter.

Example:

country_bank Table before summation:

key fields

1 is the number of key fields which is:

The values of these fields/columns will be used as a group to sum each of the rest of the columns. So the sql query may sort by these columns. Any column to be found in the total by field must be in the SQL query, even if it is going to be replaced by a calculation.

select country, total_accounts, sum_money, 0 avg_money from country_bank

Next fields are those to total. A field/column with the format name= will use a Python statement to generate the result. Eg:

avg_money='{0:.2f}'.format(sum_money/total_accounts)

The avg_money column will take on the value using the running total of sum_money and total_accounts columns. Any column you use in a formula should precede the formula.

Result

The last row in the table is generated and appended after all databases have been queried.

Groups

A list of groups that may have access to the report. Use * to allow anyone access.

Hide Columns

List all columns (comma separated) to be removed from result displays. Primary keys are prime examples.

NOTE: the column name is case-sensitive.

Formatting

Way to display the results:

• Table - show results in a table of columns and rows.
• Cards - show each row as a separate card.
• Pie, Doughnut, barChartVertical, lineChart - Chart results simply.

Background Color

Color to show in the background of the report.

Background Image URL

Image to display in top right of report and on the home page.

Sample Images

Select from one of these builtin images for a background.

Preview

Home page report preview. Gives you an idea of how the report will appear in the home page.

Use this to create drill down of results to a new report. The values of the result row will be passed to the Pass Report.

Pass Columns

List all columns (comma separated) to sent to a “Pass report”. Columns should either match the SQL result or be of the format “fieldvalue[1..20]”, Field value will use the existing field criteria and pass to the pass report as ” fieldvalue[index of the column in the pass columns list]”. Column names must match the case of the SQL result. For example, with the following pass column list field_value_1' becomesfield_value_3` in pass report.

id,contactname,field_value_1

Example:

Pass columns definition:

color,smell,field_value_2

With row 1 clicked and report criteria and SQL result as follows:

Type: [ hello ] Name: [ Blay ]

Will pass the sql results from color, then smell and then field_value_2 (value=Blay) as follows:

field_value_1=red&field_value_2=cheese&field_value_3=Blay

Pass Report Name

Select the report to pass the row of values from the result to.

Control how the report results are refreshed.

Cache Results

Cache the results of queries to improve speed.

Refresh minutes

Choose a number to cause the report to be re-run every required minute. Leave blank for no refresh.

Report fields can be added to a 'result' type report to allow parameters to be added to a reports SQL query.

Click on the Add report field button to add a field to the report.

Report fields are added to a report to allow entering of parameters to be used when building the SQL body. These can then be used in the SQL body. Example:

select username, email from user_record
where 1=1

and email = :field_value_1

Field name

The label that will appear on the report form. Required. When available in the SQL body the value will be present as the variables field_value_ and _field_name. Where _field_name is made up of the field name with a ‘_’ underscore in front and any non Python variable characters replaced with a _underscore. Usingfield*value*as a variable in the query wheren is the index of the field order, starting at one. Example:

Report field:

Report field name: email address
Report value: andrew@gmail.com

SQL Body:

  select username, email from user_record
  where 1=1

  and email = :_email_address

    select username, email from user_record
    where 1=1
    and email = :_email_address

NOTE: field names are case-sensitive when processing the SQL body. NOTE: field names are not used with formatting SQL choices.

Description

A longer description of the field that will show when hovering over the field.

Field Type

Type of the field. Controls how validation and SQL insertion is handled.

• text - any text value
• date - shows date picker with current date checkbox
• email - validated email text field
• number - must be Javascript number.
• choice_sql - sql statement derives choices
• choice - list of texts derives choices
• checkbox - Y / N

NOTE: Current Date checkbox will return the string today

Value Conversion

JavaScript conversion, is used in the client to convert the field base value to another type or get a substring of the value.

Type conversion can be one of:

• Number
• String
• Boolean
• Uppercase - converts to String first
• Lowercase - converts to String first

Or two numbers that define a substring to extract using String.substring method.

Eg:

1,2

Extract from position 1 to position 2. Zero based indexes. Value is converted to a String first.

Default is not to convert.

Select SQL

A SQL statement that returns at least one column of results to show as choices. The first connection in a cluster is used to build the results.

Eg:

select text_name, value from table_name

First column is the displayed choice text, second column, if specified, is the value to use.

The value sent to the report SQL will be the second column’s value or the first column if only one column in the select statement.

JINJA2 macros can be used to format the SQL.

NOTE: If the first column text contains commas, , then the choices will be expanded to each item seperated by ,.

Eg:

`select '1,2,3'`

Becomes:

1
2
3

Select Choices

List of text choices to show in the field. Text and value are the same.

Example:

1 day,30 days,1 month,6 months,1 year,2 years

Validate Regex

Javascript regex expression to test the entered value for validity.

Default Value

Default value to populate the field with. For choice fields this must match the text value of the choice.

Field Order

Order of the field to display. Report fields can be reordered using drag and drop.

Min Width

For numbers the minimum value the result can be. For text the minimum length the value can be. A number greater than 0 makes this field required.

Max Width

For numbers the maximum value the result can be. For text the maximum length the value can be.