OK, and there's a validation that prevents the configuration. The question is really if there's an alternative to solve the following within a report definition.
I have a three tables A, B, and C such that for a row in A there will be multiple rows in B which match the key and we want the most recent (sys update date is a key as well) from B for a record in A. C is such that there will, again, be several records and we only care about the most recent in relation to a given row in B. So, for the most recent A we want the most recent B and for that most recent B we want the most recent C. A single layer of sub-reports will allow us to select for the most recent B, but without multiple layers we're not able to support this within a single report definition.
Posted: 6 years ago
Posted: 20 Jul 2015 17:24 EDT
Matthew Morency (morem)
Principal Platform Security Engineer
I believe that have done similar things in the past using the Class Joins and Associations on the Data Access tab of a Report Definition rule. Someone from either the Casino Royale or DB Dynamos team should be able to provide better direction.
Let me share this with our development team and see if we can work it out with your idea. It's pretty reasonable and reflects Matt's suggestion above. So a performance question, as a follow up: I could make a data page for each level that returns the more atomic results and then assemble in a data transform within the (Grand)Parent data page. How is that compared to a report of all nodes (this join) rather than building the nodes up?
Database queries work well if the right indices are defined on the columns and you get less data into memory compared the data page approach. So it boils down to how many records are we talking about in each of these tables.
Right now, small, less than 50 records in most of the first pass queries. I think the combination of indices and the suggestion of how to structure the sub-reports above should let us work through the requirement we're trying to satisfy.