Calculations @ the Group Level
Author Message
Calculations @ the Group Level

I am trying to produce a report that groups financial accounts and does
interim math.  For instance:

Grp1    Grp2    Account    Amount
1            1        abc            1000
1            1        def             2000
2            1        gh1            300
2            1        hhh            300
2            2        yyy            400
2            2        zzz            300

My report would look like the following

Revenue (Grp1)
abc                1000
def                 2000
Total Revenue    3000

Expenses (Grp1)
Grp2.1 Category  Name
gh1            300
hhh            300
Total Grp2.1  600

Grp2.2 Category  Name
yyy            400
zzz            300
Total Grp2.2 700

Total (grp1)    1300

Net Income        1700

I am fine until I get to the bottom section (net income).  I have several
areas in my report where I need to calculate interim totals.

All my data is coming from a single table.  I initially tried to get each
section in a subreport and then do my interim calculations based on the
subreport totals.  This worked until I got "Unable to access first record
errors".

Any suggestions would be greatly appreciated.

Wed, 12 Nov 2003 18:36:03 GMT
Calculations @ the Group Level
Hi,

To keep a running total of net income I would use a formula field with a
Global variable, something like:

Global NumberVar NetIncome;
If {Table.Grp1} = 1 Then
NetIncome := NetIncome + {Table.Amount}
Else
If {Table.Grp1} = 2 Then
NetIncome := NetIncome - {Table.Amount};
NetIncome

If this is inserted into the footer of a group, it should give you the value
of NetIncome at that point.

Regards,

John....................

Quote:

> I am trying to produce a report that groups financial accounts and does
> interim math.  For instance:

> Grp1    Grp2    Account    Amount
> 1            1        abc            1000
> 1            1        def             2000
> 2            1        gh1            300
> 2            1        hhh            300
> 2            2        yyy            400
> 2            2        zzz            300

> My report would look like the following

> Revenue (Grp1)
>     abc                1000
>     def                 2000
> Total Revenue    3000

> Expenses (Grp1)
>     Grp2.1 Category  Name
>         gh1            300
>         hhh            300
>     Total Grp2.1  600

>     Grp2.2 Category  Name
>         yyy            400
>         zzz            300
>     Total Grp2.2 700

>   Total (grp1)    1300

> Net Income        1700

> I am fine until I get to the bottom section (net income).  I have several
> areas in my report where I need to calculate interim totals.

> All my data is coming from a single table.  I initially tried to get each
> section in a subreport and then do my interim calculations based on the
> subreport totals.  This worked until I got "Unable to access first record
> errors".

> Any suggestions would be greatly appreciated.

Thu, 13 Nov 2003 01:52:02 GMT
Calculations @ the Group Level
What you are really trying to do is to add Debits and Credits where they are
both stored in the database as positive numbers.  Some Accounting systems do
it this way, while others store one (usually Credits) as a negative number.
There should be a  field for Sign (Dr/Cr) etc, so use that to get your total
around the right way.  Because as well as Income and Expenses, you might
also have Assets and Liabilities in your Db as groups.

Quote:
> Hi,

> To keep a running total of net income I would use a formula field with a
> Global variable, something like:

> Global NumberVar NetIncome;
> If {Table.Grp1} = 1 Then
>    NetIncome := NetIncome + {Table.Amount}
> Else
> If {Table.Grp1} = 2 Then
>   NetIncome := NetIncome - {Table.Amount};
> NetIncome

> If this is inserted into the footer of a group, it should give you the
value
> of NetIncome at that point.

> Regards,

> John....................

> > I am trying to produce a report that groups financial accounts and does
> > interim math.  For instance:

> > Grp1    Grp2    Account    Amount
> > 1            1        abc            1000
> > 1            1        def             2000
> > 2            1        gh1            300
> > 2            1        hhh            300
> > 2            2        yyy            400
> > 2            2        zzz            300

> > My report would look like the following

> > Revenue (Grp1)
> >     abc                1000
> >     def                 2000
> > Total Revenue    3000

> > Expenses (Grp1)
> >     Grp2.1 Category  Name
> >         gh1            300
> >         hhh            300
> >     Total Grp2.1  600

> >     Grp2.2 Category  Name
> >         yyy            400
> >         zzz            300
> >     Total Grp2.2 700

> >   Total (grp1)    1300

> > Net Income        1700

> > I am fine until I get to the bottom section (net income).  I have
several
> > areas in my report where I need to calculate interim totals.

> > All my data is coming from a single table.  I initially tried to get
each
> > section in a subreport and then do my interim calculations based on the
> > subreport totals.  This worked until I got "Unable to access first
record
> > errors".

> > Any suggestions would be greatly appreciated.

Fri, 14 Nov 2003 05:52:59 GMT

 Page 1 of 1 [ 3 post ]

Relevant Pages