Skip to Content
avatar image
Former Member

BEx query - counts of records in query - FS-CD

Hello,

I work for an insurance company that uses the FS-CD module (cash receipts and disbursements).  As receipts are received and payments are disbursed, these receivables and payables hit General Ledger accounts within FS-CD on a detailed record-by-record basis.  Nightly, this information is summarized and sent to the actual General Ledger (FI module).

I need to write a BEx query that is broken out by General Ledger account, which shows the counts of records associated to that General Ledger account in FS-CD.  I am able to break out this information by amount, but cannot figure out how to break it out by count.  Does anyone have any ideas?

Thank you for your help.

Angela

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    avatar image
    Former Member
    Oct 20, 2015 at 08:09 PM

    Hello Angela,

    To achieve the result you are expecting, we need to create three formulas in the query with exception aggregation.

    • Forumal 1 - COUNT( amount) Exception Aggregation - Counter for All Detailed Values Ref. Characteristic - SubItem No.
    • Formula 2 - Reference Formula 1 with Exception Aggregation - Counter for All Detailed Values Ref. Characteristic - Item No.
    • Formula 3 - Reference Formula 2 with Exception Aggregation - Counter for All Detailed Values Ref. Characteristic - Document Number

    Thanks,

    Arun.

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Oh wow - Arun - that looks like it worked.  Thank you so much.  I don't think I'll close this question out till I do more thorough testing tomorrow, but it is looking good for a quick sample I just ran.

  • avatar image
    Former Member
    Oct 19, 2015 at 09:21 PM

    I am guessing a lot of my SCN friends are at Tech Ed. 😊 Which might be a good thing, gave me a chance to play around a bit and refine my question.

    I have data that looks like this:

    Document No          Customer        Amount

    20000014997          247956          $-1542

    20000014997          247956          $1542         

    20000014998          247956          $-1542

    20000014998          247956          $1542

    I would like this to count as 4 records.

    When I create a Formula, then Exception Aggregation = Counter for All Detailed Values That Are Not Zero, Null, or Error, with Ref Characteristic = Document Number, it counts these as 0 records.

    When I choose Exception Aggregation = Counter for All Detailed Values, with Ref Characteristic = Document Number, it counts these as 2 records.

    Is there a way to have this count as 4 records?

    Thank you for your help.

    Angela

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 19, 2015 at 09:20 PM

    Hi Angela,

    Use Exception aggregation as count of receipts and respective to GL account (GL Account as Reference object).

    Please try to search "Exception Aggregation" concept in BEx.

    Thank you

    Nanda

    Add comment
    10|10000 characters needed characters exceeded