Conditional filtering

Hi all,

Our requirement is to filter some IDs which will be having Aggregate1='0' && Aggregate2='0'

But problem is that we cant use aggregated field in filter. We can't do this at database level beacuse we need all the columns along with ID in the report.

Please find the sample:

A | B | C | D | E | F

abb | 123 | 567 | 567 | $67.00 | $777.00
rrr | 345 | 456 | 895 | -$67.00 | -$777.00

$0.00 $0.00

If this Agg(E) && Agg(F)==0 Then this table should not display the two rows and should not be blank.It should filter the 2 IDs(567 & 456)

Please help me in this.

Thanks In advance
Sankari

Find more posts tagged with

Comments

Megha Nidhi Dahal

<blockquote class='ipsBlockquote' data-author="'Sankari'" data-cid="70841" data-time="1291119962" data-date="30 November 2010 - 05:26 AM">
Hi all, 
 
Our requirement is to filter some IDs which will be having Aggregate1='0' && Aggregate2='0' 
 
But problem is that we cant use aggregated field in filter. We can't do this at database level beacuse we need all the columns along with ID in the report. 
 
Please find the sample: 
 
 
A | B | C | D | E | F 
 
abb | 123 | 567 | 567 | $67.00 | $777.00 
rrr | 345 | 456 | 895 | -$67.00 | -$777.00

$0.00 $0.00 
 
If this Agg(E) && Agg(F)==0 Then this table should not display the two rows and should not be blank.It should filter the 2 IDs(567 & 456) 
 
Please help me in this. 
 
Thanks In advance 
Sankari </blockquote>
 
Sankari, 
 
Which field in the data identifies the relation between these records? As far as I can see in the above two records that you have posted, there are no matching ids or so between these two records. If your query is to hide the whole table if AggE == 0 && AggF == 0, then you can write an aggregation on the dataset. Later these summed up value can be compared to 0 to hide the whole table. 
If in case there exists such matching entries in these two records, then you can group it on that matching field and use the same technique, i.e. check if these two agg are 0. 
 
I have created a sample report answering both these questions along with input data and have attached with this post. 
 
For test.rptdesign, test.txt is the data file. This example illustrates your first condition, i.e. if there are no record identifiers, hide the whole table if the sum of both these column is 0. 
 
For test2.rptdesign, test2.txt is the data file. This example illustrates your second condition, i.e. if there is a matching identifier for these records. I have added an extra field G which will hold that id, based upon that field I have grouped the data and calculated the sum for the two columns. 
 
Please pardon if I have misunderstood your question. 
 
regards 
Arpan

Sankari

Hi Arpan,

Thank you for the valuable response.

Your understanding is correct but i want aggregation of one column based on some group.

In dataset we cant compute the values based on group created for a table.

Sample:

Source Table:

Name Age Designation Points

AAAA 25 PA 20,000
BBBB 35 Manager 70,000
CCCC 26 PA -20,000
DDDD 38 Manager -70,000
EEEE 22 Trainee 10,000

I am going to group the above table by 'Designation' field,so the resultant table is

Name Age Designation Points

AAAA 25 PA 20,000
CCCC 26 PA -20,000

Agg(Points)= 0

Name Age Designation Points

BBBB 35 Manager 70,000
DDDD 38 Manager -70,000

Agg(Points)= 0

Name Age Designation Points

EEEE 22 Trainee 10,000

Agg(Points)=0

If we are calculating aggregation(Points) in dataset means it will add all the 5 rows and it will give the value as 10000 so it wont hide the rows(AAAA,CCCC) && (BBBB,DDDD).

So i need to compute the agg(Points) in table level only.Then i need to filter the rows (AAAA,CCCC) && (BBBB,DDDD)because Agg(Points) for these rows are 0.

Please help me in this.

Thanks
Sankari

Megha Nidhi Dahal

Sankari, 
 
Please go through the second set of attachments above, i.e. test2.rptdesign and test2.txt. I have done something very similar to what you have said above. The first approach is not useful for you I understand. You can group the contents on the Designation column and have a sum on the Points column. Later you can hide the whole group if the points is summed to be 0. 
 
I have attached another set of designer and data file depicting the exact scenario that you have mentioned lately, here I have created a group on Designation column by name DesignationGroup and written a group level aggregation on the Points column by name TotalPoints. 
Since the aggregation is on the group, it doesn't sum up the whole table, it will calculate the sum only for that group. Later for the visibility of the detail row and the group footer row, I have added a condition to hide if row["TotalPoints"] == 0. 
 
I hope this helps. 
 
regards 
Arpan

Sankari

Hi Arpan,

Thank you for the good example.

Sorry i have missed one thing to say.Actually we need to give page break for each designation(Pls refer the image attached).

So if we are giving visibilty as row["TotalPoints"] == 0 means it will give blank page as attached.I want to eliminate that blank page too.

I think filtering is best than visibility option.

Please help in this.

Thanks
Sankari

Megha Nidhi Dahal

Sankari,

You can try out by removing the page break option in the group (the image that you had attached) and try including it in the group footer row. Things I learned with BIRT's formatting in past couple of months is that, it is trial and error. Some things work well in some places whereas fails in other places. That may be because of lack of understanding from my side.

I tried giving the page break(as shown in the attachment, gave it as Always for After) for the group footer and it worked just the way you want it.

Worth giving a try.

regards
Arpan

Sankari

Hi Arpan,

Thanks a lot for your timely help.

Its working

Thanks
Sankari

Megha Nidhi Dahal

<blockquote class='ipsBlockquote' data-author="'Sankari'" data-cid="70995" data-time="1291381799" data-date="03 December 2010 - 06:09 AM">
Hi Arpan, 
 
Thanks a lot for your timely help. 
 
Its working

Thanks 
Sankari </blockquote>
 
Glad to hear that