BEx query - splitting out positive/negative records when there's no indicator attached - FS-CD

[Former Member]

Hello,

I work for an insurance company that utilizes the FS-CD module (insurance-related receipts and disbursements).  I need to write a BEx query at the General Ledger account amount level, that breaks out open items within FS-CD by the positive and negative records - see the example below.  This would be easy on a General Ledger BW cube, since we have the debit/credit indicators on that cube.  I have not been able to find something like that in FS-CD though, and I'm struggling creating a formula or condition in my BEx query that can pull out the positive/negative records like this, without doing a dump of all the records first and manipulating with a workbook.  Does anyone have any ideas?  I would have thought it would be easy to summarize by just the records that are positive amounts, and negative amounts.

GL Account         Net                    Debits                    Credits         

1205011               $187,618          $$1,996,031          $(1,808,413)

2216011               (11,183)               498,953               (510,137)    

2216013               585,777               824,302               (238,525)

2216017               (19,833)               13,189                    (33,022)

Thank you for your help.

Angela

[Former Member]

Hi Angela,

as positive values doesn't need any sign, for negative sign you can simply create a formula for credits (your key figure which you want values to be shows an negative) like below

Formula --> - (credits)

Net = debit - formula --> negative values will be shown with sign here as well.

hope this is what you are looking for?

[Former Member]

Hi Jyothi,

Thank you for your response.  I actually need the query to be able to recognize the negative value records on the cube, and then summarize them together like my example above.  Same with the positive value records.  And we don't have a debit/credit indicator characteristic on this cube that I am aware of - so if possible, the query is going to have to recognize these records utilizing the key figure only.  Does this sound possible with a formula, or some other way?

Angela

[karthik_vasudevan]

Hi Angela

Sorry if am asking something which you have already mentioned. But just to be clear before giving any suggestions, I have to.

Do you mean that your infocube has two entries for the same G/L account. One with a negative value and another with positive. You wanted to display both separately and the net value as well?

If this is the case, we could achieve by formulae.

If this is not the case, please try to explain a little more with an example of how it appears in your cube. You have already explained what are you expecting which is fine.

Regards

Karthik

[Former Member]

Hi Karthik,

Thank you for your reply.  These are customer transactions - receipts and disbursements, which show as positive and negative amounts/records.  These transactions are mapped to different G/L accounts in our FS-CD module, and then nightly, those records are summarized and sent to those G/L accounts in the G/L (FI module).  My query is on one of our FS-CD cubes, where all the customer transactional data sits.  You are correct - I would like to display the sum of the negative records, the sum of the positive records and the net amount.  Does this make sense?  Thank you for your help.

Angela

[karthik_vasudevan]

Thanks Angela for making me understand.

So if my understanding is correct, your cube will have records in the below way either one entry or many entries. (not sure which is splitting your information in cube level as it will aggregate the data)

GL Account         Keyfigure

1205011               $1,996,031

1205011               $(1,808,413)

You want this to display like below in query.

GL Account         Net                    Debits                    Credits         

1205011               $187,618          $1,996,031          $(1,808,413)

To do this, you need to write three formulae in the query designer.

Debits --> (Keyfigure > 0) * keyfigure + '0'

Credits --> (Keyfigure < 0) * keyfigure +'0'

Net --> Debits + Credits

If this is not the case, would you give the details of data appearing in your cube

Regards

Karthik

[Former Member]

Hi Karthik,

I gave that a shot, and it's still taking the entire G/L account balance and assessing the positive/negative at that level, for example:

G/L Acct          Debits          Credits          Total

1205011          $1000          $    0                $1000

2216011               0              (2000)              (2000)

2216013               0              (10,000)         (10,000)

2216017            20,000               0               20,000  

The data on the cube is broken out by Item Number/Subitem Number/Document Number.  I am able to pull data at this level into other queries on this cube.  Here is an example:

Item #        Subitem #          Doc #                    Customer              Amount     G/L Account

1                    1                    1000348145              23925               (100)               2216011

1                    2                     1000351500               152124               200               1205011

2                    3                        1000361038          662                       400               2216013

3                    2                        1000374640              662                    500              2216011

There can be thousands of records tied to a G/L account, both positive and negative records.

Thank you for your help.

Angela

[Former Member]

That example data didn't turn out so well above.  It's basically this:

Item #, Subitem #, Doc #, Customer, G/L Account and Amount (plus many other fields on the cube, but these are some of the main ones).

[karthik_vasudevan]

Hi Angela

That gives the clear reason. I was in an assumption that your cube has only G/L account.

In this case, it will be difficult to get the details as per your expectation. Let me think of some other way while others try to help you.

If you have BI 7.3 or above, you could achieve this by having your full query as info-provider with all your lower level characteristics (item number, document number, customer, etc.,). Then use the same formulae and calculate debits and credits in that level. Then you could use this query as your source and load the data into a DSO or cube (map only G/L account and debit, credit keyfigures to your target).

You can create a new query over that infocube which will exactly work in your situation.

Its unfortunate if you do not have 7.3. Lets wait for others while we think about this. There might be some way for this in transformation.

Regards

Karthik

[Former Member]

Hi Angela,

The doc# seems to unique (from your above example)

you can try adding doc# next to GL account in query(rows), and then hide the doc#(no display) in this way GL account values don't get summarized in query level,

use the formula to derive debit , credit and net.

[Former Member]

Hello,

Thank you everyone for your help.  We actually ended up adding a characteristic to the cube that adds a "C" to the record if it's a negative amount, and a "D" to the record if it's a positive amount.  I'm able to pull that into my queries now, and it didn't take very long to implement.

Angela

[karthik_vasudevan]

Hi Angela

Good to hear that. Have a good one 

Regards

Karthik