displaying data in the report

[unknownly]

Hi All,

I have the sample data below. How can I display the report some what like match

bu aff amt on left side of the report and bu aff amt on the right side( when the aff reports to bu with same unit)

sample
BU Account aff Rule ID Amount

12345 BS12345 I90060 test1 2000 (show this on left side of the report)
I90060 BS12345 12345 test1 -2000(show this on right side of the report)

I couldn't explain better than this. Hoping I can get some help on this.


Thanks,
Sweetie

More sample data below





BU Account aff Rule ID Amount

12345 BS12345 I90060 test1 2000
12345 BS12345 23055 test1 5000
23055 BS8908 12345 test1 -5000
12345 BS7856 23055 test1 -3000
12345 BS7829 23055 test1 -4500
12345 BS7682 76878 test1 34000
12345 BS12345 76878 test1 43200
76878 BS12345 12345 test1 -10000
76878 BS12346 12345 test1 -24000
12345 BS88 76878 test1 -646
12345 BS12345 324234 test1 2000
I90060 BS12345 12345 test1 -2000



BU Account Aff Amount BU Account Aff Amount

12345 BS12345 I90060 2000 I90060 BS12345 12345 -2000
Sub total I90060 2000 -2000

12345 BS12345 23055 5000 23055 BS8908 12345 -5000
12345 BS7856 23055 -3000
12345 BS7829 23055 -4500
Sub total 23055 -2500

12345 BS7682 76878 34000 76878 BS12345 12345 -10000
12345 BS12345 76878 43200 76878 BS12346 12345 -24000
12345 BS88 76878 -646
Sub total 76878 76554 -34000

12345 BS12345 324234 2000
Sub total 324234 2000

 

[synapsevampire]

Try adding the table in again and join the bu to the aff.

Group by the bu, and you should now be able to display all of the BU and it's associated AFFs.

To get the sum, you'll want to sum the details of the Aff joined table, and then add the total of the BU table amount to that in the group footer:

sum({tableaff.amount},{tablebu.bu})+{tablebu.amount})

-k

 

[unknownly]

Hi Synasevampire,

I am trying to do as you said
I have all the data coming from one table which results 9 rows
I joined the same tbl again and joined with other key which results with 31 rows.

So I joined as you told a.bu = b.aff which results with 7 rows. 2 rows are missing which I will need to display in the I have tried outer joins to bring back the 2 missing rows.
Can you please advice what else can I do

Thanks