calculate.machine

To make a fixed header for a crosstab doing sums, you need to supply the values that are missing in the source data. The solutions is this: Perform a difference operation between [1,2,3…12] and distinct(month) to find the missing months (for instance, they are, say, 3 and 5). Then fill up the values of those missing months in the source data. Here I use esProc to do the job (code is simpler). Here’s the esProc code: A1=$select month,location,value from tb A2=to(12)A7.id(month) A3=A1|A2.new(~:month,A1.location:location,null:value) Jasper can access the esProc code via JDBC. This is similar to its calling of a database.

If there’s data recording multiple days of information of the usage of a parking garage (for example, 73,74,73,70 for a certain day), and we need each day’s numbers of cars that get into and out the garage, we can do the calculation this way: Group the data by dates; in each group we subtract the number of parked cars in each member from this number in the next member (73,1,-1,-3), get the remainders from the second member to the last (1,-1,-3), and then, to get the number of cars that get into the garage, calculate the sum of the numbers that are greater than 0 (1=1), and to have the number of cars that go out of it, calculate the sum of the numbers that are less than 0 and get the opposite number ((-1-3)*-1=4). This is relatively complicated, so we’d better generate a two-dimensional table according to this algorithm to make the data source ready. Then we can just present the result set in Jasper. As it’s difficult to implement it in SQL, we could do it in esProc with simpler code: A1=$select date,occupied from tb A2=A1.group(date;t=(~.(occupied-occupied[-1]).to(2,)),t.select(~>0).sum():arrived, t.select(~<0).sum()*-1:left) Like calling a database, Jasper can integrate the esProc code via JDBC.

Group the records by both Code and Month and concatenate countries in a group with the line break. If you use MySQL, you can use group_contact to compose strings . But there's troubles with other databases. You can use esProc to prepare such a data source and then present it with crosstab in Jasper: A1 =$Select Code, Country, Month from tb A2 =A1.group(Code, string(date(Month, "MM"),"MMM"):M;~.(Country).string("n"):C) Jasper can access the esProc code via JDBC, which is similar to calling the database.

You cannot sort the data according to aggregate values automatically in Jasper. A better way is to sort the source data by aggregate values before reporting , then perform group and aggregate in Jasper (here data is only grouped without sorting) . You can implement the algorithm in esProc . The code will be simpler than using a stored procedure: A1=$select Store, Item, Safe from tb A2=A1.group(Store).sort(~.sum(Sale):-1).conj() Jasper can access the esProc code through JDBC. This is like the way it calls a database. Learn more at http://blog.raqsoft.com/?p=4744 .

We can use jasper's built-in multi-column layout function to produce the desired repot. To create the multi-column layout in a subreport or a band, we need to arrange the source data according to the layout. Suppose that the source data has 10 double-field records, and you want to arrange it into 3 columns(colN=3) . Then we need to generate a 6-field two-dimensional table. We can use esProc to rearrange the data. The code is simple: A1=$select name,value from tb A2=ceil(A1.len()/colN) A3=A1.group(ceil(#/A2)) A4=create(${colN.("name"/~/",value"/~).string()}) A5=A4.paste(${A3.len().("A3("/~/").(#1),A3.("/~/").(#2)").string()})

