calculate.machine

To create a report according to your requirements is really not easy, because the arrangement of the source data is not the one the crosstab expects for reporting. But by transposing the source data, the creation of a crosstab report will become much easier. The following figure shows the transposed data: The transposition can be done by esProc. The code is as follows: Then Jasper can connect to esProc through JDBC and call the esProc script in the same way as it calls the stored procedure. For more details, see http://blog.raqsoft.com/?p=2855 .

Both the table header row group and table header column group of your cross table consist of intervals. The measurement comes from another table. It’s rather difficult to design such a cross table only using SQL+Jasper. Yet esProc can be used in this case to get data prepared for helping Jasper design the report. The esProc solution is as follows: A3=[560,575,585,595,605,615,625,635,645,655,665] B3=[39,66,91,116,137,155] A4= account_detail.select(empirica_score>=A3(1) && mfin_score>=B3(1)).group( A3.pselect(empirica_score<~[1]):row, B3.pselect(mfin_score<~[1]):col; (accounts=~, paysoft_result.count(accounts.(account_no).pselect(~==custno)) / NAEDO.count(accounts.(account_no).pselect(~==customer_code))):rate) Jasper can connect to esProc through JDBC, and calls the esProc script in the same way as that it calls the stored procedure. For details, see https://dataccelerator.wordpress.com/2015/02/05/esproc-assists-report-development-cross-table-in-which-header-row-column-group-are-intervals/ .

Typically there are three Jasper scenarios in which multiple data sources are required: different data sources for different tables, for different subreports and for master report and table. All of them can be handled through esProc along with Jasper. If master report data come from MySQL, you can retrieve the data using this line of esProc code: result mysql1.query("select * from emp") result msSQL1.query("select * from sales)。If table data come from MSSQL, you can use this line of code to get them: result msSQL1.query("select * from sales) See https://dataccelerator.wordpress.com/2015/02/04/esproc-assists-report-development-different-datasources-for-master-report-and-subreport/ for details.

It’s complicated for Jasper to allocate different data sources for every subreport and the master report, with implicit user name and password. In this case you can try using esProc to help Jasper out from the difficulty. If, for instance, the data for use in the main report are stored in MySQL, you can retrieve them using this esProc script: result mysql1.query("select * from emp") If the data for use in the subreport are stored in MSSQL, in which they relate to the main report through SellerId field, then you can retrieve data using this line of script :result msSQL1.query("select * from sales where SellerId=?",eid). For Jasper, esProc is like a JDBC data source (its name is, say, esProcConn). Different esProc scripts are different stored procedures under the same data source. Thus you can select “Use same connection used to fill the master report” in configuring data source for different subreports. For more details, see http://blog.raqsoft.com/?p=2621 .

The way Jasper handles tables using different datasources is, in fact, the same as that in which it handles different subreports using different datasources. I believe the solution provided by clicking the following link will be useful to solve your problem：http://community.jaspersoft.com/questions/844053/different-report-connections-subreports-jaspersoft-studio

Seen from your description, I guess you want to join tables from different datasources and then filter the result. If no solution can be figured out with virtual data source, you can try esProc. For example: There is table sales in MySQL and table emp in MSSQL. You are asked to join the two tables, with the condition that the result only includes the orders in the last 30 days or those that belong to certain specified departments (like Marketing and Finance). esProc code for doing this is as follows: A1=myDB1.query("select * from sales") // Retrieve data from MySQL A2=myDB2.query("select * from emp") // Retrieve data from MSSQL A3=A1.switch(SellerId,A2:EId) //Join tables together A4=A3.select(OrderDate>=after(date(now()),days*-1)|| depts.array().pos(SellerId.Dept)) // Filter the result of join Jasper can connect esProc using JDBC. It calls the esProc script using the same method as the one with which it calls the stored procedure. See http://blog.raqsoft.com/?p=2546 for more information.

There is no convenient way to realize dynamic datasources Jasper, let alone the free version. In this case you have to create a user-defined datasource. Alternatively, you can try esProc. The script is as follows: result ${sCode}.query("select * from sales where OrderDate>=? and OrderDate <=?",begin,end) In the report, assign parameter sCode with myDB1 to connect to Oracle database, or with myDB2 to connect to MYSQL database. Of course you can write code to connect to different datasources. For more details, see http://blog.raqsoft.com/?p=2528 .

You can handle the printing of top N records of each group while generating the datasources. You can do it in Oracle like this: select * from (select rank() over(partition by catalogFiled order by SortFiled desc) rankNumber,table1.* from table1) t where t.rankNumber <=parameterN But to write this SQL statement requires the Oracle version you are using support window functions. And the coding in MSSQL will have to be modified a bit. If the database doesn’t support window functions, like MYSQL, you can consider using esProc to help Jasper to achieve the result. esProc code is as follows: A1=mydb1.query("select * from table1 ") //Query data A2=A1.group(SellerId) //Group data A3=A2.(~.top(Amount;parameterN)) //Get top N records from each group; the parameter is passed from the reporting tool A4=A3.union() //Union these records together A5: result A4 // Return result of A4 to Jasper Jasper can connect to esProc through JDBC. It calls the esProc script in a way the same as that it calls the stored procedure. For more information, see http://blog.raqsoft.com/?p=2447 .

