RaqForum 35 No.
1,189 View  • 
     ...

* How to concat a dynamic sql query according to parameters in BIRT

 
sql(179) birt(52)

Key words：Birt splice SQL dynamically   Birt parameter query

BIRT can use SQL query to create datasets, set parameters in reports to pass user input of clients, and set dataset parameters and associate with report parameters (these two parameters are different). In this way, user input of clients can be passed to parameters of datasets for where statements of SQL query. This is a common way to create SQL statements, but this method is only suitable for fixed SQL statements, not for splicing dynamic SQL statements.

For example, you need to deal with a scenario where different order information is queried by level according to the user name and start / end date. Author stores user department information, account stores account information (multiple users can belong to one account, separated by commas), and meta stores order details. The schematic diagram is as follows:

undefined

1. Pass in the login parameter "login_user" to judge the department level           

2. When the level is manager, you need to obtain the current account in advance           

3. When the Department is admin, count all accounts and user sales           

4. When the Department is manager, count the sales of users under the current account           

5. When the Department is engineer, only the sales of the user is counted

BIRT can realize it in an indirect way: select the beforeopen event under the script tab of the dataset, and dynamically splice SQL by writing JS scripts. The codes are as follows:

...

var query =   "select ITEMS,USERS,sum(AMOUNT) as TOTALAMOUNT from META where   ORDERDATE>=? and ORDERDATE<=?";

var user   = reportContext.getParameterValue("login_user");

...

if(flag ==   "admin"){

        ...

        query   += "group by ITEMS,USERS";  

}else if(flag ==   "manager"){

        ...

        query   += "and ITEMS ='"+ account +"'";

        query   += "group by ITEMS,USERS";

}else{

        ...

        query   += " and  USERS ='"+ login_user +  "'";

        query   += "group by ITEMS,USERS";

}

this.queryText = query;

...

This hard coding method will lead to high coupling between calculation logic and presentation. Either a large number of report files need to be modified, or each report needs to inherit a common library file. The code is very complex. When the number of reports and the number of developers are large, it is difficult to manage, modify and maintain.

A better way is to introduce an explicit data computing layer, in which the script function that can be interpreted and executed is provided, and the data source calculation is independent (all business logic judgment and batch data calculation are put in this layer, and finally the calculation results are directly returned to the BIRT dataset for report presentation).

esProc is such a data computing middleware, which provides rich structured dataset computing functions, and can easily realize batch data computing and various dynamic SQL splicing. For example, in the above problem, the SPL code of esProc is very simple and intuitive:

A

B

1

=connect("myDB")

=sql="\"select ITEMS,USERS,sum(AMOUNT) as TOTALAMOUNT   from META where ORDERDATE>=? and ORDERDATE<=?"

2

=flag=A1.query("select dept from author where userid=?",login_user).dept

3

if flag=="admin"

>B1=concat(sql,"group by ITEMS,USERS\"")

4

else if flag=="manager"

>B1=concat(sql,"and   ITEMS='",A1.query("select accountid from account   where find_in_set(?,limits)",login_user).accountid,“'  group by ITEMS,USERS\"")

5

else

>B1=concat(sql,"and USERS='",login_user,"'group by   ITEMS,USERS\"")

6

=A1.query@x(${B1},startdate,enddate)

From the above example, we can see that: to implement the same algorithm, SPL code is shorter, workload is less, debugging is convenient, and it is also conducive to understand and grasp the algorithm as a whole.

In fact, there are many situations in which BIRT is not convenient to solve the dynamic computing problem, but it is very simple with help of esProc SPL. Please refer to Some examples of solving BIRT dynamic data source

esProc provides JDBC driver, and can be easily integrated with BIRT and other reporting tools. Please refer to How to Call an SPL Script in BIRT

Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.

sql(179) birt(52)
 
 •  1,189 View  •