Source language: Translate to:

Sum Columns without details

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Sum Columns without details

Postby BRobinsonS » Wed Mar 04, 2015 8:00 pm

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?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sum Columns without details

Postby Neosoft Support » Thu Mar 05, 2015 12:35 pm

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5593
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: Sum Columns without details

Postby BRobinsonS » Thu Mar 05, 2015 5:40 pm

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"
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sum Columns without details

Postby Gaev » Thu Mar 05, 2015 5:42 pm

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.
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sum Columns without details

Postby BRobinsonS » Sat Mar 07, 2015 4:22 pm

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sum Columns without details

Postby Gaev » Sat Mar 07, 2015 6:22 pm

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)
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sum Columns without details

Postby BRobinsonS » Sat Mar 07, 2015 7:03 pm

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?
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sum Columns without details

Postby Gaev » Sat Mar 07, 2015 7:23 pm

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
User avatar
Gaev
 
Posts: 3716
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Re: Sum Columns without details

Postby BRobinsonS » Thu Jul 02, 2015 5:18 pm

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada

Re: Sum Columns without details

Postby BRobinsonS » Mon Jul 06, 2015 6:38 pm

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.
Brian Robinson
'When all else fails, try again!'
www.ComputerSoftwareSystems.com
User avatar
BRobinsonS
 
Posts: 337
Joined: Sun Sep 25, 2005 9:00 pm
Location: Ontario, Canada


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron