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; } } }

Main-Subreport-Join with filter-conditions

Mario1710

Hello!

I have to transfer a crystal report to birt.
Between the mainreport and the subreport of the crystal-report there the following conditions:

if {?Pm-FUNDS.FUNDSTRUCTTYPE} <> 4 then
{FUNDS.FUND} like left({?Pm-FUNDS.FUND},7) & '*' and
{GLHOLKEYS.ACCIK} in [1]
else
{FUNDS.FUND} = {?Pm-FUNDS.FUND} and
{GLHOLKEYS.ACCIK} in [2]
and
IF {funds.fundstructtype} = 2 THEN
{glaccrepfc7.glaccrepfc7} <> 'BR_HN_FONDSZE'
AND {glaccrepfc7.glaccrepfc7} <> 'BR_HB_FONDSZE'
ELSE
TRUE;

Description:
{?Pm-FUNDS.FUNDSTRUCTTYPE} and {?Pm-FUNDS.FUND} are the joins between main- and subreport.

I have build the mainreport and the subreport and joined them. But now i need the conditions to filter the datasets in the subreport.

How can i transfer these conditions to birt?

Thanks!

Mario

Find more posts tagged with

Comments

mwilliams

Hi Mario,

You should be able to filter your embedded sub-report based on the value from the outer table or list. You should be able to do this with the expression builder for the filter on the filter tab of the property editor of your inner table. Let me know if I'm not understanding what you're trying to do, correctly.

Mario1710

Hi Michael,

thanks for yout answer.
I try it, but i think, the position to integrate the conditions is in the "dataset binding".
In the mastereport i have a fund (i.e. 010000100). With this value i go to the subreport and i want so select all funds which have the first 7 numbers (i.e. 0100001). This could be 010000110, 010000120, 010000130, etc.

The resultset should look like:

mainreport subreport
010000100 010000110
010000100 010000120
010000100 010000130

Regards

Mario

mwilliams

Mario,

I'm assuming you're using 2 separate dataSets. Can you post some sample data from each dataSet and how you're wanting it to look. I'll then take your sample data and try to reproduce it in a report design. Thanks.

rpolunsky

I would create computed columns in both the outer and inner table datasets consisting of the first 7 characters of the account number. Then you can filter your inner table as inner.first7chars = outer.first7chars.

That might not be the most efficient in terms of performance but I think it will work for you.

Mario1710

Hi!

In the attached file you see the sample data and the expected Master-Sub-Report.

Any ideas to solve the problem?

Regards
Mario

mwilliams

Mario,

There are several ways in which you could do this. I'll go over a couple. One uses both dataSets and the other uses only the inner dataSet.

For both dataSets, you'd first put the outer dataSet items in a table and group by fund. In the group footer, you'd merge the cells and place the subreport table. You'd then filter it with two conditions:

row["fund"].substr(0,7) equal to row._outer["fund"].substr(0,7)
row["fundstructtype"] equal to row._outer["fundstructtype"]

This should limit the inner table to what you're wanting. You'd just have to add an aggregation to sum your values.

For just using the inner dataSet, you could just add a groouping to the table and group on row["fund"].substr(0,7). Then, add another group for fundstructuretype. You would then place all of your data elements in the fundstructuretype group header and add an aggregation in place of the "value" fleld that adds up the values over the fundstructure type group.

If you need an example of either/both of these or have a question, let me know.

Mario1710

Hi Michael,

to use the filter was a very good hint! Thanks!

Now i use this two routines in the filter-tab of the subreport:
The example that i decribed, was a little bit easier.

{
if
(row._outer["FUNDSTRUCTTYPE"] != 4)
{
row["FUND_07_HB"] == row._outer["FUND_07_NB"] && row["ACCIK"] == 1
}
else
row["FUND"] == row._outer["FUND"] && row["ACCIK"] == 2
}

{
if
(row._outer["FUNDSTRUCTTYPE"] == 2)
{
row["GLACCREPFC7"] != 'BR_HN_FONDSZE' && row["GLACCREPFC7"] != 'BR_HB_FONDSZE'
}
else
true
}

It works fine, but the performace ist terrible. Until now, i used nearly used sql-routines and put the final-datasets in my reports and they were very fast.
But with this Master-Sub-Filter-Condition the report is very slow.

Any hints to speed-up the report? Maybe with Script?

Regard,
Mario

mwilliams

Mario,

If you had dataSet parameters in the inner query and could use dataSet parameter binding feature on the binding tab for the inner table, you could query just for the values needed for that subreport using substrings in your SQL and for your parameter to check for the first 7 values, passing the data from the outer table to the inner table's query. Then, you wouldn't need to use slow filters.