The data you provide is incomplete, but I can figure out that the result of the MongoDB query should be like the following result1: CurTskId Owner1 Owner2 1 Noemi 2 Carl 3 Carl 4 Darla 5 Darla 6 Carl 7 Paola 8 Paola 9 Noemi 10 Noemi And you want to make it look like result2 below: CurTskId Owner1 Owner2 1 Noemi Noemi 2 Carl Carl 3 Carl Carl 4 Darla 5 Darla 6 Carl Carl 7 Paola 8 Paola 9 Noemi Noemi 10 Noemi Noemi To do that you can retrieve the unduplicated values (which is similar to distinct operation) from Owner1 and loop through each record to update Owner2; then repeat the operation on the original Owner2 and update Owner1 by loop. It can be difficult to implement this indirect algorithm using a MongoDB query. And it’s as hard to do it using a Jasper script after the retrieval. In this case you can use esProc to make the process of data preparation easier, as shown below: A1=mongodb("mongo://127.0.0.1:27017/myTest?user=root&password=sa") A2=A1.aggregate("tickets","[copy your mongo query string here]") A3=A2.id(Owner1).select(~) B3=A2.id(Owner2).select(~) A4=A2.run(if(A3.pos(Owner2),Owner1=Owner2)) B4=A2.run(if(B3.pos(Owner1),Owner2=Owner1)) After data is prepared, you can present data with the original template, or you can use esProc to complete it: A5=A2.group(Owner1,Owner2) A6=A5.new(ifn(Owner1,Owner2):owner, ~.count():Inventory, "Owner1:"+ifn(Owner1,"null")+"|Owner2:"+ifn(Owner2,"null"):detail) Below is the final result: owner Inventory detail Paola 2 Owner1:null|Owner2:Paola Carl 3 Owner1:Carl|Owner2:Carl Darla 2 Owner1:Darla|Owner2:null Noemi 3 Owner1:Noemi|Owner2:Noemi Find more related information from http://blog.raqsoft.com/?p=4824 .

The algorithm is this: retrieve the substring beginning with "members" from xml, take it as JSON data and parse it as a two-dimensional table. It is complicated to write a report script to code this. An alternative is to first parse JSON using esProc import@j() function and then use the report to access the esProc code via JDBC. Below is the code: A1=file("d:\source.txt").read() A2=pos(A1,"{members") A3=pos(A1,"</json_members>") A4=mid(A1,A2,A3-A2) A5=A4.import@j().members You can find more examples in http://blog.raqsoft.com/?p=4679 .

You can implement the repetition of the result set for N times in esProc easily, using the following code: A1=$select f1,f2,f3 from tb A2=A1*argN Find more examples from http://blog.raqsoft.com/?p=4744 .

You can judge if a row is null to decide whether to hide it or not through a report script. But this means the length of each record won’t be the same any more. If you want to modify field values during data preparation to meet the condition that “if add2 is null then add3 comes in add2 line and add4 comes to add3 line”, I suggest you using esProc, in which you can handle it with two lines of code: A1=$select partyid,add1,add2,add3,add4 from tb A2=A1.(~.record(~.array().select(~)|["","","",""])) Find more examples from http://blog.raqsoft.com/?p=4744 .

You can set the number of columns to have a vertical multi-column layout for the report in Jasper. If you just want 4 rows in part of the report, you can prepare data for report creation by converting the original data table to a 4-row, multi-column one. But it’s hard to code this using the stored procedure or Jasper. Yet you can use esProc to make it easier: A1=myDB1.query("select orderID,Client from sorder") A2=create(${Col.((t=~,A1.fname().(~+string(t)))).conj().string()}) >Row.run(A2.record(A1.m(to(Col*(~-1)+1,Col*~)).conj(~.array()))) In the code, Row represents the number of rows and Col represents the number of columns. Both are report parameters. This piece of code, in which you can set an actual value 4 for Row, can achieve vertical multi-column on any data table. The code applies to any multi-field table. For a one-field table, the code can be simpler. For more details, refer to http://blog.raqsoft.com/?p=4744 .

To recompute the structured data from a stored procedure, you can use another stored procedure and write the computing result into a temporary table, or you can write a report script. Both are complicated. Suggest using esProc to fetch data from the stored procedure and perform the recomputation during data source preparation. Then the esProc code can be called by the report via JDBC. Below is the code: A1=ora.proc("{call mySP(?,?)}",:101:"o":outCursor,arg1:0:"i":) A2=outCursor.group(counter;~.sum(Amount):subtotal).sort(-subtotal) A3=A2.derive(subtotal/A2.sum(subtotal):percentage) In the above code, 101 means the Oracle stored procedure returns a cursor; arg1 is a report parameter; sort(-subtotal) means sorting data in a reversed order. The esProc method of helping Jasper can be found in http://blog.raqsoft.com/?p=4665 .

