Load result of a crosstab query into a foundset

Classic Servoy
1

Hi

I am currently running various cross tab queries via getDatasetBy query, and displaying the results in an eastwood chart.

My question is this,

As it is only foundsets that are limited by filter params, Is there a way to take the result of my cross tab and create a foundset from it even though the resultant colums do not represent fields in the DB rather aggregations and calculations of the DB data, so that i can utilise the current table filter params.

Regards

2

As an addition to this post - and after reading a response by David Workman posted on another thread we could use a little clarification!

As mentioned by McCourt, since we’ve built some fairly complex crosstab reports in Servoy (not Jasper) using the getDatasetByQuery() methods, and now realising that the tableFilterParams will only work on a foundset we need to clarify a few points.

  1. Originally we believed that other than rawSQL all data handling in Servoy would be filtered with the setTableFilterParams - can we confirm that this is not the case and the filters ONLY work at the foundset level?

  2. With Davids post today (http://forum.servoy.com/viewtopic.php?f=22&t=14686) he demonstrated a few ways to manage the use of datasets and foundsets. Where we have used datasets (buit with getDataSetByQuery) for these reports - is it feasible to put them into the foundset of the report (using foundset.loadRecords() rather than using controller.loadRecords() for example) and have the resultset filtered by the setTableFilterParam()?

  3. What strategies do others use for similar situations where complex data needs to be structured and calculated yet filtered through the setTableFilterParams?

Appreciate feedback on this please folks as it’s a pretty big influence on how we continue - and changes we need to make rapidly.

3

Hi Ian,

  1. table/foundset filters are respected by Servoy as soon as Servoy takes care of the query.
    this is not the case when firing sql via the rawSQL plugin or the getDatasetByQuery() function. (both can be seen as custom queries)
    As soon as you load the result of your getDatasetByQuery() onto a form or foundset, Servoy will fire a query respecting the filters.

  2. this depends on your actual query result. Keep in mind that the first column of the dataset has to be a pk. If not you can convert a column with id’s to a new dataset and use that.
    Anyway the foundset result won’t contain any foreign table data.
    Keep in mind foundsets can be created in memory, which can save resources.

  3. we almost only use filtering for tenant_id and useraccess, we’ve stored these rules in a global object so we can easily recall the settings and apply the rules when it comes to doing custom query. Anyway, you have to manually add your filters to a custom query.
    When creating cross table overviews we either use:
    a) a dataset which will be converted into a datasource and added to a form which is build with the solutionModel
    b) a dbView which will show up as a seperate table in Servoy. (Please be aware of the rules when you play this game)
    c) a html table displaying the result.

Hope this helps

4

Thanks Marc - we are digesting your feedback - looks like we will take the route of adding the ‘getSQL()’ filters to our custom queries for the time being, and figure out a better way forward for the next iteration. All of our charting is affected by this filter requirement ( we made the mistake of assuming how that would work and left the large filter requirements until late in the day!)

Ho Hum - on we go - thanks Marc.

5

Probably easier to use getTableFilterParams(servername) or getFoundSetFilterParams(foundset) instead of getSQL() to figure out the extra stuff you need to add to the end of custom queries.

6

Hi

We are currently using the previously suggested method of getTablefilterparams and adding them to our custom queries.

We have however encountered a bug for which i have put a case in.

When recalling filter params that were built using an IN clause.
The rerun value for getTablefilterparams returns in the wrong format

[component_detail, cd_id, in, [Ljava.lang.Object;@15dfcc5, tfpFilterComp]

So we get a java lang instead of a list of PK’s

Can anyone offer advice as to a workaround for this, as it is pretty much fundamental to our development to be able to apply filters built in this way to our custom queries.

Regards

7

is that not an array in an array?
so something like

tableparam[3][0] == first pk ? (3 == the value of the in)

8

Hi

So are you suggesting that I need to loop through this inner array and piece it back together into a string?
If so then that would work fine for us

Regards

9

i think so, because what you print is a toString() of a java array.