nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Subreporting and database access

newbie321

Hi, 
 
When creating sub-reports using list/table combo with dataset parameters, does the dataset bound to the table call the database only one time or every time a new parameter is obtained? 
 
For example:
Suppose that I have a list 'A' that contains the set of unique ids, let's say 1,2,3...N
In the detail of the list 'A', I have a table with the query: 
<pre class="_prettyXprint">
'select A, B from XYZ where id=?'</pre>
in the table's parameters I have a parameter definition, let's say itemID | integer | ..
and the table's dataset parameter (itemID) is bound to the list's detail id
 
Does this mean that:
- as the list element cycles through the ids (1,2,3...N), the datasource is going to be accessed N times by the dataset bound to a table? 
 
If this is so, then it is somewhat 'expensive' to access the database every time data is needed. 
 
Are there any ways to pull the entire dataset and create subreports by applying the dataset filter that would change at each list's detail iteration and be equal to the value of the current id in the list detail?
 
Thanks!!

Find more posts tagged with

Comments

micajblock

One query for each sub-report. Which is why best practice is to avoid sub-reports. Is the sub-report a different database? If yes, why not try and use joined data sets? If no, is there a reason you cannot build one data set?
Unfortunately the practice of using sub-reports is due to limitations of other tools.

newbie321

Thanks for a clarification regarding the number of queries per sub-report question. 
 
just to follow up: can I pull the master dataset (so to speak) and then use a filter whose value changes as the content of the list detail change? dynamic filter? 
 
thanks!

micajblock

Yes you can. See attached design (look at the filter on the inner table). My question still remains - why do you think you need a sub-report?

newbie321

Thanks a lot for the sample file and a pointer on row._outer() call!! My hunch was telling me it is possible, but I had absolutely no idea about the row._outer(). Very very helpful!! 
 
Regarding the sub-reporting ... my dataset has multiple levels of hierarchy and subreporting seems to be a natural way recycle the master dataset while showing only relevant data for a given level. 
 
I suppose I can achieve very similar effect using the grouping on a table and customizing the group headers, etc but at the time I was inclined to do list/table combo as a subreporting solution. Note: list/table combo was not ideal since it makes tons of db calls (per previous discussion) 
 
Now that I think about these things more, I am curious:
- What are the standards implementations in BIRT that achieve sub-reporting effect and feel? 
 
Thanks again!!

micajblock

It depends on that you mean by sub-report effect. Can you provide a screenshot?

newbie321

Attached is the sample screenshot of hypothetical sub-reports. Looking at the screenshot, we see grouping where for each country there will be x number of states, and for each state there will be y number of cities and for each city there will be z number of streets. 
 
Thanks!

micajblock

I still do not understand why you need sub-reports? It is MUCH more efficient to use a grouping reports.