Database queries

This section provides details and examples of SQL queries for several types of data filtering options.

The security filtering can be performed by limiting the database query using the Viewer (user) info-related variables starting with _reportViewer. Other types of filtering can be applied depending on user input, using Request from User (RfU) variables. See Request from User (RfU) variables for more details.

The standard function used for filtering in Reporting is a conditional ternary expression. The syntax is {Condition? Value1: Value2}, where everything inside the {} indicates an expression to be evaluated, Condition is the boolean statement under evaluation, Value1 is the result for the True case of the evaluation, and Value2 is the result for the False case of the evaluation.

Query examples

All the following examples are written for a PostgreSQL database. The syntax should be modified accordingly for all other database types.

Ex.1

The following example is a simple query to the report table, using an RfU string variable NameLike

select id, name, "dateCreated" from report where

{NameLike == "" ? " 1=1 " : " name like '%" + NameLike + "%'" }

• In case NameLike equals an empty string, then it is ignored (query executes 1=1).
• In case NameLike is not an empty string, fetch the select id, name and dateCreated column values for all records where name column contains the string from NameLike.

Note The string _OPTIONAL_ is used as a keyword in the Init by field of an RfU variable to indicate that it is not required (mandatory) to input a value for report rendering.

Ex.2

NameSelected is an RfU variable with a populated list of values, acquired from the database.

Only the data for the selected report name will be displayed.

select id, name, "dateCreated" from report where
{NameSelected == "_OPTIONAL_" ? " 1=1 " : (" name = '" + NameSelected + "'") }

 

Ex.3

This example is similar to the previous one, however, a list is used to display available items, making multiple selections possible.

select id, name, "dateCreated" from report where
{NamesSelectedList == "_OPTIONAL_" ? " 1=1 " : (" name in (" + ListToQueryString(NamesSelectedList) + ")") }

A special custom function ListToQueryString is used to populate the list dropdown menu. The function reformats the values acquired from the name column of the connected table to a list required format: "abc,def" => "'abc','def'"

Function body: (list) => list.split(",").map(elem => `'${elem}'`).join(",")

Note In this version of Reporting, custom functions are not available. This feature will be made available in the upcoming releases.

Ex.4

Display only data for the submitted Id value.

select id, name, "dateCreated" from report where
{Id > 0 ? "id=" + Id : " 1=1 "}

Ex.5

Multiselect Id from list, similar to Ex.3.

select id, name, "dateCreated" from report where
{IdsItems == "" ? " 1=1 " : "id in(" + IdsItems + ")"}

Ex.6

Filter data to be fetched and displayed based on the user input date, but ignore if date is invalid.

select id, name, "dateCreated" from report where
{DateCreatedExact.ToString() == "" || DateCreatedExact.ToString() == "Invalid date" ? " 1=1 " : " \"dateCreated\"::date ='" + DateCreatedExact.ToString("YYYY-MM-DD") + "'::date" }

Ex.7

Filter data to be fetched and displayed based on the user input date range.

select id, name, "dateCreated" from report where
"dateCreated" >= '{DateCreated.From.ToString("YYYY-MM-DDTHH:mm:ss")}'::timestamptz
and
"dateCreated" < ('{DateCreated.To.ToString("YYYY-MM-DDTHH:mm:ss")}'::timestamptz)

Ex.8

Display only data about reports owned by the current viewer using the _reportViewerUsername automatically created variable.

select r.name, r.id, r."dateCreated" from public.report r join public.user u on r."userId" = u.id where u.username='{_reportViewerUsername}'