Store the codes and table names in a variable (such as a two-dimensional table, a hash table or a temporary table), and retrieve table names according to the codes to execute SQL dynamically. It’s not easy to implement the algorithm in SQL or using a report script. An alternative is using esProc. [/code]A1=[1,"VIEW_A",2,"VIEW_B",3,"VIEW_C",4,"VIEW_A"] A2=create(key,value).record(A1) A3=myDB1.query("select distint ORIG FROM "+A2.select(key==argkey).value) In the esProc code, argkey is an external parameter that represents the codes. If the codes are an integer sequence starting from 1, you can get table names by member positions. Thus the code can be simplified as below: A1=["VIEW_A","VIEW_B","VIEW_C","VIEW_A"] A2=myDB1.query("select distint ORIG FROM "+A1 (argkey)) Detail about the esProc solution can be found in http://blog.raqsoft.com/?p=4665 .

It is a hassle to convert a multi-level JSON file to a two-dimensional table using the report script. I think you could consider using esProc to prepare data that the report needs. The report then can access the esProc code via JDBC. A1=file("d:\source.json").read().import@j() A2=A1.news(custCars;A1.custName:custName,carMake,carModel,carMiles) Refer to http://blog.raqsoft.com/?p=4789 for more details.

In creating a Jasper report, if the main report and its subreports use different data sources, you can only use Java classes or write an actual url, username and password, instead of referencing the data source names. Yet, esProc is helpful in solving this type of multi-data-source problems. For details, refer to http://blog.raqsoft.com/?p=4243 .

The source data has two parts: the databases and the files. Here the requirement is to parse data dynamically and then perform database query by referencing file names through a parameter. I suggest using esProc to assist the coding work. You can find details from http://blog.raqsoft.com/?p=4243 .

