Report AP by Dimension

bhalpin

I'm looking for ideas on how to do this:

- Client has two 'departments'

- A dimension will be used to segregate the departments

- There is one common list of vendors

- When purchases are entered, a dimension code at the line-item level will take care of departmentalizing the debits (expense, asset, whatever).

Here is the problem: How to get AP totals by dimension?

Another way of looking at it: You can pick a (single) department (dimension) in the header of a purchase invoice, but is there any way to "split" the AP credit to enable a GL balance by dimension?

Any thoughts would be appreciated.

Bob

AdamRoue

The analysis by dimensions will give you this. You can see the defined GL accounts in the rows, and the columns can be the set departments with a date filter. Do you have this funcitonality?

Failing that from the COA have a look at the balance button and balance by dimension.

bhalpin

Hi Adam.

Thank-you for your reply.

I've looked at analysis by dimensions and balance by dimension but can't get the result I need.

Maybe the posting logic will help:

When entering the purchase, the debits, credits & dimensions look like this:

Dr ExpAcct1 $100 DimensionA (Set at item level)
Dr ExpAcct2 $50 DimensionB (Set at item level)
Cr AcctsPayable $150 DimensionX (Set in header)

The requirement is to pull these totals from AcctsPayable:

DimensionA Cr $100

and

DimensionB Cr $50

However, the only total that's available is DimensionX for Cr $150.

I know this is impossible - after all, the credit to AP is $150 and carries only DimensionX - but I'm hoping someone else has faced this requirement and has some sort of a work-around (no matter how cumbersome) to get the result this customer wants.

Bob

AdamRoue

Hi Bob

I presume ExpAcc1 and 2 are actual ledger accounts and you can go to these ledger accounts, drill into the transaction and find the purchase transaction and see the dimensions attached?

I am struggling to see why you cannot get it to work. If the transaction exists against a GL with dimensions you can analyse it!

bhalpin

Hi Adam.

Yes, ExpAcct1 & 2 are GL accounts.

Also, I agree, if a transaction exists with a dimension, you can analyse it. And I can analyse the debits by dimension. *But*, it's the credit that's the problem.

Based on the postings described earlier, here is what I get in Analysis by Dimension:

Acct        Total    DimA   DimB     DimX
AP        -150.00                 -150.00
ExpAcct1   100.00  100.00
ExpAcct2    50.00          50.00

But, this is what the customer wants:

Acct        Total    DimA   DimB     DimX
AP        -150.00 -100.00 -50.00
ExpAcct1   100.00  100.00
ExpAcct2    50.00          50.00

Does that explain the issue better?

Bob

AdamRoue

Yes I see they want to see the other side of the T account in the original ledger account when this information is simply not held.

I think the "best" suggestion I could give (apart from trying to convince them not to have this ) would be to create an analysis view card where the GL filter was set to AP|ExpAcct1|ExpAcct2. Your view could then show the dimensions as the rows with the date buckets as columns, in this manner you get your total of AP by dimension, but it is presented differently.

The difficulty is you do not have the Analysis report option as you do for sales, purchase and inventory where you bring in the range of what you want. If you could do this with GL codes it would help. I think filtering in the described manner is your best bet. Although a "really" long winded way would be to define these three accounts with a dimension of itself and then sum them into one total and analyse them in this way, although I am not sure this would work depending upon the posting processes of the transactions.

bhalpin

Yes, it's a bit of a sticky wicket.

You've given me a bunch to mull over and test.

Thank-you.

Bob