Solved

Show last page column totals on next page as opening balance

Posted on 2009-06-27
24
729 Views
Last Modified: 2013-11-26
I am using Crystal Reports that comes bundled with Visual Studio 2008. In one of my report, I have 4 columns of numeric fields. I want to display the total of each column on page footer. This is to be done for every page and also I want to show the totals of the last page on the top of the next page as Balance Forward of prev. page. How can I do that in crystal reports?
Consider an example given below:

Page 1:
Qty       Rate        Qty Per(Unit)        Amount
4000      500        1000                     2000
3400      600        1000                     2040    
7400                                                 4040      

Page 2:
7400                                           4040           (Totals of last page)
Qty        Rate     Qty Per        Amount            
2000      200        1000             400            
1000      400        1000             400            
3000                                           800           (Totals of this page)

10400                                       4840           (Pg.2 footer:  Totals of last page 1 + Totals of page 2)
0
Comment
Question by:vipul4y
  • 8
  • 6
  • 3
  • +3
24 Comments
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
You need to define a running total field, and put the value in the correct place in the page footer, and you should get the proper totals.
0
 

Author Comment

by:vipul4y
Comment Utility
but i need a total of first page on the page header on the next page
0
 
LVL 96

Expert Comment

by:Bob Learned
Comment Utility
That sounds like you need a reset formula for the running total, so that it resets on the page, instead of a section.

Example:

Crystal Reports -- Running total using formulas with variables
http://www.crystalkeen.com/articles/crystalreports/runningtotalvar.htm

"Just place the Reset formula into the Page Header section instead of the report header or group header."
0
 
LVL 3

Expert Comment

