We are currently in the process of designing a report  from a table that has the following sample data

 

Bus Unit      Affiliate         Amount

-----------     ----------       ------------

 

  01               02                2500

  01               03                2330

  01               04                  40

  02               01               -2500

  02               03                 -223

  03               01                -400

  03               02                 540

  04               03                 650

  04               06                  100

 

and so on....

 

In other words, the BU/Affiliate combinations do not follow any specific pattern.

 

We are tyring to present the data in the following way in the report

 

Bus Unit      Affiliate            Total

----------      ----------           --------

 

 01               02                  2500

 02               01                 -2500

                                        --------

                    Subtotal:             0

 

01               03                   2300

03               01                    -400

                                        --------

                    Subtotal:       1900

 

01               04                   40

04               01                     0 ( because combination not defined in table)

                                        ---------

                     Subtotal:       40

 

 

 

02              03                     -240

03              02                      540

                                     -     -----

                   Subtotal:          300

 

and so on.............

 

 

In other words, the opposite Bus Unit/Affiliate combinations rows should be following each other.

 

If someone could provide suggestions about ways to accomplish this, we'd be very thankful.

 

Thanks again

 

 

 

01     03             1000

03     01              -200

 

       Subtotal      800

 

Can you please elaborate? Thanks

 

Here is what I did:

- Used the following SQL:

select BU as "Entity", 'A' as "Type",Affiliate as "Entity2", Amount
from Mytable
union
select Affiliate as "Entity", 'B' as "Type", BU as "Entity2", Amount
from Mytable

This results in the following sample data:

Entity    Type     Entity2   BU     Affiliate   Amount
------    ----     -------   ---    ---------   ------

GL01        A       GL58     GL01      GL58      -500
GL01        B       GL58     GL58      GL01       500
GL58        A       GL01     GL58      GL01       500
GL58        B       GL01     GL01      GL58      -500

In the report, when I group by Entity, and then by Entity2, I'm getting duplicate rows in the following manner:


Group by GL01
  Group by GL58

          BU        Affiliate       Amount
          --        ----------      -------

          GL01        GL58           -500
          GL58        GL01            500
                                     -----
                           TOTAL:      0



Group by GL58
   Group by GL01

          BU        Affiliate       Amount
          --        ----------      -------

          GL58        GL01            500
          GL01        GL58           -500
                                     -----
                           TOTAL:      0



The "Group by GL01,then by GL58" is exactly what I want. However, this also creates the "Group by GL58,then by GL01"
rows, which are basically duplicate rows, and I dont want them to be displayed. Is there a way to achieve this?

Thanks again