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 .