by:Alces2
Comment Utility
The following may work:
(modified from http://www.tek-tips.com/faqs.cfm?fid=1295)

Create 3 formulas:

total1:
WhilePrintingRecords;
NumberVar total;
total := total + {qty_field};

total2:
WhilePrintingRecords;
NumberVar total;

total3:
{@total2}-{qty_field}

Place total2 in the page footer, it gives you the total up to there.

Place total3 in the page header.

Note that I do not use the first formula from the link above, as this resets the totals. If you also want page subtotals (so total only of the items on that page), you have to create that "reset" formula, too. But make sure you create a different set of "totals", because you do not want to reset the ones that are supposed to keep accumulating.

If you put total2 in the header, it seems to add already the first line value of that page. That's why I just subtracted the field value once.

i hope my explanations make sense, it's Monday morning after all...

0
 
LVL 5

Expert Comment

by:dhobale
Comment Utility
Insert two duplicate section for page header. Use formula to for creating running total and insert total at page footer. Now on page header (assuming you have 3 sections), in 2nd section, put your running total variable and in 3rd section, reset the variable. This will pull running total from 1st page to header of 2nd page.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
Your post is a little confusing.  You seem to want totals for each page (for just that page), but then you say that in the page 2 footer, you want the combined totals for pages 1 and 2.

 So, do you want individual page totals _and_ totals for all pages so far at the bottom of each page?  Or were the combined totals for pages 1 and 2 supposed to be grand totals shown at the end of the report (ie. in the report footer, not the page 2 footer)?

 James
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Are you looking to put the total of the previous page in the page header or the current running total?

If you put the running total field in the page header do you get what you want?

mlmcc
0
 

Author Comment

by:vipul4y
Comment Utility
Actually i want the running total, but in the form of, like if the created report page count is 1 then it will show in the page footer but if the report page count is more than one then the running total of page 1st is required at page header of page 2 and the total of the header running total and the balance on the 2nd page running fields on the page 2 footer.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You can put the running total inthe page header
Right click it
Click FORMAT OBJECT
Click the COMMON Tab
Click the formula button the the right of SUPPRESS
PageNumber = 1

mlmcc
0
 

Author Comment

by:vipul4y
Comment Utility
thanx for your solution, i think i m near about but there is some more confusions like:-
1. first of all how do i put the if condition in formula if i want that, the bottom running totals will show on second page if the created report pages are more then 1 otherwise on 1st page.
2. as per your solution when i putting the running total in page header and suppress it to page 1 then it showing me some wrong total on second page. i think it includes the the 1st row of the 1st page. i just want the 1st page total on the second page.
0
 
LVL 3

Expert Comment

by:Alces2
Comment Utility
Have you tried my suggestion above with the 3 formulas?
That lets you differentiate between the header and footer total, and subtracting the value once from total2 eliminates the the problem of including the 1st row value.
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:vipul4y
Comment Utility
no i didn't tried that solution.
actually i think i m not understanding the syntax's of the formula's. when i m trying to put the formula that,
{RTotal0} = {RTotal01} - {RTotal0}
then it's showing an error that something is missing. could u tell me that how can i put the formula for that so that it can eliminates the the problem of including the 1st row value coz after this my problem will be solved.
0
 

Author Comment

by:vipul4y
Comment Utility
the RTotal0 is an running total at page header and the RTotal01 is running total at page footer.
0
 
LVL 3

Accepted Solution

by:
Alces2 earned 500 total points
Comment Utility
In your formula above you have {RTotal0} = {RTotal01} - {RTotal0}, so A=B-A, that doesn't look right because you are subtracting the result, so you are going in circles.

So for you it should be like:

1. Formula RSum  (with RSum just a plain old running sum):
WhilePrintingRecords;
NumberVar total;
total := total + {your_amount_field};

2. Formula RTotal01 (page footer)
WhilePrintingRecords;
NumberVar total;

3. Formula RTotal0 (page header)
{@RTotal01}-{your_amount_field}  

Formula 3 (RTotal0) this takes the footer summary and adjusts it by subtracting the current record. That way, when putting it into the header, it takes out the first line record again (after adding it in through the running sum, because it evaluated already the first record when the new page starts).

"total" is just a randomly selected variable name. Call it anything, but keep it the same in formulas 1 and 2.

Place RSum in the detail section so that it shows up for each record you want to add. Suppress if needed.

Place the Rtotal0 and 01 in footer and header, respectively. That should do the trick for you.


0
 

Author Comment

by:vipul4y
Comment Utility
thanx a lot buddy i got my proper solution.
i just want to know 1 more thing that there is 1 more running total with text field i just want to display it on the last page not on the first or any other page if the created report pages are more then 1 and if it is just one then on first page. how do i do it?
0
 

Author Comment

by:vipul4y
Comment Utility
that is a grand total
0
 
LVL 3

Expert Comment

by:Alces2
Comment Utility
Not quite sure if I understand you right.

You have a text field (string) that you want to build a running total from? Or just the grand total?
And that should only be on the last page (which is also the 1st page for 1-page reports)?

You would have to use a formula field to convert the string into a number, Crystal syntax: ToNumber(your_string)

And then just place that as a sum in the report footer, which will be automatically at the end of the report.
0
 

Author Comment

by:vipul4y
Comment Utility
you are quite right and quite worng, actually the thing is the grand total which i want, have to be placed in the page footer coz formatting of the report needs that. And it has a text label also with it now i want that if the report has more than 1 page than i want to these fields suppress exclude from the last page.
0
 
LVL 3

Expert Comment

by:Alces2
Comment Utility
Does this work for you:

Create a formula field, let's call it grand_total:

sum ({your_amount_field})

Then put this field in the page footer:

To suppress on the first page if there are more than 1 pages:
Right-click the field, format field. Then check off "suppress" and apply the following formula with the x-2 button:
(TotalPageCount > 1) and (pagenumber = 1)

0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
I very well may have missed something, but it looks to me like Alces2's post # 24841831 was the solution.  The OP's next post starts with "thanx a lot buddy i got my proper solution".  So I think that answered his original question.  Then he asked a related question and that was not resolved, but it appears that he did get a solution to his original question.

 James
0
 
LVL 3

Expert Comment

by:Alces2
Comment Utility
Thanks for stepping in, James.
0
 
LVL 34

Expert Comment

by:James0628
Comment Utility
No problem.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Suggested Solutions

For those of you who don't follow the news, or just happen to live under rocks, Microsoft Research released a beta SDK (http://www.microsoft.com/en-us/download/details.aspx?id=27876) for the Xbox 360 Kinect. If you don't know what a Kinect is (http:…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now