Page 1 of 1

Sum Columns without details

PostPosted: Wed Mar 04, 2015 8:00 pm
by BRobinsonS
In my accounting application, I want a report that sums the debits and credits of the journal entries.
I only want the sum and not the details of each transaction.

e.g.
100 Bank $100 DB
400 Sales $100 Cr
200 Accounts Payable $50 DB
100 Bank $50 Cr

I would sort by account number
then sum the Debits and Credits

So I would like the 'sum of each account' report without details to look like

100 Bank DR $100 CR $50 Difference $50
200 Accounts Payable DB $50 CR $.00 Difference $50
400 Sales DB $0.00 CR$100 Difference -$100

Suggestion on how to accomplish this with Report Designer?

Re: Sum Columns without details

PostPosted: Thu Mar 05, 2015 12:35 pm
by Neosoft Support
I don't know the answer off the top of my head. I would think that sorting the data and using the Report Designer's Group Header/Footer partitions would be the key to getting the correct results.

Re: Sum Columns without details

PostPosted: Thu Mar 05, 2015 5:40 pm
by BRobinsonS
sorting the data and using the Report Designer's Group Header/Footer partitions

That was my thinking but didn't work for me. Seems to need data band and fields, but again I don't want them to show.
There is a Master/Detail relationship with I cancel and then reset at end.
Tried both, commented out GoSubs and not but didn't work either.

Code: Select all
GoSub "CancelRelation"
dbpSort "NBAccounting" "JrDetails" "Account=ASC"
dbpPreviewReport "NBAccounting" "CheckAccounts.dbr" "DisplayMode=FitPage;InitialPage=1"
dbpUnSort "NBAccounting" "JrDetails"
GoSub "SetRelation"

Re: Sum Columns without details

PostPosted: Thu Mar 05, 2015 5:42 pm
by Gaev
Brian:

Assuming your Journal Table is made up of fields ...
AccountNumber
AccountDescription
ItemAmount
DebitCredit

... things would be a lot easier if instead it was made up of fields ...
AccountNumber
AccountDescription
Credits
Debits
Differences
DebitCredit

... but if (for some reason) you can't do that ...

1) Create a Temporary Table called JournalReport (using field types for fields as appropriate) like so ...
Code: Select all
CREATE TABLE JournalReport
  (AccountNumber INT,
   AccountDescription TEXT,
   Credits CURRENCY,
   Debits CURRENCY,
   Differences CURRENCY
  );

... then do the transformation using ...
Code: Select all
INSERT INTO JournalReport (AccountNumber, AccountDescription, Credits, Debits, Differences)
SELECT (AccountNumber, AccountDescription, ItemAmount, 0, ItemAmount)
FROM Journal
WHERE DebitCredit = 'CR'

INSERT INTO JournalReport (AccountNumber, AccountDescription, Credits, Debits, Differences)
Select (AccountNumber, AccountDescription, 0, ItemAmount, (-1 * ItemAmount))
FROM Journal
WHERE DebitCredit = 'DB'

... and you will end up with a Table that can easily be used with the Report Designer ... just SUM the 3 currency fields by AccountNumber.

Re: Sum Columns without details

PostPosted: Sat Mar 07, 2015 4:22 pm
by BRobinsonS
I have done a similar report using Delphi 2009 with Fast Reports.
Under Fast Reports I was able to set the Databand to 'don't print' so I would get the Group Header and Group Footer to get the desired results.
I can't seem to do this with Report Designer.

I do have separate fields for Debits and Credits used in the Journal entries.

Again I don't want the individual details of each account in the journal only the totals for each account.
This report is to verify that my GL Account totals agree with journal entry totals for each account.

Re: Sum Columns without details

PostPosted: Sat Mar 07, 2015 6:22 pm
by Gaev
Brian:

I do have separate fields for Debits and Credits used in the Journal entries.


Say the Journal Table looks like this ...
Code: Select all
AccountNumber
AccountDescription
Credits
Debits


This code will create a Table (that can easily be printed with ReportDesigner) ...
Code: Select all
CREATE TABLE JournalReport
  (AccountNumber INT,
   AccountDescription TEXT,
   ItemCount INT,
   TotalCredits CURRENCY,
   TotalDebits CURRENCY,
   Differences CURRENCY
  );


... and this code will populate it ...
Code: Select all
SELECT INTO JournalReport
AccountNumber AS AccountNumber,
AccountDescription AS AccountDescription,
COUNT(Debits) AS ItemCount,
SUM(Debits) AS TotalDebits,
SUM(Credits) AS TotalCredits,
SUM(Debits-Credits) AS Differences,
FROM Journals GROUP BY AccountNumber


... such that there is one row for each AccountNumber ... with additional columns showing the AccountDescription, count of rows for that AccountNumber, Sum of all Debits, Sum of all Credits and Sum of all Differences ... printing the contents of this Table with ReportDesigner is now a simple/easy task.

Note: Since the JournalReport is a temporary holding place for the Summary records, in order to accomodate repeated use, this Table will have to be cleared of its records from previous runs (either just before each run or just after you have produced the Report)

Re: Sum Columns without details

PostPosted: Sat Mar 07, 2015 7:03 pm
by BRobinsonS
Thanks Gaev.
Gives me a better idea how to handle this.

Perhaps I could add this table to the database and use the sql to populate and empty using DBPro as needed?

Re: Sum Columns without details

PostPosted: Sat Mar 07, 2015 7:23 pm
by Gaev
Brian:

Perhaps I could add this table to the database and use the sql to populate and empty using DBPro as needed?


Something like ...
Code: Select all
dbpTableExists "yourDB" "JournalReport" "[Result]"
If "[Result]" "=" "True"
  ... empty old contents
  dbpDeleteAll "yourDB" "JournalReport"
Else
  dbpCreateTable "yourDB" "JournalReport" "AccountNumber BigInt; AccountDescription String(50); ItemCount Integer; TotalCredits Currency; TotalDebits Currency; Differences Currency"
EndIf

Re: Sum Columns without details

PostPosted: Thu Jul 02, 2015 5:18 pm
by BRobinsonS
Back to this project. But baffled.

dbpDeleteAll "NBAccounting" "IncomeExpenseTbl"
dbpExecSQL "NBAccounting" "INSERT INTO IncomeExpenseTbl (Account,AccountName,AccountType,TotalDebits,TotalCredits) SELECT Account,AccountName,AccountType,SUM(InAmt) AS TotalDebits,SUM(OutAmt) AS TotalCredits FROM JrDetails GROUP BY Account,AccountName,AccountType" ""
dbpSort "NBAccounting" "IncomeExpenseTbl" "AccountType=ASC;Account=ASC"
dbpPreviewReport "NBAccounting" "IncomeStatement2.dbr" "DisplayMode=FitPage;InitialPage=1"


This code is designed to populate the 'IncomeExpenseTbl'. It is not a temporary table.
First I delete the records by 'dpbDeleteAll or dpbEmptly' .
On first run (from a Button for testing), the Report (based on IncomeExpenseTbl) is blank.
With second run the Report shows.
If I check the IncomeExpenseTbl, the table has been populated with duplicate records.
I have added a AutoInc field to IncomeExpenseTbl with no favourable results.

Not sure what is happening.

Re: Sum Columns without details

PostPosted: Mon Jul 06, 2015 6:38 pm
by BRobinsonS
I may have this working now.
I only seems to have started working when I Closed the Table after the SQL Insert statement then Opened it again before I did the Report Preview.
Don't know why this worked. I guess it's important just to get it working.

The dbpSort doesn't seem to be required either.