Skip to Content
0

Grouping Multiple Transaction Codes Into New Dimension

Mar 03, 2017 at 04:45 PM

604

avatar image
Former Member

It seems this has been asked before but I'm still not getting it.

I am trying to group multiple transaction types into a single transaction type and then count how many of these occurred in a given month. I am able to display each transaction code separately by adding the trans code description to the left side of my crosstab and month to the top, and populating the values with a [count of cash events] measure I created.

What I need is to be able to group multiple transaction codes together, rename them, and display their sum.

I am able to identify the transactions using in list function from another SAP thread {URL} https://archive.sap.com/discussions/message/15165245#15165245

=If([Trans Cd] InList("3124"; "3125"; "3126"; "3127"; "3128"; "3129"); "Escrow Disbursement";[Trans Cd Desc])

I am trying to get the report to look something like this, where A will be "Escrow Disbursements" and B will be "Escrow Payments" and the report will populate their total.

I have also tries using =If([Trans Cd] InList("3124"; "3125"; "3126"; "3127"; "3128"; "3129"); 1;0) and the using sum([Escrow Payments]) but when I pull it into the report I get an additional row under each transaction code instead of a new row for Escrow Payment.

Thanks in advance for any insight.

Sincerely,
Jay

ideal.jpg (58.8 kB)
capture.jpg (41.4 kB)
10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

8 Answers

Jyothirmayee A Mar 03, 2017 at 05:07 PM
0

Hi,

Try:

=sum(If([Trans Cd] InList("3124"; "3125"; "3126"; "3127"; "3128"; "3129"); [Escrow Payments]) )

Thanks,

Jothi

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Jothi,

Should my [Escrow Payments] formula be =If([Trans Cd] InList("3124"; "3125"; "3126"; "3127"; "3128"; "3129"); 1;0)

If this is the case the equation is counting how many transactions are occurring, but I am still unsure as to how aggregating these trans codes into a new row would work.

Thanks,
Jay

0
Former Member

I am able to generate a standalone report at this point with the function you mentioned.

But haven't been succesful in displaying the other ungrouped transaction codes alongside these.

capture.jpg (16.1 kB)
error.jpg (42.5 kB)
0
Jyothirmayee A Mar 03, 2017 at 06:19 PM
0

Hi,

Now I understood,

you want to display the count of Transaction codes that fall under your list. I thought you wanted to agreegate Sum([Escrow Payment] within that list.

=Count([Trans Cd]) where ([Trans Cd] InList("3124"; "3125"; "3126"; "3127"; "3128"; "3129"))

Let me know if this works.

Thanks,

Jothi

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

That formula doesn't accomplish what I am trying to figure out.

I am trying to have the report be as follows:


Interest is clearly defined by trans code 3200 so I use measure of [count([cash event])] and dimensioned the report with [trans code] to show ow many of this "trans code type cash events" happened during the the month

Principal is clearly defined by trans code 3100

Fee Payment is clearly define by trans code 2700

Escrow Disbursements can be in list 3124, 3124, 3126, 3127, 3128, 3129 depending on its business use purpose but I would like these consolidated into a single row and occurrences of any of these cash events counted. I am able to accomplish this by using =sum(If([Trans Cd] InList("3124"; "3125"; "3126"; "3127"; "3128"; "3129"); [Escrow Payments]) ) where [Escrow Payments] is defined as =If([Trans Cd] InList("3121";"3122";"9372";"9373";"9374"; "9375");1;0). I am not able to display this count as an individual row along with the interest, principal or fee payment dimensions.

Escrow Payments can be in list 3121, 3122, 9372, 9373, 9374, 9375 and same as above.

Thanks,

Jay

capture.jpg (32.8 kB)
0
avatar image
Former Member Mar 03, 2017 at 07:26 PM
0

Jothi,

Yes, the [Trans Cd] dimension captures all of the transaction types. This dimension is from the database and is not a formula and there are about 100 codes but I filtered out most at the query level.

I am trying to display in cross tab with [month id] on top and [Trans Cd] on the left. Each [Trans Cd] has a corresponding [Trans Cd Desc].

Here is what my report looks like with these dimensions in place:

"Escrow Disbursement" is more of a general description that we would like multiple [Trans Cd] included in.


current-report.jpg (29.5 kB)
Share
10 |10000 characters needed characters left characters exceeded
Jyothirmayee A Mar 03, 2017 at 07:13 PM
0

Hi,

Do you have a variable that captures all the types?. if so, what happens when you bring in TransType variable into table rows?. is it Crosstab?.

If you have a single variable then say:

=if([Transtype] = "Escrow Payments" then count([Cashevents]) In ([Month)]

post a screen shot of your report and also the TransType variable pls.

Thanks,

Jothi

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Jothi,

Yes, the [Trans Cd] dimension captures all of the transaction types. This dimension is from the database and is not a formula and there are about 100 codes but I filtered out most at the query level.

I am trying to display in cross tab with [month id] on top and [Trans Cd] on the left. Each [Trans Cd] has a corresponding [Trans Cd Desc].

Here is what my report looks like with these dimensions in place:

"Escrow Disbursement" is more of a general description that we would like multiple [Trans Cd] included in.

0
Jyothirmayee A Mar 03, 2017 at 07:40 PM
0

Hi,

I see that you have grouped list of [Trans Cd] and wanted to display as single Transtype.

Suppose here: if ([Trans Cd] InList("3124"; "3125"; "3126"; "3127"; "3128"; "3129"); "Escrow Disbursement").

Can you post what you have from DB and what you wanted to see in report?. I think I'm confused.. Sorry :(

Thanks,

Jothi


23609-ideal.jpg (58.8 kB)
Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Sorry for the confusion. To clarify the "Escrow Disbursement" I'm referring to is not the same as the one listed next to 3124. 3124 is just one of the possible "Escrow Disbursement" codes with the others listed above.

This is what my data looks like

I want to see Trans Description (ones defined by data (ie. Interest, Principal, Fee) and ones created with inlist() functions(ie. escrow disbursement and escrow payment)). Each [Bundle Item ID] has a corresponding [trans cd] so my logic was that I could define [Count Cash Event]=count([bundle item ID]) as my measure. Below isn't what I'm trying to display in metrics but is a sample of the data. This would be displayed as follows in the cross tab

Month

2700 3

3200 2

capture.jpg (25.0 kB)
0
Jyothirmayee A Mar 06, 2017 at 03:06 PM
0

Hi,

Sorry for the late reply.. Weekend you know :)

if you remove Bundle Id then you should see Count(Cash Events) aggregated. If not use

=Count([Cash Events]) in ([Trans Cd])

Thanks,

Jothi

Show 1 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Jothi,

I was able to use a combination of what was described above to come up with a solution. I eventually created a dimension for each trans code individually and then used the sum function to count occurrences.

[interest payment]=If([Trans Cd] InList("3200"); 1;0) and then =Sum([Interest Payment])

This method allowed me to display all of the trans codes I needed.

Thanks,
Jay

0
avatar image
Former Member Mar 07, 2017 at 06:51 PM
0

Jothi,

I was able to use a combination of what was described above to come up with a solution. I eventually created a dimension for each trans code individually and then used the sum function to count occurrences.

[interest payment]=If([Trans Cd] InList("3200"); 1;0) and then =Sum([Interest Payment])

This method allowed me to display all of the trans codes I needed.

Thanks,

Jay

Share
10 |10000 characters needed characters left characters exceeded
Jyothirmayee A Mar 08, 2017 at 05:06 PM
0

Awesome.. Glad issue got resolved.

Thanks,

Jothi

Share
10 |10000 characters needed characters left characters exceeded