In today’s article, we’re going to tell you about features of data filtering when querying storage and how to reduce time for it, having optimized the process of report and dashboard rendering. In addition, we’ll tell you about the capabilities of report creators when creating a report and a dashboard. Before we proceed to detailed analysis, let us remind you that, in most cases, it’s possible to specify a filtering parameter to a data storage in queries. This, of course, is mainly about SQL data storage, such as MS SQL, MySQL, Oracle, etc.
Our products contain data filtration by the engine of the report generator. You can define filters for elements and components and use data transformation, etc. But, it allows you to display only necessary data in the viewer. However, it will not reduce the time to execute a query. So, the time of getting data from storage can considerably increase the time of their processing by the report generator. This is due to the fact that other external factors exert influence over the speed of execution of the query - from technical characteristics of the server where the database is deployed to maintaining a stable connection to that server. All this indicates the advantages of using parameters in a fetch query.
The parameter is specified in query text according to the syntax used for a particular type of data storage. Important to know!
A query can use named and unnamed parameters. You can learn more about this in the specifications of your data storage. Don’t forget that if a data storage type supports named parameters, they are referred to in the query text by the parameter's name. If named parameters are supported, in this case, parameters in a query are referred to sequentially. Firstly, the first parameter (the uppermost on the parameters panel in the query form) will be used in the query text, then the second (i.e., the second in the list) and further along with the parameters list in the top-down direction.
An expression of the parameter can be both any value and an expression that will result in a filtering value. A reference to a report variable can also be specified as an expression.
Using a variable as a parameter in a query has several advantages over using the Parameter object. First of all, you can create one report variable and use it in queries for various data sources. At the same time, a simple Parameter object can be used only in the data source in which it is created. Secondly, a report variable can be with a value query from a user (input or selection) or simply return a value.
Please note!
In order for a variable to be with a request for a value from a user, in the variable editor, check the box next to the Request from User parameter. In this case, when viewing a report, a user will be able to select a value of the variable. In order for a user to be able not only to select a value but also to enter it, it is necessary to set the Allow User Values parameter in the variable editor. In cases of using a variable as a parameter in a query with user input or selection, you can define the query execution mode when rendering a report: execute a query while rendering a report or postpone query execution until a user confirms the input of parameter values.
What data filtering is intended for?
As a rule, filtering aims to reduce the size of data transferred from storage to the report writer. After processing a query for selection, only the data that is directly required to be used in a report or dashboard will be returned. Since the size of filtered data will be less than the size of all data in storage, they will be processed by the report generator and displayed in the viewer much faster. Reminder!Our products contain data filtration by the engine of the report generator. You can define filters for elements and components and use data transformation, etc. But, it allows you to display only necessary data in the viewer. However, it will not reduce the time to execute a query. So, the time of getting data from storage can considerably increase the time of their processing by the report generator. This is due to the fact that other external factors exert influence over the speed of execution of the query - from technical characteristics of the server where the database is deployed to maintaining a stable connection to that server. All this indicates the advantages of using parameters in a fetch query.
Parameter object
When creating or editing a query in the form of queries, you can create the Parameter object that will be used in a query. There is the New Parameter control to create this object as a query. After creating the parameter using its properties, you can define a data type, size, name, and an expression. The calculation result will be a filtering value of the current parameter. Added parameters in the form of a query will be displayed in the report data dictionary and may be edited at any moment of report creation.The parameter is specified in query text according to the syntax used for a particular type of data storage. Important to know!
A query can use named and unnamed parameters. You can learn more about this in the specifications of your data storage. Don’t forget that if a data storage type supports named parameters, they are referred to in the query text by the parameter's name. If named parameters are supported, in this case, parameters in a query are referred to sequentially. Firstly, the first parameter (the uppermost on the parameters panel in the query form) will be used in the query text, then the second (i.e., the second in the list) and further along with the parameters list in the top-down direction.
An expression of the parameter can be both any value and an expression that will result in a filtering value. A reference to a report variable can also be specified as an expression.
Report variable as a parameter
A report variable can be used in a query explicitly or implicitly. Implicitly means creating the Parameter object in the query form and specifying a link to a report variable as an expression of this parameter. Explicitly using of a report variable as a parameter in a query means setting the Allow using as SQL parameter option in the form of variable edit. Next, you should specify a variable name in the query text.Using a variable as a parameter in a query has several advantages over using the Parameter object. First of all, you can create one report variable and use it in queries for various data sources. At the same time, a simple Parameter object can be used only in the data source in which it is created. Secondly, a report variable can be with a value query from a user (input or selection) or simply return a value.
In order for a variable to be with a request for a value from a user, in the variable editor, check the box next to the Request from User parameter. In this case, when viewing a report, a user will be able to select a value of the variable. In order for a user to be able not only to select a value but also to enter it, it is necessary to set the Allow User Values parameter in the variable editor. In cases of using a variable as a parameter in a query with user input or selection, you can define the query execution mode when rendering a report: execute a query while rendering a report or postpone query execution until a user confirms the input of parameter values.
Query of parameters
By default, when rendering a report, the engine executes a query to data sources, having prematurely initialized parameter values in this query. After, in case of successful query execution, the report is rendered that is displayed in the viewer. However, when a variable is used as a parameter in a query explicitly or implicitly, you can delay query execution until a user enters or selects a variable value in the parameter panel in the viewer when viewing a report.To do this, you should set the Request Parameters property to True value. When loading a report to the viewer or switching to the Preview tab, a query will not be executed until a user clicks the Submit button on the parameters panel. After clicking this button, query execution with initialized values of variables will be started. After receiving data, the report generator renders a report with this data.
In addition to the above, there is one more moment that will help you optimize the process of receiving data by the report generator. When rendering a report or after clicking the Submit button on the parameters panel, the report generator will execute queries for all sources, the description of which you can find in the dictionary. If the dictionary doesn’t have the description of sources that are not used in a report, the report generator will spend time executing them. First of all, it can considerably increase the time of report rendering since the number of unused data sources can considerably exceed the number of those that are needed.
However, the report generator has the ability to define the mode of query execution for the data sources that are directly used in a report.
Request only necessary data
By default, queries to data storage will be performed for all data sources whose descriptions are present in the dictionary. But you can change this and retrieve data only for the sources you need to render reports. In other words, if a report in any component, a tool, connections, or elsewhere has a link to a data column of any data source, that data source is essential for rendering a report.The property that determines whether to perform all dictionary queries or only those used in the report belongs to the report template and is called Retrieve Only Used Data. By default, this property is set to False, which means that all data sources in the dictionary are queried when a report is rendered. In order to query only those data sources that are used in a report, you should set this property to True.
In some cases, performing queries only for used data sources can significantly reduce the time it takes to render a report. Since the dictionary, for example, may contain a description of 10 data sources, but only 2 are used in a report. Thus, the report generator will actually spend time on processing queries for 8 extra data sources, but practically it will not bring any benefit to the current report.
You should remember that rendering a report is, first of all, obtaining and processing data. We are constantly improving our products for rendering reports and dashboards, working on internal optimization processes, and introducing new technologies, but it is not always possible to speed up the process of transferring data from the storage to the report. Therefore, when creating reports, it is important to use all the tools available in the report generator to help make the process of data visualization and analytics faster and more efficient.
If you have any questions about data filtering, please contact us.