Add sum/total on header 3

[madalini]

Hi there!

I'm a newbie, so, please, have mercy!

Due to some changes in fiscal law, I'm trying to have my own report. It has 3 sections: Page Header, Detail and Page footer. The sum (of Detail) works fine in footer but I can't add that in Page Header.

Can you help me to add that?

 

[Chris Miller]

Page header is printed before the details, so it has no access to things like sums or other computed values. Precompute what you need to print in a header and set it into variables before REPORT FORM and then print them is one idea.

Another idea is two pass printing. In the simplest form by using IIF(_pagetotal>0,whattoprint,0) which causes two passes and nothing is printed in the first pass. whattoprint therefore must be made available for the second pass, for example when a group ends.

Chriss

 

[Mike Lewis]

I would go with Chris's first option.

Immediately before your print the report, calculate the sum of your Details field. You can do that with a SQL SELECT statement or a VFP SUM command. Store the resulting figure in a variable. Then, in your report, place a field in the page header and point it to the variable.

If each invoice shows a different total, you would have to print each one separately. In other words, you do the calculation for the first invoice, then print the first invoice, then repeat for the second invoice, and so on. This won't be necessary if the figure is the same for all invoices in the batch.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[madalini]

Thank you, @Chris+Miller and @Mike+Lewis!

I will try to do that! I'm advanced in many domains but programming. I just started the David Malan's CS50 course, so that would be a real challenge for me ... but I take it. I have to honestly say that Cris's 2nd option I'd go first. I can work only on the report. I can't access programming part.

 

[Chris Miller]

If you can only change the report, then indeed you can't put precalculations into the code of an executable. But is there nobody to adapt or develop this application?

What is printed there now is just the same as in the footer, the "Valoare totala", right? Well, that seems to me computed within report variables you can define and set to calculation like a sum. So the value is there after the details band has been processed, but it starts with 0 and that's why you get 0 in the page header.

Your first try could be changing that expression to this expressionn:

IIF(_pagetotal>0,val_vd+tva_vd,0)

The downside of forcing a two pass approach is that the report runs twice as long, and bve cautious, it might even sum the same values twice, so you'd need to divide the values by 2, or it might reset the varaibles to 0 in the second pass again and you don't get the total in the page header. Then you'd need to use an approach that is described here:

https://www.vfphelp.com/help/_5wn12p4yd.htm

The "expression to run" should be "PageHeaderValoareTotala = val_vd+tva_vd" in the "on exit" box. And PageHeaderValoareTotala is a variable you need to add in the report variables section without any calculation, that means here:

https://www.vfphelp.com/help/_5wn12p5bp.htm

Last not least the expression within the pageheader report label then should then become

IIF(_pagetotal>0,PageHeaderValoareTotala,0)

, but try the one with val_vd+tva_vd, first.

Chriss

 

[Chris Miller]

By the way, there is a chance to still use Mikes advice to implement my first idea: The data environment of the report has methods that are run and you can program in them. So in the init of that you could compute that total and set a variable to that, which you don't even need to add to the report variables in the section I referred to above. You can still just print that variable in the page header.

The situation becomes more complex if the report does print more than one invoice, because then you'd need a variable array with one element per invoice.
I'd go for a new table or cursor instead, when the report is used to print multiple invoices in one go, but one step after the other.

Chriss

 

[madalini]

Thank you so much, Chris! You are great and I really appreciate your support!

Indeed, I have no access to programing side. I can access only reports and I find no place to add one more variable, even those for printing. The report is printing one invoice at a time. I know it is an abuse of your time but let me tell you context. The fiscal law it is changed and starting with 01.01.2024 we have to load all invoices into the Fiscal Authorities portal. It is called "e-factura". Because I have 1-2-3 invoices on low amounts, I use OpenOffice.org, having own design with that "Total Amount" on header. Now, I have to use an external app to load and validate these invoices on that portal. I found a free app BUT I can't access any support. It is better to use an app because I can automate the upload and checking when the invoices were validates and ready to download to send to customer. I thought it would be great to keep same format with the one I have (as in pics above) to have no confusion but it seems to be a bad idea.

Once again, thank you for your time and support!

 

[Mike Lewis]

I don't know anything about the legal framework in which you operate, but given that this is a legal obligation, and that financial documents are involved, you or your employers should really consider paying someone to solve this problem for you. He or she doesn't need to be a VFP expert. It might well be possible to write a stand-alone program that accesses your VFP database, performs the calculations and prints the invoice. My point is that, given the importance of getting this right, you should be prepared to pay for it.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[madalini]

Thank you, Chris! Yes, you are right!

 

[Chris Miller]

Thanks for sharing these details. I don't come to a conclusion though. Does that all mean that changing the report would still not suffice the overall goal, so the thread can be closed?

You do seem to have access to FoxPro, or how did you made the screenshot with the report in design mode? Is it a feature of the application to allow you modifications? I know there is a possibility to allow a user to see a report for limited editing MODIFY REPORT with a clause called "PROTECTED" and for the developer having defined the report that also means he can protect or unprotect parts of the report. You may never have the ability to get into report variables and can only change label texts. Well, I then wonder how the total in the page header was constructed. By definition it can't print a total, as that can only be known after the deteail band of the repot has printed, which prints after the header, of course. So that design can't work.

One experiment seems to still be open and untried: Setting this to IIF(_pagetotal>0,val_vd+tva_vd,0):

Chriss

 

[Chris Miller]

One completely other way of getting at the data you want is, that the application you have surely stores all your invoices and data like that in a database, bei it a Foxpro database with DBFs or any other. Then you should always have means to get your sums from there, even a total over a fiscal year or other statistics you need. You just need to know where the data is stored and find out in which tables you see your details about sales and you can compute whatever you want, not just make this micro optimization of having all totals in the head of documents.

Chriss

 

[Chris Miller]

To give you an example of how you could summarize your data for fiscal reporting or bilance sheets:

Select sum(qty*price) as salesincome from orderdetails where orderid in (Select id from orders where orderdate between date(2023,1,1) and date(2023,12,31)

or, if you want the totals per order and not just one overall total:

Select orderno, sum(qty*price) as ordertotal from orderdetails where orderid in (Select id from orders where orderdate between date(2023,1,1) and date(2023,12,31) group by orderid

Untested, and surely will differ from what you actually need depending on how your application developer strcutured the data, but just to show how little SQL can show you the data you need without going through cumbersome harvesting of data from the documents you have printed, only.

The data of a business is the core of everything, even of starting to program a new application, if the original programmer isn't available anymore. And there are many tools out there for generally connecting to databases and working on them with queries.

Stonefield Query (

https://www.stonefieldquery.com/)

is one that I would think of first, as it supports FoxPro data, but not only Foxpro data. It's by Doug Hennig, who also posted here.

And, last not least, when this application is a general business software sold in your country, you're not the only one with the need to comply to the new fiscal law. If the vendor isn't gone out of business that software should have already beed adopted to accomodate that new need or that vendor will lose customers, wouldn't he?

Chriss

 

[madalini]

Thank you, Chris and Mike! You are awesome! And I appreciate your time and dedication. I will follow your advice, both of them and of both of you. I think the thread could be closed.

 

[mjcmkrsr]

Hi madalini,

If you're using VFP 9 and if by chance you have access to the book "What's New in Nine", you may want to have a look at Ch 5 - p. 111-113 : "Pre-calculation of totals".
hth

MarK

 

[madalini]

Thank you, Mark! You are great! Your suggestion worked! I owe you (all!) a beer!

Madalin