getDataSetByQuery calculated field to form

Classic Servoy
1

Hi folks

I am struggling with the conceptual side of use getDataSetBQuery into a) a form where I can add drill down, and b) from the form into a report.

My Dataset must be a " group by’ statement joining multiple tables. I get data ok, and can place it in the controller. This works because my group by is the same as the main table and have pk. (may not be in other cases). Filed common to the man table in the controller display correctly.

My dataset contains calculated fields a*b = c. My problem is… How do I get C to display in the form as I cannot set the “dataprovider” programmatically?
I realize I could create calculated fields in the table with the same names, but this can’t be best practice.

Thanks Mark

2

HI Mark,

So have I got this right?

  1. You have got a dataset using your own SQL that includes a “Group By” in the statement ? and you want display these results on a servoy form.
  2. Then drill down into each group and see individual records on a different form ?
3

Hi Mark,

First, using a DataSet to load a form is not very efficient. Essentially you are loading the data twice. First all records of the WHOLE dataset are loaded into the DataSet…and then you pass it to the form controller (which only wants/needs a PK) and fetches the shown form fields for you (thus loading data twice).
The best way to load data using custom SQL in a form is to pass the SQL straight to the controller using controller.loadRecords(SQL, Parameters).

Now Servoy has it’s own aggregate objects where you can define sum/count/avg/etc. over a table column. You can then place these on specific form parts (summary/sub-summary/etc) and Servoy will fetch the data accordingly. But these aggregates only work over table columns, not over un-stored calculations.
But it sounds to me you want more dynamic reports. For such dynamic reports you might want to look at Velocity Report or Jasper Reports which are more suited towards this.
If you don’t want to use any plugins then then you could also go the solutionModel route and create reports out of dynamic forms yourself by looping through your dataset(s) and create/place labels with the result accordingly (kinda) in the same way I did with the Gantt module. But this approach might be more work then using one of the aforementioned plugins.

Hope this helps.

4

Hi Phillip
Thanks for the response… Here’s some more info.

Almost correct. The problem as to why I can’t do them is that I can’t figure out how to display a calculated field from the Getdatasetbyquery on a form because I can’t tell the form field(control) to attach to the field I’ve created in my query. Once I can display it, then the other two parts should be easy.

here’s my code:

// FixEventz.eveid is pk
var lcQuery = “SELECT fixeventz.eveid, max(fixeventz.evename),sum(cosledg.ldgqty2*cosledg.Val5) as cal_empRev” +
" FROM fixeventz INNER JOIN cosledg ON fixeventz.eveid = cosledg.ldgeveid " +
" GROUP BY eveid ";

var vDataSet = databaseManager.getDataSetByQuery(globals.active_database, lcQuery, null, maxReturnedRows);
var lnx = vDataSet.getMaxRowIndex(); // Returns 5 rows

// load into controller
var DidLoad = controller.loadRecords(vDataSet); // Returns true
var lnx1 = controller.getMaxRecordIndex(); // Returns 5 lines

Fields eveid, evename are easy to display as are in the database, but cal_empRev I can’t get to display, because i can’t get it into the form fields dataprovider property.

regards Mark

5

Hi Robert

Thanks for our input. My query above is a cut down version excluding the date range, so expect record set to be 50 to 500 max records. Just to confirm I understand the double loading. My query will only query the database once to do the group select, which will be reducing possibly 5000 lines down to 50, so will be doing most of the work. The second load only involves the user interface getting 199 records at a time from the “temporary table” sitting in memory as the user pages down, or the report outputs. Do you use the RawSql plugin rather? Maybe you can give me a sample of how your code would be different to mine above.

I have the calculated fields working and have them set up as stored calculations, to get sub totals and totals, but seems to only work for a single table, and don’t allow grouping of data.

I am keen to learn how to use all the features of Servoy, before getting into Velocity and Jasper. Are there any videos on using Velocity reports?

Regards Mark

6

HI Mark,

There are a heap of tutorials here on both servoy and jasper

http://www.servoy.com/content.jsp?t=790&mt=393

Also have a look here, there are some free videos which may be of help.

http://www.servoyuniversity.com/

7

Hi Folks

Thanks for the input folks, but problem still not resolved. Still need some help to get a getDataSetByQuery to display in a form as a table view.

Thanks Mark

8

Hi Folks

I found a clue to my problem indirectly in the post “Trouble with ConvertToDataSet” posted by andres_achiary March 2009.

THe following code allowed me to get my calculated fields data in a GROUP BY query spanning multiple tables, and place it in a newly created form which gives me the grid with sort functionality etc. I now just need to add the form back to my existing forms tabpanel. The solution may not be best practice, but does the job. Saves creating calculated fields in database. Hope it’s of use to someone.

Here’s a simplified version of the code

//Start of working example using GROUP BY clause into grid on form

var maxReturnedRows = 20

// FixEventz.eveid is pk
var lcQuery = “SELECT fixeventz.eveid, max(fixeventz.evename) as evename, sum(cosledg.ldgqty2*1234.567) as balance” +
" FROM fixeventz INNER JOIN cosledg ON fixeventz.eveid = cosledg.ldgeveid " +
" GROUP BY eveid ";

var vDataSet = databaseManager.getDataSetByQuery(globals.active_database, lcQuery, null, maxReturnedRows);

var vResult = vDataSet.createDataSource(‘mydata2’,[JSColumn.INTEGER, JSColumn.TEXT, JSColumn.INTEGER]); // types are inferred from query result

history.removeForm(‘frm_test’)
solutionModel.removeForm(‘frm_test’)

var jsform = solutionModel.newForm(‘frm_test’, vResult, null, true, 300, 300);
jsform.newField(“eveid”,SM_DISPLAYTYPE.TEXT_FIELD,100,100,100,20)
jsform.newField(“evename”,SM_DISPLAYTYPE.TEXT_FIELD,200,200,100,20)
jsform.newField(“balance”,SM_DISPLAYTYPE.TEXT_FIELD,200,200,100,20)

jsform.view = JSForm.LOCKED_TABLE_VIEW
forms.frm_test.controller.show()

//End of test code

9

Mark,

Just for the record, you can simply get the same results when designing it with developer (in stead of scripting all) and without using any sql:

  • Create a relation fixeventz_to_cosledg
  • Create an sum-aggregate ldgqty2_sum=sum(ldgqty2) on table cosledg
  • Crate a calculation balance=ldgqty2_sum*1234.567
  • Create a form on table fixeventz (viewType = table-view), add the fields in form designer.

Servoy takes care of all loading/aggregates for you.

Rob

Btw, max(fixeventz.evename) in your query is not needed since you group on pk anyway.

10

Hi Rob

You are a genius. Thank you for explaining it in so simply and clearly. I understood all the calcs and aggregates and have been using them. What I never realized was that placing the aggregates via a relationship would result in a view that contained the summed fields. Very nice and tidy. Took a while to set up the 15 field combinations I needed to get all the data. I now also have the data available for other forms and reports. It’s great that Servoy has such a cool way to do this intrinsically.

Thanks again for your expert advice. Especially after 142 views I was getting a little worried.

Mark