TRIAL BALANCE GROUPING

[SMSNET]

hello everyone!
im not quite adept with ms visual foxpro... but i am working in a company where i have adopted a system from previous programmer... i learned to appreciate the language (VFP 9.0) and so i tried to develop an accounting system that would
allow the automation in all the transaction of accounting entries. My problem is i am having difficulties in grouping
data of the same account name and sum its total in detail band report.

as you can see, data must be group according to accountname and its amount must be sum up or calculated.

any idea? thanks for the help in advance.

 

[Mike Lewis]

Welcomem to the forum.

There are two approaches you can take to solve this problem.

The first option is to do the grouping and totalling in advance, before you do the report. You get the data into a cursor, and use that cursor to drive the report.

For example, something like this:
[tt]
SELECT AccountName, SUM(Amount) AS Amount FROM Transactions GROUP BY AccountName ;
INTO CURSOR csrTrans[/tt]

Then use csrTrans to drive the report. The report would need no summary or calculations or grouping, as that has all been done in advance. Just place a record from csrTrans into the details band, with two columns: AccountName and Amount. You will also presumably need a grand total on Amount.

The second option would be to set up a goup within the report, and to use a report variable to sum the transactions. But I would prefer the first option, as it lets VFP do the grouping, etc. in the place where it does it best, that is, in a SQL statement. This approach also makes debugging easier.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SMSNET]

just got back from a day trip (out of town)... sir mike, your idea was quite fine...
as it resolve issues of my concern except that it does not include the last column PK
which would determine the classification of accounting entries as debit and credit...
i added the entry pk to your suggestion:

SET safety OFF
SELECT AccountNam, SUM(Amountsum) AS Amountsum, pk;
FROM jev2017 GROUP BY AccountNam, pk;
INTO TABLE jevrep
report form trialbalance2.frx noconsole to printer prompt preview

i didnt used cursor in driving the report, instead i tried to put the selections to a temporary table since i am used to it...
anyways, i have one remaining issue to deal. i cant seem to get the grand total of a column based
on the debit and credit. i wish that i could do it before issuing the report and in an SQL statement... Thanks sir Mike for the reply.
Greatly appreciate your support...

 

[Olaf Doschke]

If I don't get it wrong you would need to multiply all pk="DR" with -1, so your SUM would change to

SUM(IIF(oj="CR",AmountSum,-Amountsum)) as Totalsum

You'll need to NOT group by Pk for that to work out and if you want all sums for CR, DR and both, you'll need to union:

SELECT AccountNam, SUM(Amountsum) AS Total, pk, .T. as partialsum;
FROM jev2017 GROUP BY AccountNam, pk;
UNION;
SELECT AccountNam, SUM(IIF(pk="CR",AmountSum,-Amountsum)) as Total, "  " as pk,  .F. as partialsum;
FROM jev2017 GROUP BY AccountNam;
INTO TABLE jevrep

To cover the unusual case pk is neither CR nor DR you would perhaps apply ICASE:

SUM(ICASE(pk="CR",AmountSum,pk="DR",-Amountsum,0)) as Total

Last, not least you might want to turn a positive total in the rows with empty pk (" " as pk) to CR while you would denote a negative total as DR and take the absolute amount. That's perhaps best done as UPDATE after creating the jevrep table:

UPDATE jevrep SET pk=IIF(Total<0,"DR","CR"), Total = Abs(Total) WHERE not partialsum and empty(pk)

This has one problem, if you'd not always have records in the order first CR, then DR then total, though it's likely the case, I added the partialsum column for that matter to identify the overall total as not partialsum.

Bye, Olaf.

 

[Mike Lewis]

Olaf has given you a good answer regarding totalling of debits and credits: multiply the debits by -1.

To display the symbol [tt]CR[/tt] and/or [tt]DB[/tt] in your report, you don't need to do anything in the cursor. Just format the field in the report. Do this:

- In the report designer, right-click on the Amount field and choose Properties.

- In the resulting dialogue, go to the Format tab.

- Select Numeric, then tick either "CR if positive" or "DB if negative" or both, depending on your requirements.

Regarding the grand total, you can't easily do that at the SQL stage. Better to do it within the report. Do this:

- In the report designer, select Optional Bands from the Report menu.

- Tick "Report has summary band".

- Place a new field in the Summary band.

- Open its Properties dialogue (see above); on the Calculate page, select Sum as the Calculation Type, and Report for "Reset baseed on".

Final point: I appreciate that you are using a temporary table rather than a cursor because that is what you are used to. However, I suggest you familiarise yourself with cursors, as they have several advantages over a temporary table:

- You don't have to worry about clashes with existing file names;

- You don't have to worry about multiple users trying to create tables with the same name;

- You don't have to clean up afterwards (cursors are automatically deleted when you have finished with them).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[SMSNET]

Olap,

sir, your suggestion is an option to take. Actually, i have added the functionality as options for checking as it actually yields accounts with variance or unbalance based on the results of trial balance. It makes life more easier now as tracing could easily be achieved by comparing DR/CR values. A million thanks to you sir.

Sir mike, i take your point of concern regarding the use of cursors rather than temp tables... Actually, i dont really have that idea as to the advantages of using cursors in making SQL statements... Thank you sir for keeping my job easier with your well timed advice. God Bless to both of you!

 

[TamarGranor]

The only reason to use a temporary table rather than a cursor is if you need it to exist after the code completes, for example, to be seen by another user. Otherwise, cursors are always a better choice.

Tamar