Comparitive Balance Sheets

[pronet74]

We moved from Progression to ES about a year ago. While we had Progression we had the ability to print out a balance sheet that did a comparitive of YTD and MTD. We noticed in ES that we don't have the ability to do a MTD comparitive. Is this something I will need to do myself with Crystal Reports?

If so, can someone show me the query you used to achive the YTD comparision? I know it would be a complicated process but we are looking for something like we had in Progression.

 

[pronet74]

I was messing around with the query for me to get a total based on a period / year such as:

SELECT SUM(bdr_hfl) AS Amount, reknr AS Account
FROM gbkmut
GROUP BY RTRIM(LTRIM(bkjrcode)), RTRIM(LTRIM(periode)), reknr
HAVING (RTRIM(LTRIM(bkjrcode)) = '2007') AND (RTRIM(LTRIM(periode)) = '5')
ORDER BY reknr

However my AR account is showing a negative number, so I know this query is not right. Macola's customer portal was telling me to use the gbkmut table however majority of the fields in that table seem to be labeled in german and I have no clue what those field represent.

 

[crystalreporting]

You would be able to achieve the desired results using the Excel Add-in. In addition to the standard list of reports, the Excel Add-in includes a number of custom functions for specifically pulling information from Macola. These are similar in nature to F9. For example:

=ACC_BAL_DATE(" 1010",-1,"1/1/2001","1/30/2001",0,3)

This is balance of account 1010, between January 1, 2001 and January 30, 2001.

The functions can reference other field values so that your spreadsheets can be dynamic e.g. by tying the first part of the function (account number) to another cell, then the account balance for that account would be returned when the cell value changes. This also makes it easy to create balance sheets as you can simply copy a range of cells. The Macola help files list each of their functions and parameters. It is a very powerful set of tools.

Peter Shirley

http://www.srhconsulting.com

 

[dgillz]

Actually if you are an F9 fan, they came out with a version of F9 for Macola ES. I cannot speak to how good it is but another reseller that I know really well seems to like it.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[pronet74]

Thanks for the info. I started messing around with this. First thing is I noticed if I put a function in there, save the xls file close & re-open it. It will say 'NO RIGHTS' until I connect using the 'functions' menu selection. Is there any way to have this automatically connect? Guess a macro is the best choice.

When I use the forumla:
=ACC_BAL_DATE(" 1240 ",-1,"1/1/2006","1/30/2006",0,3) Which is our Inventory Account I get $178988.74

Going back and looking at the GL trial balance for Jan. 2006 it's showing Beg. Balance: 1,826,971.20
Total Debits: 2,128,118,84
Total Credits: 1,889,628.95
Net Change: 239,489.89
Ending Balance: 2,065,461,09

I must be doing something wrong because all of the accounts are not matching up dollar wise.

 

[pronet74]

Nevermind.. I had to use GL_BAL function.

 

[pronet74]

I do have one more question.

When I do this function on my Cost GL Accounts I get the right figure, however when I do it on my AR GL Accounts I come up with $0.00

=GL_BAL_DATE("4100:4999","0:9","00000000",-1,"1/1/2007","6/15/2007",0,3,1)

Accounts 4100 to 4999 are my GL AR Accounts. Any idea why it's giving me a zero balance?

The GL_BAL function works good for both AR and AP accounts.

 

[pronet74]

Feel like i'm talking to myself.. Nevermind about my previous post again.

I was able to fix it, just by using the GL_BAL function. However I noticed that the AR accounts show a (-) while the AP account show as a positive on the GL_BAL function. Is there anyway to reverse this?

 

[crystalreporting]

I assume the D or C flag is correct on the COA? You could try a different number in the VALUE portion of the function (last field - set to 1 in your example).

Peter Shirley

http://www.srhconsulting.com

 

[dgillz]

I don't believe there is a D/C flag in ES. Is there a functional equivalent of this?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If you have a big enough dictionary, just about everything is a word"
--Dave Barry

 

[crystalreporting]

Don - A green check mark appears in the balance column (D for debit, C for credit) for each account. In Macola ES, the debit or credit code refers to the true normal balance for the account. You can change this code from the Chart of Accounts application by double clicking in the appropriate column (D for debit and C for credit).

Peter Shirley

http://www.srhconsulting.com

 

[pronet74]

The way I did this was to just multiply the final number by -1.

It's kinda cool how they implemented this, I created an Income statement where our controller just types in the month number and year and it will pull up those figures for him.