Skip to Content
avatar image
Former Member

Grouping Multiple Transaction Codes Into New Dimension

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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

8 Answers

  • Mar 03, 2017 at 05:07 PM

    Hi,

    Try:

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

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 03, 2017 at 06:19 PM

    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

    Add comment
    10|10000 characters needed 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)
  • avatar image
    Former Member
    Mar 03, 2017 at 07:26 PM

    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.

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 03, 2017 at 07:13 PM

    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

    Add comment
    10|10000 characters needed 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.

  • Mar 03, 2017 at 07:40 PM

    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

    Add comment
    10|10000 characters needed 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)
  • Mar 06, 2017 at 03:06 PM

    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

    Add comment
    10|10000 characters needed 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

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

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Mar 08, 2017 at 05:06 PM

    Awesome.. Glad issue got resolved.

    Thanks,

    Jothi

    Add comment
    10|10000 characters needed characters exceeded