Accueil → WINDEV 21 → [WD21] [HFSQL21] Date total to arrive after taking balance of previous date
[WD21] [HFSQL21] Date total to arrive after taking balance of previous date
Débuté par Sivaprakash, 18 mai 2017 15:23 - 6 réponses
Posté le 18 mai 2017 - 15:23

This reports got four columns, Date, Particular, Debit Amount, Credit Amount. At the end of each day, totals need to be displayed for Debit Amount & Credit Amount and Balance Amount deducting Debit Amount from Credit Amount, after Date total. Only this balance amount need to be taken for the next day total. And this balance amount could appear either @ Debit Amount column or @ Credit Amount column.

Date Particulars Debit Amount Credit Amount

17.08.2017 Paid to Mr.X 10,000.00 0.00
17.08.2017 Billing To Mr. Y, No. 234 0.00 50,000.00
17.08.2017 Purchase from Mr. Z 45,000.00 0.00
---------------- --------------------
Total 55,000.00 50,000.00
Balance 5,000.00
18.08.2017 Receipt from Mr Y 10,000.00
18.08.2017 Sales to Mr. Q 22,000.00
-------------- -------------------
Total 5,000.00 32,000.00
Balance 27,000.00

19.09.2017 Payment to Mr. E 30,000.00 0.00
-------------- -------------------
Total 30,000.00 27,000.00
Balance 3,000.00

Any idea to arrive totals such as above, is really appreciable.

Happiness Always
BKR Sivaprakash
Posté le 18 mai 2017 - 21:33
In that case I think that is a good practice to store the calculations into the database. For example you can do it once a day, at the end of the day. And save the information in the same file or, normally, in a new one.

It has some advantages like:
-Once the calculations are done, they never change. If you do the other way, and there is a change in (or somehow becomes corrupted) one row of some months ago, you will get all the balance of all previous days changed.
-Data is ready and easy to be read. No need to such complex queries that need to make calcs on all previous days.
-Let's prune to errors, in mi opinion.

I'm assuming that calculations of previous days should never change once done.

José Antonio.
Posté le 19 mai 2017 - 06:52
Thanks José Antonio.

But here it's not possible. We need to arrive it every time when a report is requested. Not possible to display from arrived value(s) from database.

Our earlier software, developed using Powerbuilder, we have already given this solution. Now we need to give it in our Windev application too.

Happiness Always
BKR Sivaprakash
Posté le 19 mai 2017 - 18:00
Hi Sivaprakash,

1. Create a report - SORT by Date and also create a BREAK (on the Date field)

2. Declare the following variables in your Report code -> Opening ...
TotalDebit, TotalCredit, DayDebit, DayCredit -> numeric or currency

3. In your "BODY area" in the report add the Date, Debit and Credit fields from your table.

4. Select BODY->Block code and in the "after printing BODY" area add the code:

5. In the "End of Break 1" area in the report designer add 2 items : item_Debit and item_Credit that are linked to "none".

6. Select End of Break 1->Block code and in the "before printing BREAK FOOTER 1" area add the code:

7. Select End of Break 1->Block code and in the "after printing BREAK FOOTER 1" area add the code:

Generally you can do many things in a report, using simple WX code.

Steven Sitas
Posté le 20 mai 2017 - 08:29
Thanks Steven,

Here my need to add the Day Balance (DayDebit - DayCredit) to next day's balance. Can I set,

If Daydebit > DayCredit Then
DayDebit = DayDebit - DayCredit
DayCredit = 0
DayCredit = DayCredit - DayDebit
DayDebit = 0

@ After printing Break Footer 1 ?

Happiness Always
BKR Sivaprakash
Posté le 20 mai 2017 - 12:30
Hi Sivaprakash,
You can add whatever code you like, as long as what you get, is what you expect :)
But you must understand how Break Footers work ...
Specially the "before printing ..." and "after printing ..." code areas in the code block of the Break Footers.

I would add another variable to keep the running day balance ->DayBalance and I would do calculations for this variable, BEFORE clearing DayDebit and DayCredit - and best in "BEFORE printing Break footer 1"

Steven Sitas
Posté le 20 mai 2017 - 13:12
Thanks Steven,

Got the idea. I'll experiment. Names are self explanatory.

Happiness Always
BKR Sivaprakash