Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Advanced Subtotalling Post Reply Post New Topic
Author Message
ncsailor
Newbie
Newbie


Joined: 27 Dec 2008
Online Status: Offline
Posts: 3
Quote ncsailor Replybullet Topic: Advanced Subtotalling
    Posted: 27 Dec 2008 at 7:02am
It has been years since I have had to use Crystal.  We are trying to put together a report that uses a large database? (in Access) that will count specific occurances of a specific item within a group so that we can create a manufacturing pull list.
I have set up a group on the cabinet number and a secondary group on the item name.  The report currently looks like this:

Group (Cabinet Number)
  Group (Item Name)           (count of item within cabinet group)

This seems to work except that the count is not correct.  I have tried resetting all of the options for the subtotal but nothing appears to work properly.  I have also tried to use a different method by inserting a summary of the item name group.  This would appear to work except that the summary just keeps repeating the last item in the cabinet.  Unfortunately the online documentation is similar to MS in that it is useless.  I want the report to look like this:

Cabinet 1
  Brown offset hinge                                                 4
  silver door pull 1/4"                                                2

Cabinet 2
  Brown offset hinge                                                  2
  silver door pull 1/4"                                                 1

Totals
  Brown offset hinge                                                  6
  silver door pull  1/4"                                                3


Any ideas on the right way to get subtotals and totals to count properly?
R.A. Williams
ncsailor62@gmail.com
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 29 Dec 2008 at 6:28am
I guess I am missing what the report looks like now, but as a general answer when I have 'issues' with subtotals (usually due to duplicated data) I create formulae.
 
the basic idea that I try to follow is to zero out my counter(s) when the group changes, then as the detail is displayed, I increment my counter(s).  Finally at the group footer, I display my counter(s).
 
Shared variables assist in persisting the numbers between detail lines.
 
And I agree, most help systems aren't worth the time it took someone to create them, much less look at, as they don't clear up anything.
 
Hope this helps.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 29 Dec 2008 at 7:24am
Are you doing a count or a distinct count and which field are you using to get your summary?
If you have a field in your DB that is an unique value per row (usually your primary key to prevent duplicate records that is just an incrementally increasing number value) you can use that as a the field to summarize to get your totals.
Create a summary on that field. Calculate it as "Distinct Count" on group 2. Moved the created summary field (defualts to group footer 2) into group header 2. This will count every instance where "item Name" is the same under each "Cabinet Number". You can do the same thing to get a total number of items in each cabinet. Just do another summary of the same field as a the same distinct count on group 1.
IP IP Logged
ncsailor
Newbie
Newbie


Joined: 27 Dec 2008
Online Status: Offline
Posts: 3
Quote ncsailor Replybullet Posted: 30 Dec 2008 at 11:58am
Thank you for your help.  I was fooling around this weekend and finally got things to work as I wanted.  Basically I used the same procedure that you were suggesting.  Wish that I had read my email earlier......
Thank you again for the response.
R.A. Williams
ncsailor62@gmail.com
IP IP Logged
ncsailor
Newbie
Newbie


Joined: 27 Dec 2008
Online Status: Offline
Posts: 3
Quote ncsailor Replybullet Posted: 30 Dec 2008 at 12:01pm
Thank you for the assistance.  After spending the weekend playing around I got the subtotals to work properly.  I had another report, provided by the software vendor, which shows totals.  I have been trying to use a modified version of that as a subreport to get the totals in place but it appears to use a different SQL query or something that causes its output to be completely incorrect.  I have some books on the way so I am hoping to get it worked out quickly.  I guess my other alternative is just to use two different reports, one for the "pull list" and one for the manufacturing report.
Thanks again
R.A. Williams
ncsailor62@gmail.com
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.