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  
 
 [ 3 post ] 

 Relevant Pages 

1. Calculations on Group Summaries

2. Use Report Totals in Group Calculations???

3. Report containing random calculations outside of groups

4. Multi-Level GROUP BY clause not allowed in a subquery

5. Code for Custom Grouping Level in Report

6. How to Reset the Pages Number on Group Level in a Report

7. Deleting a group level using code

8. change group levels in subrpt

9. Group level

10. Help: DataEnvironment Multi-level Grouping

11. How do you build multiple grouping levels

12. Dynamically assign grouping levels

 

 
Powered by phpBB® Forum Software