Using the SQL queries helps avoiding the data duplication in the tables and provides maximum flexibility when searching and showing the data in a database. This also allows requesting only required data, fields, registers etc.
One of the ways to get only required data is using parameters. When you create a query you can use Parameter. It is used to pass additional information into the query for selecting data. For example, you can create a query with parameters when it is required to use some values in the query during every execution of this query. The object Parameter can be used only together with SQL data sources.
More information about it can you read in our documentation.
Also you can create parameters from the code:
Working with parameters in Stimulsoft Reports software is similar to the parameters in the .NET Framework.
Using variables in parameters
In Stimulsoft Reports you can use Variables in the report. The Variable provides an ability of placing and using a variable when creating reports. The variables can be of different types such as string, date, time, array, collection, range etc. All variables are stored in the data dictionary. Before you will use any variable, you should add it to the data dictionary. More information about this can be found in our documentation.
Values to variables can be set from code or by request from the user. There are two ways how to do this from code. It depends on with which report (compiled or not) the user works.
before compilation:
after compilation:
When you request the value of the variable from the user, you should set the parameters on the panel Request from user. These parameters determine possible involvement of the user when applying a variable in the report. Possible values of variables the user can get from the data source, specifying it in the Data Source property. For correct work of this functionality you should get data for all data sources, and then request the selection of the values for variables from the user. If possible values are not taken from the data sources and values selected by a user are used in queries, then, in order to avoid requesting all the data once again, it makes sense to set the Request Parameters property of the report to true.
In our reporting tools you can use the variables directly in the query. The value of the variable will be inserted into the query directly before its execution without some additional actions. For example, when the query is:
and a value of the variable is Chocolate, then in this case the next query will be executed:
Naturally, this query will not be executed. The correct query is the next one:
You can find this feature only when working with strings, but this behavior allows creating more difficult queries dynamically in contrast to values of variables. The similar example is shown below.
Using variables to output lists
From time to time you need to create more complex queries. For example, when, on one hand, you need to get data from the data source to give a choice for a user, and on the other hand, some queries may not be executed, because the required parameters are not set. In this case we a have a problem.
One of such examples. The variable with enabled option Request from user can be used both for selecting several values and in the operator IN of the SQL query.
You need to know one thing. By default the variables of the type List are filled only after connection of the dictionary to data sources. Therefore, during the first connection of data source this variable will be empty, and the expression Variable1.ToQueryString() throws an empty string and, accordingly, the entire query throws the error. To avoid this we have added an additional check.
In this example all data will be shown and after you click the button Submit the report will be rebuilt using only selected data.
When you need to view only required data, you should do the following:
1. Set the report property report.RequestParameters to true. And, before the first click of the button Submit, only the empty report page will be viewed and rendering will begin after the click.
2. At once initialize the report variable with additional values.
One of the ways to get only required data is using parameters. When you create a query you can use Parameter. It is used to pass additional information into the query for selecting data. For example, you can create a query with parameters when it is required to use some values in the query during every execution of this query. The object Parameter can be used only together with SQL data sources.
More information about it can you read in our documentation.
Also you can create parameters from the code:
Working with parameters in Stimulsoft Reports software is similar to the parameters in the .NET Framework.
Using variables in parameters
In Stimulsoft Reports you can use Variables in the report. The Variable provides an ability of placing and using a variable when creating reports. The variables can be of different types such as string, date, time, array, collection, range etc. All variables are stored in the data dictionary. Before you will use any variable, you should add it to the data dictionary. More information about this can be found in our documentation.
Values to variables can be set from code or by request from the user. There are two ways how to do this from code. It depends on with which report (compiled or not) the user works.
before compilation:
after compilation:
When you request the value of the variable from the user, you should set the parameters on the panel Request from user. These parameters determine possible involvement of the user when applying a variable in the report. Possible values of variables the user can get from the data source, specifying it in the Data Source property. For correct work of this functionality you should get data for all data sources, and then request the selection of the values for variables from the user. If possible values are not taken from the data sources and values selected by a user are used in queries, then, in order to avoid requesting all the data once again, it makes sense to set the Request Parameters property of the report to true.
In our reporting tools you can use the variables directly in the query. The value of the variable will be inserted into the query directly before its execution without some additional actions. For example, when the query is:
and a value of the variable is Chocolate, then in this case the next query will be executed:
Naturally, this query will not be executed. The correct query is the next one:
You can find this feature only when working with strings, but this behavior allows creating more difficult queries dynamically in contrast to values of variables. The similar example is shown below.
Using variables to output lists
From time to time you need to create more complex queries. For example, when, on one hand, you need to get data from the data source to give a choice for a user, and on the other hand, some queries may not be executed, because the required parameters are not set. In this case we a have a problem.
One of such examples. The variable with enabled option Request from user can be used both for selecting several values and in the operator IN of the SQL query.
You need to know one thing. By default the variables of the type List are filled only after connection of the dictionary to data sources. Therefore, during the first connection of data source this variable will be empty, and the expression Variable1.ToQueryString() throws an empty string and, accordingly, the entire query throws the error. To avoid this we have added an additional check.
In this example all data will be shown and after you click the button Submit the report will be rebuilt using only selected data.
When you need to view only required data, you should do the following:
1. Set the report property report.RequestParameters to true. And, before the first click of the button Submit, only the empty report page will be viewed and rendering will begin after the click.
2. At once initialize the report variable with additional values.