Normal Topic Subtotals in Reports (Read 1694 times)
rtaaop
Member
*
Offline


No personal text

Posts: 27
Joined: Dec 27th, 2003
Subtotals in Reports
Jan 18th, 2004 at 9:39pm
Print Post Print Post  
How do I get a subtotal after a particular field changes in a report? For example if location = CA I want a subtotal of sales in CA when location changes to OH and ditto for OH when Location changes to TX. I thought I was following instructions in the manual but I see no subtotals. I am attempting a columnar report.
  
Back to top
 
IP Logged
 
TJCajun
Junior Member
**
Offline


"Laissez les bons temps
rouler"

Posts: 72
Location: Louisiana
Joined: Nov 25th, 2002
Re: Subtotals in Reports
Reply #1 - Jan 19th, 2004 at 12:09am
Print Post Print Post  
I can't offer any specifics of something to try.  Looking at page 378 of the User Guide indicates that there are two (2) things that have to be set.  Pretty much quoting here:

"Set the Break Element to the LE that controls the subtotals (such as Company) and set the Break Value to control the actual break. "

Be sure to go further in the User Guide, and read the "tooltip" that is displayed in Figure 37. (Which opens over the Break Value).  From what I see, you need a Break Value of 0 (zero) because you want the break to occur whenever your field (i.e. layout element) which is called Location changes values.

  
Back to top
IP Logged
 
rtaaop
Member
*
Offline


No personal text

Posts: 27
Joined: Dec 27th, 2003
Re: Subtotals in Reports
Reply #2 - Jan 19th, 2004 at 2:28pm
Print Post Print Post  
Thanks for your response. I did follow the instructions you mention but somewhere along the way I screwed it up. I am not getting the subtotals. I do thank you for mentioning the "tooltip."
  
Back to top
 
IP Logged
 
Hammer
YaBB Administrator
Lanticans
*****
Offline


Fire bad. Tree pretty.

Posts: 3436
Location: Ohio
Joined: Nov 22nd, 2002
Re: Subtotals in Reports
Reply #3 - Jan 19th, 2004 at 4:14pm
Print Post Print Post  
If your Group Body contains the following elements...
Value Box
Bound To- State
Name- LE0

Value Box
Bound To- SaleAmount
Name- LE1

...then Set the Break Element to LE0 and put an element in the Group Footer...
Summary Calculation
Type- Sum
Bound To- LE1
  

- Hammer
The plural of anecdote is not data.
Back to top
IP Logged
 
rtaaop
Member
*
Offline


No personal text

Posts: 27
Joined: Dec 27th, 2003
Re: Subtotals in Reports
Reply #4 - Jan 19th, 2004 at 9:10pm
Print Post Print Post  
Thanks, Hammer. I have come to the conclusion that I am not bright enough to figure this out. I feel like I have done exactly as you suggest and I am still not getting it. I am looking for the subtotals to appear right under the "location" change. When I put a summary element in Group Footer is this suppose to be placed opposite the "Location?" I have several fields that need subtotals. In Q&A I use "T,ST" in every field that I want a subtotal. "Location" is in my case LE16; break element is LE0. In Group Footer, where along the column do I use the summary calculation? I figure this is about as clear as mud but I do appreciate you wading through it.
  
Back to top
 
IP Logged
 
TJCajun
Junior Member
**
Offline


"Laissez les bons temps
rouler"

Posts: 72
Location: Louisiana
Joined: Nov 25th, 2002
Re: Subtotals in Reports
Reply #5 - Jan 20th, 2004 at 1:44am
Print Post Print Post  
Try the following.  I used the Customers database that came in the Samples with Sesame.  I first made a copy of Customers by:  Opening Customers in Designer, giving a new name to a DSR file, and then saving as a New Application under a different name (NewCust).

Open NewCust in Designer.  Click on Reports, Customers, Add New Customers Report.  The Report Layout opens.

Click in the Group Body column to highlight this column.  Click in Bind Element To and select STATE.  Click in name and enter LE0  (zero).  Click on Add Element to Report.  The Value box should appear under Group Body.  It should contain:  
Value
[LE0]
State

Click on Bind Element To and select NUMERICAL.  (Note that the Group Body is still highlighted  - in yellow on my screen)  Click in Name and change to LE1.  Click on Add Element to Report and a second Value box should appear under Group Body.  It should contain:
Value
[LE1]
Numerical

Carefully click precisely on the highlighted words Group Body, and a window titled Report Section Properties should appear.  Click on the down arrow drop-down next to Break Element and select LE0 (zero) and then click OK.

Click in the Group Footer column to highlight it.  Click on Select Element Type and move down the list to Summary Calculation.  When the mouse pointer is over Summary Calculation, a secondary window opens and you should select SUM from this secondary window.  The word "Sum" will now be in the Select Element Type area.  Click on Bind Element To and select the only entry that should be there - which is LE1.  Click Add Element to Report and a box should appear under Group Footer.  It should contain:
Sum
[LE2]
LE1

Click Save Layout Design (click second time to see the 'flash' that indicates the layout is saved.)  Click Close Design Tab.

You can now either Preview, Reconcile, or Save as another New Application.  Regardless of whichever you do, here's how to see that this worked.

I'm going to Preview.  Open the file in Search/Update.  Sort the records on State by Clicking in the Spec Window on State when "Sort Main Form" is showing.  This moves State to the top of the list with an "A" entered in the box.  You can now click on the heading Sort Main Form and click Run or press F10 to both Search and Sort the entire database.

Now click on Customers under REPORT at the bottom of the menu tree, and you should have two lines for New Report (Preview or Print Immediately)  Pick PREVIEW, or you will start a long printing process of 377 records!  When you pick Preview your HTML report should appear in your Internet Browser.  It ain't pretty, but it is sorted by state, with subtotals after each break on the State abbreviation.

-----Here's how a few lines looked----
 
AR 43.50
AR 58.88
AR 50.71
153.10 
 
AZ 90
AZ 63.71
AZ 81.36
AZ 74.44
AZ 51.87
AZ 79.07
AZ 88.93
AZ 75.56
AZ 101.23
AZ 76.82
782.99
 
---- and so on.  Looks like some rounding is going on - look at the sum value for the 3 values for AR. ----

I hope following these steps, and seeing this subtotal report, will help you understand how to get subtotals to work.  Your appl. sounds more complex, with several subtotals needed.  Sure hope this helped.


  
Back to top
IP Logged
 
rtaaop
Member
*
Offline


No personal text

Posts: 27
Joined: Dec 27th, 2003
Re: Subtotals in Reports
Reply #6 - Jan 20th, 2004 at 3:31am
Print Post Print Post  
You bet, TJ. I think the dawn is beginning. Thank you so much for your thorough, step by step approach. I needed that.
  
Back to top
 
IP Logged