Joins across MongoDB and MySQL can be realized with virtual data source. But it is only available in commercial or high-end versions and has limited ability by supporting the combination of only two data sources. You can try using esProc to help Jasper in handling this kind of join. Suppose to query records of collection emp1 in MongoDB and to switch its CityID field to CityName field of table cities in MySQL. You can do it with the following esProc code: A1=MongoDB("mongo://localhost:27017/test?user=root&password=sa") A2==A1.find("emp1","{'$and':[{'Birthday':{'$gte':'"+string(begin)+"'}},{'Birthday':{'$lte':'"+string(end)+"'}}]}","{_id:0}").fetch() A3=A1.close() A4= myDB1.query("select * from cities") A5= A2.switch(CityID,A4) A6=A5.new(EID,Dept,CityID.CityName:CityName,Name,Gender) Jasper can connect to esProc using JDBC. The way it calls a script file is the same as that it calls the stored procedure. For more information please see http://blog.raqsoft.com/?p=2460

I don’t think you describe your problem clearly enough. Maybe you want this: Input the parameter (1 or 2) and get from the JSON document three fields of the specified members of the array: horseId, trainerId,ownerColours; then the result should be as follows if the input parameter is defined as 1: 1.00387464 1.00034060 Maroon, pink sleeves, dark blue cap. 1.00373620 1.00010997 Black, emerald green cross of lorraine, striped sleeves. Based on this, I tried to solve the problem using Jasper but all I got is null. Is this the problem to which you are trying to get a solution? I tried another method later. I used esProc to assist Jasper in processing the Json data and it worked well. The esProc code is as follows: A1=file("D:\jsonstr.json").read() / Read the file out as the string A2=A1.import@j() / Parse the json string A3=A2(which).runners / Get the specified members of the array according to the parameter defined; “which” is the parameter name A4=A3.new(horseId,trainer.trainerId:trainerId,ownerColours) / Get three fields; trainerId needs to be got from the sublevel of data A5 result A4 / Return the result of A4 to Jasper The code can be combined into one single line：result file("D:\jsonstr.json").read().import@j()(which).new(horseId,trainer.trainerId:trainerId,ownerColours) Jasper can connect to esProc through JDBC. The way it calls the esProc script is the same as that it calls the stored procedure. For details please refer to http://blog.raqsoft.com/?p=2447 . The final report is as follows：

Both SQL and Jasper should be able to realize the goal of group_concat function. But the code will be difficult to write. You’d better try using esProc to assist Jasper. The following code will do completely the same thing as group_concat function does, without the database involved. =myssqlDB.query("select * from table1").group(Col1,Col2,Col3;~.(Col4).string@d():Col4) Similar to the database, esProc offers the JDBC interface to be integrated in Jasper. Please refer to http://blog.raqsoft.com/?p=2436 for details.

To connect to multiple datasources, Jasper offers virtual data source. The function, however, is provided only in the commercial or higher version and probably won’t appear in a free version. In addition, it supports connecting to only two datasources. But you can try using esProc to assist Jasper to join the two collections. Suppose to select orders during a certain time period from collection sales and create a left join with collection emp. You can do it in esProc as follows: A1=MongoDB("mongo://localhost:27017/test?user=root&password=sa") A2=A1.find("sales","{'$and':[{'OrderDate':{'$gte':'"+string(begin)+"'}},{'OrderDate':{'$lte':'"+string(end)+"'}}]}","{_id:0}").fetch() A3=A1.find("emp",,"{_id:0}").fetch() A4=A1.close() A5=join@1(A2:sales,SellerId;A3:emp,EId) A6=A5.new(sales.Client:Client,sales.Amount:Amount,sales.OrderDate:OrderDate,emp.Name:Name) Jasper can connect to esProc through JDBC. The way it calls an esProc script is the same as that it calls the stored procedure. Please refer to http://blog.raqsoft.com/?p=2460 for details.

Jasper can only use one datasource. It provides no convenient way of using dynamic datasources. Generally you can use the user-defined datasource, but the code for doing this is not easy to write. Yet, this can be made easier if you use esProc to assist Jasper. The esProc script is as follows: result ${sCode}.query("select * from sales where OrderDate>=? and OrderDate <=?",begin,end) In a report, connect to Oracle database by assigning myDB1 to parameter sCode or MYSQL database by assigning myDB2 to the parameter. For more complicated cases, you can write code for connecting to different datasources. For details, please refer to http://esproc.blogspot.com/2015/01/esproc-assists-report-development.html .

Jasper can only use one datasource. It provides no convenient way of using dynamic datasources. Generally you can use the user-defined datasource, but the code for doing this is not easy to write. Yet, this can be made easier if you use esProc to assist Jasper. The esProc script is as follows: result ${sCode}.query("select * from sales where OrderDate>=? and OrderDate <=?",begin,end) In a report, connect to Oracle database by assigning myDB1 to parameter sCode or MYSQL database by assigning myDB2 to the parameter. For more complicated cases, you can write code for connecting to different datasources. For details, please refer to http://esproc.blogspot.com/2015/01/esproc-assists-report-development.html .