Rank 1
I have a table called Components that is hierarchical; each item has a ParentId that is either NULL if it's a top-level item, or populated with the Id of its parent Component. I also have a table called Requirements with an OwningComponentId field, which connects each one to a Component.
Unfortunately, there is no limit to how deep the tree can go, nor is there a limit to how many siblings an item can have.
My report needs to be able to select a Component (ideally any component, not just top-level) as a parameter, and display aggregates for it as well as each of its children, on down to the leaves. One thing I do know is that only the leaves (Component with no children) will actually have Requirements associated with them. For the rows of the report showing the leaves, it should show the aggregates for that leaf. For each parent row, it should show the aggregates for all that parent's children.
I've seen some other threads, such as http://www.telerik.com/forums/cross-tab-report-with-a-self-referencing-hierarchy . I was able to get that example to work. However, that one does not show any aggregates or detail rows for each item, and I can't seem to figure out how to add those in in the way I need.
At one point I had a query that returned all the rows with aggregate data for the leaves (still didn't have aggregates for parents) computed directly on the database, but for some reason the report would repeat the entire set of data once for each of my Components. If there were say 125 components in the hierarchy, then the entire table was repeated 125 times. I also realized from monitoring the db that for some reason the report was calling the same stored proc on the db 126 times (once and then once again for each component). I have a feeling I had something wrong with my grouping but couldn't figure it out. Is there some logic in there that says to call the stored proc once to get the Group items, and then call it again for each group? I saw no such logic in my DataSource definition (which by the way is a SqlDataSource calling a Stored Proc that I wrote).
I would greatly appreciate any help you can give on how to do this. I've been working on this for a few weeks now with little progress, and am at the end of my rope!
Chris
1 Answer, 1 is accepted
Let us start from the example in Cross tab report with a self referencing hierarchy. The example originates from How do I displaying recursive data?
- The layout is based on a Band Report that loads itself in a SubReport item. You can add more sections and use the built-in aggregate functions to add totals for the current set of data. You can also update data to expose fields returning the calculated totals.
- The SubReport item is configured in code via binding to the SubReport.ReportSource property and user function, to avoid stack overflow exception in the designer due to the self reference, and to manage data for the sub report.
- The report uses an ObjectDataSource, where sub reports load data through the collection properties of the main business object - Use DataObject as a datasource for nested data items (Table, List, Crosstab, Graph)Steps:. The idea is to avoid retrieving data for each sub report.
In general, each DataSource property set to a data source component will cause the data source component to execute its data retrieval method on its own, no matter if the retrieved data is the same as for other sub report.
Please check the modified project in the attachment. It uses Telerik Reporting R3 2016 SP1 v10.2.16.1025, that can be changed by running the Upgrade Wizard. Note that this is only an example, and should not be used as an end solution.
Regards,
Stef
Telerik by Progress