Include subtotal of other data in a transaction report??

[cottontop]

universe1 accountnum, name, cableboxnum, active(y/n)

universe2 accountnum, date, trans_amt, trans_type

Info: accounts can have multiple active or inactive
cableboxes
trans_type, "W","E","T","H"
There could be only 1 "W" type for an account

Question: How can I display the total number of active (or inactive) cableboxes on a "W" type transaction report? The account shold only list once on this report.

Something like:
Accntnum active_boxes Date trans_amt Trans_type
12345 4 06/22/03 -250.45 W
55431 2 06/22/03 -50.00 W

1. Can it be created in WEBI? How?
2. What's the easiest way in Full Client?

Thanks a bunch!

 

[cottontop]

Sorry!

That's a different class within a universe!

Can I subtotal within one class, list that subtotal while listing something in another class.

Thanks,

 

[skrandel]

If it's all in the same universe, you should be able to do anything.

The hard part is that you have accountnum in multiple classes. You really should have a lookup table so that you can have only 1 accountnum object.

Then life would be easy.

Steve Krandel
BASE Consulting Group

http://www.baseconsulting.com

skrandel@baseconsulting.com

 

[Subhashab]

hi,
the query on your first universe should be made to return only active boxes using the condition area in the query panel. active = 'Y'

similarly the second query should be restricted to being only W trans type. type = 'W'

So the two queries have one to many relation on acctnum : one record in the second query and multiple record in first for the same person.

make the link between the two query on acctnum using the Data View option.

now make a table from existing data and select acct num , date, amt , type from query 2 (q2) and boxnum from query 1 (q1).
break in the acctnum field and add break footer. in the summary column of boxnum insert the following formula
= count(<boxnum>)
in rest of the column since the values are same for all row a cut and paste in the summary row will do. using the format->break option fold the break to display only the footer.