Summerizing Transactions

[bitsmith2k]

Hey All, I havent spent too much time trying this on my own yet, but i'm assuming i will run into trouble perhaps someone here would be able to help me out..

here's how my data relates:
contract
contractID
portfolioid

transactions
transid
contractid
transtype

transactiontypes
transtype
paymenttype

Here's my task..
we have a bunch of transactions that i need to report.. some transtypes for the transactions are marked as paymenttype = 1. these are the guys i want to report.

i want to use a crosstab to display the data ie:
contractid sum(transtype1) sum(transtype2).....sum(transtypeN)

no problem getting that info displayed.. is there a way to group data within the crosstab based on the portfolioid of the contract record while preserving all the columns?

i ask because if we add another transtype that is a paymenttype i want it reflected in the report without making any code changes?

any ideas how this can be done?

 

[synapsevampire]

Not exactly sure what you mean, but you can always create a group in the report and place the cross-tab within that group.

Or are you saying that you want to use the portfolioid instead of the current contract id for the rows?

Easy enough to change, or you can create a parameter and base a formula on the parameter, and then use the formula as the row in the cross-tab. The formula would then switch between customerid and portfolioid based on what the user selected.

-k

 

[bitsmith2k]

thanks synapse, i know you've helped me out before..

before i left i tossed the cross tab in a group based on contractid..

gets the info out that i want, but i'm re-writing an old report and people upstairs are stuck on having it display basically the same way..

is there a way to include all transtypes (that are a paymenttype) as column headers for all groupings
ie
contractid 1 5 7 11 50
999990 50.5 100.26

i'm not at the office so it's hard to get my head around it from home.. maybe outer join the transactiontypes and make the null sums 0..