sub totals and grand totals

[yolly12]

environment - GoldMine 6.7 GMCE - SQL 2000, Crystal Reports 9 pro.

I am writing a report that at first I thought I could do as a cross tab but none of the formulas would work so I ended up taking another approach. below is what the end result is suppose to be;

product: total fred barney goerge

Fabric - Misc
buttons 5 -10 0 15
zippers -8 10 -8 -10

Fabric
cotton-blue 5 0 5 0

you get the idea. Where the data is stored is hard to get to and they want the products to be listed in subcategories that are not tracked anywhere in the program and in a particular order. then when the sell something sells, they complete the activity with a SLD code and if the product sells then cancels, the same completed activity then gets the code changed to CAN. code btw.. is a field.

so this is what I did. I first created a formula for the categories that aren't tracked anywhere in the program.

if {conthist.ref} startswith "BUT" then totext ("Fabric - Misc") else
if {conthist.ref} startswith "ZIP then totext ("Fabric - Misc") else
if {conthist.ref} startswith "CBL" then totext ("Fabric") else unknown

the reason why startswith needs to be used is that GoldMine attaches everything to contacts so it inserts the contact's name after the actual thing entered in the field. I took this formula and made a group for it - categories taken care of.

then the {conthist.ref} needed to be translated into proper name so I created another formula:

if {conthist.ref} starts with "BUT" then totext ("Buttons") else - and so on. I took this formula and made another group.

instead of creating a seperate formula for each user and each product I came up with 4 (instead of 50 per user). Not only did I have to find the activities but I must sub total the SLD and sub total the CAN and display the end result for each product.

(to find the SLD and CAN)
if {conthist.userid} = 'BARNEY' and {conthist.code} ='SLD' then tonumber ({conthist.units}) else 0

you get the idea - wont type the one for CAN. then I made one for the TL

(sum({@BARNEYWON}, {@prods}) - (sum({@BARNEYLOS}, (@prods})

I placed thw WON/LOS in details and suprssed it and I placed the TL formula in the group header for the second groups which had the product translations. works great.

here is my problem - I need to do a subtotal (add up all users TL per product (second group) AND the category (take subtotals of the per product and give sum) and then come up with a master grand total.

I placed formulas in the group footer of the first group and it is not working (sum(@BARNEYWON)) - (sum(@BARNEYLOS)) and it gives a number for all users instead of what the WON/LOS formula says (specifically lists userid)

so where am I going wrong here?

thanks btw for helping

 

[lbass]

If your first group is {@categ}, then your formula should be:

sum({@BARNEYWON}, {@categ}}) - (sum({@BARNEYLOS}, (@categ})

-LB

 

[yolly12]

I get a message when I tried that.

can do summary on this formula (or was it function)

I think I even added at the top whileprintingrecords;

thinking it was when the formula was being evaluated.

 

[lbass]

You should NOT add whileprintingrecords to any of these formulas.

-LB

 

[yolly12]

thanks for the tip.

I only dd it when it told me that the category summary wasn't going to work.

I wonder if I need to add another formula for the group?

if group1 = "Fabric" then "fabric"
and so on.

I wonder if the classifications that I am doing in group1 is too much to process at the time of making a sum.

 

[lbass]

Please identify the name of the field/formula you are using for your group #1--that is what needs to be in the formula where {@categ} is.

I'm assuming that {@Prods} is your second group.

Note that you don't have to wrap hard coded text in totext(). You should just be writing:

if {table.field} startswith "BUT" then "Buttons"

-LB

 

[yolly12]

it is exactly as I put it;

if {conthist.ref} startswith "BUT" then totext ("Fabric - Misc") else
if {conthist.ref} startswith "ZIP then totext ("Fabric - Misc") else
if {conthist.ref} startswith "CBL" then totext ("Fabric") else unknown

 

[lbass]

I asked for the name of this formula. Replace {@categ} in my earlier formula with the name.

-LB

 

[yolly12]

the name of the formula is {@groupie}

(sorry, misunderstood you)

So I replaced my the formula on the group 2 sums with the groupie formula

(sum({@BARNEYWON}, {@prods}}) - (sum({@BARNEYLOS}, (@prods})

changed to

(sum({@BARNEYWON}, {@groupie}}) - (sum({@BARNEYLOS}, (@groupie})

you'd think that would be all there is to it but it gives that msg about not being about to summarize this formula

 

[lbass]

You would use the formula with {@prods} at your group #2 level, and the formula with {@groupie} at the group #1 level.

You need to show the contents of all formulas within each of these summaries, so we can determine why you are getting this message, i.e., the contents of:
{@BarneyWon},{@BarneyLos},{@prods},{@groupie}. There is something you are not telling us.

Can you verify that you have inserted groups on {@prods} and on {@groupie}?

-LB

 

[yolly12]

groups are in the right places. The {@BarneyWon},{@BarneyLos},{@prods},{@groupie}, {@BarneyTL} all work correctly.

honest I swear on a pile of good chocolate - I have written everything that those formulas consist of.

wait! I see something but it isn't going to count much;

if {conthist.userid} = 'BARNEY' and {conthist.code} ='SLD' then tonumber ({conthist.units}) else 0

is;

for @BARNEYWON

if {conthist.userid} = 'BARNEY' and {conthist.srectype} ='S' and {conthist.code} ='SLD' then tonumber ({conthist.units}) else 0

for @BARNEYLOS

if {conthist.userid} = 'BARNEY' and {conthist.rectype} = 'S U' then tonumber ({conthist.units}) else 0

As I said they work fine with the other parts of the report.

 

[lbass]

I don't see the content of {@prods} anywhere, and you haven't answered about whether you have inserted groups on the {@prods} and {@groupie}.

Where exactly are you creating the formula:

sum({@BARNEYWON}, {@groupie}}) - (sum({@BARNEYLOS}, (@groupie})

You should be doing this in field explorer->formula->new. What is the exact error message you are getting?

-LB

 

[yolly12]

this is what @prods is:

if {conthist.ref} starts with "BUT" then totext ("Buttons") else - and so on.

(sum({@BARNEYWON}, {@prods}) - (sum({@BARNEYLOS}, (@prods})

I placed the @BARNEYWON/@BARNEYLOS in details and suprssed it and I placed the TL formula in the group header for the second groups which is @prods. works great

When I create the formula to change @prods to @groupie, I replace the formulas and then push the save button. The message says - cannot summarize this field

 

[yolly12]

by the way - thanks a whole bunch for your help

 

[yolly12]

okay they did maintenance on the server and magically everything is working.

but they did ask for something else. I *think* it can be done.

The category group - @groupie;

if {conthist.ref} startswith "BUT" then totext ("Fabric - Misc") else
if {conthist.ref} startswith "ZIP then totext ("Fabric - Misc") else
if {conthist.ref} startswith "CBL" then totext ("Fabric") else 'unknown'

They want to do totals of the groups but not in the way ya think. Fabric Misc, Fabric, Fabric notions, things like this are defined in @groupie. There are about 25 of these and there are Machines, Plastics - other groups. They would like to be able to take say anything that starts with FABRIC and do a total off of that.

so it would be Barney's total sales in anything that startswith Fabric.

I am guessing that a formula of some sort would to be done to tell the summary, yes look at group1 and anything that startswith fabric add up.

 

[lbass]

I think you could just create detail level formulas like:

if {conthist.userid} = 'BARNEY' and {conthist.srectype} ='S' and {conthist.code} ='SLD' and
{@groupie} startswith "Fabric" then tonumber ({conthist.units})

//etc.

-LB