{"id":3565,"date":"2016-06-16T03:00:08","date_gmt":"2016-06-15T19:00:08","guid":{"rendered":"http:\/\/www.finereport.com\/en\/?p=3565"},"modified":"2019-10-24T14:47:56","modified_gmt":"2019-10-24T06:47:56","slug":"data-set-parameter","status":"publish","type":"post","link":"https:\/\/frg.fineres.com\/en\/2016\/06\/16\/data-set-parameter\/","title":{"rendered":"Data Set Parameter Principle and Creating Steps."},"content":{"rendered":"

What is data set parameter? And how to design it in our report designer<\/a>? Hope this article can help you in some extent.<\/p>\n

1. Data set parameter principle<\/h3>\n

In industries like communication and monitoring, a massive of data will be generated every day and the total data volume in database table will be extremely large. However, in a report, it is common that you only need hundreds of pieces of data at current day.<\/p>\n

If a report parameter is used, you have to fetch all data and then filter them, which will be extremely time-consuming;<\/p>\n

Simple application<\/strong><\/p>\n

When defining data set sql in Finereport business intelligence<\/span><\/a>, you can directly use where condition to filter all required data so as to greatly shorten report fetch time. Like sql below:<\/p>\n

SELECT * FROM Sales Volume where Area = ‘${Area}’<\/span><\/p>\n

Application with formula<\/strong><\/p>\n

In data set sql, you can use a parameter macro ${} for dynamic generation of filter conditions. The statement in ${} will be executed in FineReport. Combine the ${} execution result with the sql statement to form a final query statement and send it to the database for execution. You may use several ${}, as shown below:<\/p>\n

SELECT * FROM Order where 1=1 ${if(len(area) == 0,””,”and Shipper Area = ‘” + area + “‘”)} ${if(len(province) == 0,””,”and Shipper Province = ‘” + province + “‘”)}<\/span><\/p>\n

All built-in FR formulas can be used in ${}, like if function;<\/p>\n

In ${}, except FR built-in function names and constants, all other variables are data set parameters.<\/p>\n

As shown in the above SQL statement, if formula is a FR built-in formula, and the variable area is the data set parameter. If len(area)! =0, then area parameter is not Null. For example, if area value is East China, and the execution result of if statement will be \u201cand Shipper Area = ‘East China’\u201d. Then, combine this statement to SQL statement, and the SQL statement will be:<\/p>\n

SELECT * FROM Order where 1=1 and Shipper Area= ‘East China’<\/span><\/p>\n

2. Creating steps<\/h3>\n

2.1 Open report<\/strong><\/p>\n

2.2 Define data source parameter<\/strong><\/p>\n

Change the data set SQL statement as SELECT * FROM Sales Volume where Area = ‘${Area}’<\/span>. Click the Refresh button at bottom to display parameters. Default value is set to \u201cNorth China\u201d:<\/p>\n

\"\"<\/p>\n

Note: In data set SQL statement, use ${para} as a parameter macro, where the variable para is the parameter name. If the field value is a character string, add quotes, such as ‘${para}’.<\/span><\/p>\n

2.3 Filter data<\/strong><\/p>\n

If the value in parameter area is North China, the final value of ${Area} parameter macro will be North China, and the data set SQL will be SELECT * FROM [Sales Volume] where Area = ‘North China’<\/span>;<\/p>\n

it can be seen that, in data set fetch, data will be filtered based on data source parameter; therefore, there is no need to set additional data column for filtering.<\/p>\n

2.4 Create parameter interface<\/strong><\/p>\n

The default parameter interface is still used here.<\/p>\n

Note: As the default value of data set parameter cannot be directly transferred to the parameter panel control, you need to additionally set the control default value. For details, refer to Control Default Value<\/span>. <\/span><\/p>\n

2.5 Save and issue reports<\/strong><\/p>\n

Save report and view the effect via Page Break Preview.<\/p>\n

Note: If there are several data sets in the template and the defined parameter names are same, a common parameter control can be used in the Parameter Interface. That means, if the data source parameter names are same, only one parameter control needs to be defined.<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

What is data set parameter? And how to design it in our…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[111],"tags":[117],"_links":{"self":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/3565"}],"collection":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/comments?post=3565"}],"version-history":[{"count":6,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/3565\/revisions"}],"predecessor-version":[{"id":8486,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/posts\/3565\/revisions\/8486"}],"wp:attachment":[{"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/media?parent=3565"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/categories?post=3565"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/frg.fineres.com\/en\/wp-json\/wp\/v2\/tags?post=3565"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}