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

[Former Member]

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

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

Thank you Arun - it's still unfortunately not working for me.  When you say "Reference", you mean to put Formula 1 within Formula 2's COUNT() brackets, correct?  And then Formula 2 within Formula 3's COUNT()?

[Former Member · Accepted solution]

Hello Angela,

I just looked back at a query where I count the number of schedule line for Sales Order and below is the setting I have for that.

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

reference means Just call the formula without count.

Thanks,

Arun.

[Former Member · Accepted solution]

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.

[Former Member]

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

[ccc_ccc]

Hi Angela,

Try with Total.

[Former Member]

I've read a couple other posts that refer to Total - where is that?  I don't see anything like that in the Exception Aggregation menu.

[Former Member]

Hi Angela,

you can create a formula like below

COUNT (Amount) --> choose exception aggregation as "counter of all detailed values" and reference characteristic as Document no ..

you will get count as 1 for each document number, and if you want to see the total count (Ex ; doc no XXXX997 total count is 2 and document no XXXX998 as 2), then choose calculate result as summation.

[anshu_lilhori]

Angela,the data which you have shared with us should apparently give the count 2.

It should also get summarized to 2 rows instead of 4.For 1 customer you have 2 document number.

So if you are getting 4 rows then there must be another characteristic which should be making the combination unique and splitting the rows into 4.

Please check the data correctly.

Regards,

AL

[Former Member]

Hi Angela,

Please try this

Create the formula and then define exception aggregation =  Counter for All Detailed Values That Are Not Zero, Null, or Error, with Ref Characteristic = Customer.

Regards

Vivek

[Former Member]

Hi Jyothi,

Thank you for your response - I did try this, but it still is just counting 1 for each document number, not 2 or more when the document number appears multiple times.

[ccc_ccc]

Angela,

Did you try with "Summation" option.

Regards,

Nanda

[Former Member]

Hi AL,

Thank you for your response.  There is a combination of three fields that make a record unique - document number, item number and subitem number, like this:

Document No     Item No     Subitem No

20000014997          1               1    

20000014997          2               1

20000014998          1               1

20000014998          1               2

Is it possible to have the Ref. Characteristic take into account the combination of three Characteristics?

If not - you're right - I might just have to tell the business users this is a count of documents, not at the Item/Subitem level.

Thank you for your help.

Angela

[Former Member]

Hi Nanda,

I did - here's what I'm set at:

COUNT( amount)

Exception Aggregation - Counter for All Detailed Values

Ref. Characteristic - Document Number

Calculations tab - Calculate Results As... - Summation

[ccc_ccc]

Hi Angela,

You would like to see "Document No" also in output, if it is we cannot count 4, if you donot want to output document no, then we can bring count 4.

Regards

Nanda

[Former Member]

Hi Nanda,

I do not need to see Document Number in the output.  My output just needs to be the count 4.  Is there a way to do that - am I doing something wrong from what you told me?  Thank you for your help.

[ccc_ccc]

Hi Angela,

Could you please sample input and output.

[Former Member]

Hi Nanda,

Surely, does this help?

Input:

Document Number     Customer         Amount

2000014997                    247956          -1542

2000014997                    247956          1542                        

2000014998                    247956          -1542    

2000014998                    247956            1542

Output:

GL Account     Line Items Posted

1205011               4

I do not believe there is any other field that I could use that would be unique for each line.  Potentially a combination of fields, but not one field.

Thank you for your help.

[anshu_lilhori]

Angela,after seeing this data i would have give the same solution what Arun has given.

Hopefully the solution given by Arun seems to working for you.

Regards,

AL

[ccc_ccc]

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