Nested grouping with multiple datasets

tupe

<p>Hi experts,</p><p> </p><p>I have next to none experience what comes to BIRT reports. I'm trying to create a multilevel report to list item structures.</p><p> </p><p>Few questions:</p><p>I wrote sql query where each structure level are combined as one result set using UNION.</p><p>Totally 9 different queries. One for the item and 8 for the structure. Each query returns PARENT_CODE and STR_CODE. These can used for the joins.</p><p> </p><p> </p><p>What is the best way to accomplish this?</p><p>Should I split the query to multiple datasets?</p><p>How to join the datasets together so that STR_CODE is linked to PARENT_CODE?</p><p>If I do the datasets can I add some code that prevents executing all the structure queries if upper structure returns no value. These are quite heavy queries so in order to have good performace this would be great.</p><p> </p><p>Please see attached as an example of wanted output. Is it possible to hide columns if no data exists?</p><p> </p><p> </p><p>Example report would be appreciated.</p><p> </p><p>I'm using birt-rcp-report-designer-4_2_2</p><p> </p><p>Br,</p><p>Tupe</p><p> </p>

GLO_FR

<p>Hi,</p><p> </p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">I'll try answer you question point by point</p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"><em>> What is the best way to accomplish this?</em></p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">For you example, if you know the number of childs (always 8 ?), a simple predefined table element should works. If not, maybe using a crosstab should be a better solution.</p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"><em>> Should I split the query to multiple datasets?</em></p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Yes or no. depends on your business case</p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"><em>> How to join the datasets together so that STR_CODE is linked to PARENT_CODE?</em></p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">To join dataset, create a joined dataset (union, left join, right join....)</p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"><em>>> If I do the datasets can I add some code that prevents executing all the structure queries if upper structure returns no value. These are quite heavy queries so in order to have good performace this would be great.</em></p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Yes. You can dynamicaly overrde a query with code. The think is, if one dataset is empty, how will you manage the link between datasets ?</p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"> </p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;"><em>>> Is it possible to hide columns if no data exists?</em></p><p style="color:rgb(40,40,40);font-family:'Source Sans Pro', sans-serif;">Need some simple coding but yes</p>