8 Replies Latest reply: May 26, 2011 3:45 AM by Kaushik Solanki

# how to aggregate data using dynamic set of selection

Hi Friends,

I want to calculate month wise opening balance from the following table.

Product       Day            Qty      Tag

1                 01/04/2010  20       O

2                 01/04/2010  25       O

3                 01/04/2010  10       O

1                 01/04/2010  51       T

2                 01/04/2010  50       T

3                 01/04/2010  75       T

3                 01/05/2010  40       T

2                 01/05/2010  32       T

1                 01/05/2010  11       T

1                 01/06/2010  15       T

2                 01/06/2010  20       T

3                 01/06/2010  19       T

O=Opening balance

T=Transaction during the year

OB=Opening balance

From the above transaction table how to make a report in the following format:

Month       OB       Qty       Total

Apr            55       176       231

May         231         83       314

June        314          54      368

plz help me out.

regards

chandan

• ###### Re: how to aggregate data using dynamic set of selection

Hi,

Have a look at the attached Document

Regards,

Kaushik Solanki

• ###### how to aggregate data using dynamic set of selection

Hi Kaushik,

Great Solution. I was trying to achieve via looping in edit script but didn't get success.

Thanks.

Regards,

Sachin A.

• ###### Re: how to aggregate data using dynamic set of selection

Hi,

So that other user know whats the right ans.

Regards,

Kaushik Solanki

• ###### Re: how to aggregate data using dynamic set of selection

I m not the starter of this topic so that option is not visible for me. Let the correct person do the honour

Thanks.

• ###### how to aggregate data using dynamic set of selection

Hi Koushik,

Thank you very much for the solution.Will you please explain how is it working internally ?

chandan

• ###### how to aggregate data using dynamic set of selection

Hi,

You wanted this output

Month       OB       Qty       Total

Apr            55       176       231

May         231         83       314

June        314          54      368

Now the field Qty can be obtain with the expression as

sum({<Tag = {"T"}>}Qty) which says give me sum where tag is T. So in the month od Apr you have Tag as 'O' also which will be skipped in this expression so you get only 176 which is a sum of qty when tag is T.

Now for OB you need first value as 55 which is the sum of qty when tag is O. and then from second row ownward you need 55 + 176 = 231.

So i used below expression.

if(RowNo() = 1,sum({<Tag = {"O"}>}Qty),Above(Qty)+Above(OB))

Means when the RowNo = 1 then give only the sum of qty where Tag is O else take first Value of Qty and OB and add them.

Hope you understood the way of working

Regards,

Kaushik Solanki

• ###### how to aggregate data using dynamic set of selection

Hi,

You mean the RowNo() = 1 indicates the first row of the output table.

regards,

chandan

• ###### how to aggregate data using dynamic set of selection

Hi,

Yes you are right.

Regards,

Kaushik Solanki