Let’s compromise and put two detail banners in the report. One uses the first 5 columns and the other uses the rest of the 5 columns. Problem is that the pages will be 1,3,2,4, which is not what you want. To get the desired page arrangement, you need to transform the result set with 10 columns and N rows to one with five columns and N*2 rows, and append column names at the beginning of each page. That way the result set would be like this: c1 c2 c3 c4 c5(do not show these column names of the result set in the report) col1 col2 col3 col4 col5 (A record, which is the first row on the first page) Data… Data… …(Assume each page holds 25 rows, now another 24 rows are needed) col6 col7 col8 col9 col10 (The first row on the second page) Data… Dada… …(The 50th row) col1 col2 col3 col4 col5 (The first row on the third page) … You can compose the above result set with the stored procedure, but the code can be very complicated. Here is an implementation in esProc: A 1 $select * from tb1 2 =create(c1,c2,c3,c4,c5) 3 =A1.group((#-1)24) 4 =A3.run(A2.record(["col1","col2","col3","col4","col5"]| ~.conj([col1,col2,col3,col4,col5])| ["col6","col7","col8","col9","col10"]| ~.conj([col6,col7,col8,col9,col10]) )) 5 result A2 A1: Execute SQL statement to get data with 10 columns and N rows. A2: Create an empty two-dimensional table. A3: Group A1 every 25 rows. "#" represents the row number and "" means integer division. A4: Loop through A3’s groups and insert 50 rows to A2 in each loop. They are names of the first 5 columns, 24 rows for the first 5 columns, names of the second 5 columns, and 24 rows for the second 5 columns. ~ represents the current group in each loop. A5: Return A2’s result to the reporting tool via JDBC. The way of integration is introduced at http://blog.raqsoft.com/?p=2447 . More example can be found at http://blog.raqsoft.com/?p=4124 .

An easier and more common approach to the problem like yours is to first prepare data. Thus the reporting tool is only responsible for displaying the data. You can handle the data preparation with esProc (its free edition is available). The code is only two lines and easy to understand: A1=$select * from tb1 A2=A1.derive(if(interval@m(Request_Date,pdate@m(Received_Date))>12,"old","new"):status) derive function is used to add a computed column. interval function calculates the time between two dates. pdate function gets the first date of the current month; its @m option means the calculation is measured by the month. After that you return the result to Jasper. Details can be found in http://blog.raqsoft.com/?p=2447 .

To calculate the opening balance according to the amount of deposit and withdraw, you need to use the inter-row calculation which can be handled through Jasper expression. But the code is complicated and difficult to write. You can use esProc to work with Jasper and thus make this simpler. The esProc code is as follows: A1=file("D:\data.csv").import@t(;",") A2=result (t=43,A1.derive(t=t+Inputs-Outputs:Balance)) See code explanation from http://blog.raqsoft.com/?p=3540 .

Loop operation and inter-row operation are needed when calculating payment for each loan term according to loan amount. But the code is difficult to write using the stored procedure or Scriptlets. You can use esProc to assist Jasper in doing this: A1=myDB1.query("select * from loan") A2=A1.derive(Rate/100/12:mRate,LoanAmt*mRate*power((1+mRate),Term)/(power((1+mRate),Term)-1):mPayment) A3=A2.((t=LoanAmt,Term.new(A2.LoanID:LoanID, A2.LoanAmt:LoanAmt,A2.mPayment:payment, A2.Term:Term,A2.Rate:Rate, t*A2.mRate:interest, payment-interest:principal, t=t-principal:principlebalance))) A4=result A3.conj() Code explanation is available from http://blog.raqsoft.com/?p=3531.

The problem involves inter-column calculation which Jasper has some difficulty in dealing with. Suggest using esProc to first prepare the data source and calculate and the growth rate: A1=mydb.query("select * from store order by item,year") A2=A1.group(item).run(A1.record(["Growth Rate",item,~(2).quantity/~(1).quantity-1])) Code explanation is available from http://blog.raqsoft.com/?p=3517.

The problem has two cases. One is that a report connects to several data sources at the same time. The other is that a report can connect to different data sources based on different values of the parameter, only one source at a time. Both are hard to handle directly with Jasper. But you can use esProc to assist the job. For the first case, you can refer to http://blog.raqsoft.com/?p=2546 . For the second one, see http://blog.raqsoft.com/?p=2528 .

You can use the user-defined data source function in Jasper to switch among data sources dynamically according to the parameter. This approach requires creating a JAVA class. Try using esProc to help with Jasper if you feel this is too hard. Please refer to http://blog.raqsoft.com/?p=2528 .

The calculating of difference between the current and the next/previous record is inter-row calculation, for which Jasper has little support. It’s quite difficult to handle it using Jasper. In this case you can try esProc, in which the next record can be represented, for instance, by amount[1] and thus the difference between it and the current record is amount-amount[1]. In this manner the growth rate between two records can be represented by (amount-amount[-1])/amount[-1]. Jasper can connect to an esProc script through JDBC and calls it in the same way as that in which it calls the stored procedure. See http://blog.raqsoft.com/?p=2918 for more details about the topic.

This task of report creation is difficult because the layout of the source data is not what the report requires. But we can first reorganize the source data through the union of “rows of all the required KPIs” using SQL according to the requirements. The reorganized data are as follows: Then we will transpose these reorganized data for report display. Thus the difficulty will be significantly reduced. We can use esProc to handle transposition of the SQL result set of any format. The following provides the universal esProc code to do this: Jasper can connect to esProc script through JDBC. It calls the script in a same way as that in which it calls the stored procedure. For more details, see http://blog.raqsoft.com/?p=2903 .

Jasper supports vertical data layout with multiple columns. But you want to layout them horizontally and orderly in three columns, which Jasper doesn’t support. Yet you can use esProc to help this. With the following code, the SQL result set with 3 fields can be rearranged and combined horizontally in 9 fields. What Jasper will do is simply to display the result. Jasper can connect to esProc script through JDBC. It calls the script in a same way as that in which it calls the stored procedure. For more details, see http://esproc.blogspot.com/2015/02/esproc-assists-report-development_10.html .