Running Total on Groups 1

[Dominican1979]

Hello Everyone,

Thank you for reading my post. I have a question regarding how to create running totals on groups. I have a report that lists products by product type and also by price. Currently I have the report setup like this on the detail section of the report:

Qty ProductType Descr Price Total
2 Metal Eyewear 1645 Red Mate 16.24 32.48
1 Metal Eyewear 1450 Orange Mate 16.24 16.24
3 Metal Eyewear 1742 Blue Mate 17.42 52.26
1 Plastic Eyewear 1350 yellow 13.50 13.50
2 Plastic Eyewear 5433 Orange 13.50 13.50

Now, I want to group these by product type then by price so it would look something like this:

Qty ProductType Price Total
3 Metal Eyewear 16.24 48.72
3 Metal Eyewear 17.42 52.26
2 Plastic Eyewear 13.50 27.00

So I created two groups, ProductType and Price, then I placed the Product type on the group #1 header and supressed the group #2 header/footer and also group #1 footer. I can see the groups for product type just fine. I created two running total fields 1 for quantity Sum(Qty) for every record, reset on group change (group #1 product type) and that doesnt work, the same i did for the price, and that doesnt work either, Any Ideas on where I may be going wrong?

Thanks a lot

Crystal XI R2

 

[lbass]

You should be grouping on Product Type and then on Price and dragging the Product Type name into the Price group footer (along with the Price group header). Then set the running totals to sum qty and to reset on change of group: price. Place the rt in the Price group footer.

-LB

 

[Dominican1979]

Hi lbasss thanks a lot for your help again. So this is what I did. I put all the fields on the group footer #2 (the price group footer). I set the running total for quantity the way you said and it works great, however the total differs for most lines by cents. Here is how I have the formulas setup:

Item Price Formula:
{@ItemPrice} = {TableField.Price} * {@Discount} ---> (there's a 25% off and this works fine on the price grouping)

Discount Formula:
{@Discount} = 0.25


Now, I created a Total formula to calculate the total amount for each group:

{@TotalAmount} = {#ItemTotal} * {@ItemPrice}

I placed this formula field on the same group #2 footer and for each group, the whole number part is correct, but the decimals differ for each line between 0.01 - 0.25 cents. The weird thing is that out of 12 total lines on the report, 4 are correct as far as the total amount goes, any ideas where I could be going wrong?

Thanks a lot!

 

[lbass]

You're probably seeing some rounding differences, since you are applying the formula to a total instead of to individual items which you then total. Shouldn't you be applying the discount per item?

Also you don't really need running totals (I should have mentioned that earlier). You could just use formulas like this:

//{@sumqty}:
sum({table.qty},{table.price})

-LB

 

[Dominican1979]

Hi lbass,

Thanks again for your help, I replaced the running total with the formula above for the item quantity and works fine, however I tried this formula backwards to total each line, but get an error message:

sum({table.price}, {table.qty})

The error I'm getting says "There must be a group matching this field" I can't seem to be able to total and also a grand total

 

[lbass]

The second argument is where you put the group field, so if you want a grand total, you just remove the argument:

sum({table.price})

If you want to sum the price at the price group level, it would be:

sum({table.price},{table.price})

-LB

 

[Dominican1979]

Hi lbass,

I replaced the running total for the item quantity with the formula you suggested above and it works fine, however:
sum({table.price},{table.price}) does not work for my totals for each price group becuse its adding the records, but does not take into account the quantity under for each record. for Example I have the following record details (they all belong to the same price group) but I wanted to show you the details:

Prod No Qty Price
1196 9 59.95
1196 2 59.95
1196 1 59.95

On the formula for the item total you gave me
sum({table.price}, {table.qty}) I get a total quantity of: 12 which is great, however for the price total I get: 179.85 instead of: 719.40

I tried creating the following formula:

//{@sumamt}:
sum(({@Price} * {@ItemTotal}), {@Price})

because I need to multiply however many on the price group line times the price to get the total amt for that group, but I get an error saying "a field is needed here) when I check for errors on the formula.

 

[Dominican1979]

Hi Again lbass, the only way I've been able to do it is with this formula:

{@ItemTotal}*{@Price}, but I get all the rounding errors

 

[Dominican1979]

Hi lbass, I can't figure it out, isn't there a way to just multiply what shows on the group line for the item total by what shows on the group line for the price? When you group something on a number does that have an effect on the decimals? This is the last thing I need to be done with this report and its driving me nuts, the total for each group and the grand total for all the groups.

 

[lbass]

Create a formula like this:

{table.item}*{table.price}

This will return results at the detail level. Place the formula in the detail section and insert a sum on it at the group level or grand total level.

-LB

 

[Dominican1979]

Hi lbass,

so I created a new formula below called: {@DetailPrice}

({table.price} * {@Discount}) * {table.qty}

and placed on the detail section on the report, I then created the following formula:

SUM({@DetailPrice}, {@Price})

and placed on the price group footer, and I'm getting the exact same results as before with the decimals being off.


So basically I'm getting the price from the database. From that price i'm multiplying it by the discount which is 0.25 and then grouping on that discounted price which works fine, I don't know where it goes wrong. I'm not sure how where I should be applying the discount at?

I swear I've worked on more complicated reports in which you have helped me, and I haven't had soo much trouble on something that seems to be so simple, I guess it's proving me wrong lol.

I even tried doing the price grouping on my database view, but still getting the same result. I'm using SQL Server 2008

 

[lbass]

I think that whether you apply a discount per product or per invoice is a business decision--so you need to find out the correct way to go.

Why not remove the discount, and then look at the results. You can also round the results at the detail level to eliminate the effect of the decimals, e.g.,

round({table.price}*{table.qty}*.25,2)

Then insert a summary on that.

What is in the discount formula anyway?

-LB

 

[Dominican1979]

Hi lbass,

It's funy you mention that cause I'm doing the discount right on the SQL server instead like this ROUND(dbo.table.SalePr * 0.25, 2)

the discount formula only had .25 stored on it, probably wasn't the best way but I just thought might as well store it in a formula in case I have to changed it later, only have to do it in one place. I'm in the process of making the changes and I will let you know how it goes. Thanks a lot again

 

[Dominican1979]

Hello lbass,

I just wanted to let you know that I'm finally done with the report, here's what I ended up doing:

I did the rounding like this ROUND(dbo.table.SalePr * 0.25, 2) on the SQL server side and just brought in the price which I grouped on. I then created a formula to get the grouping total and placed it on the group #2 footer and this works great without the rounding errors.

{@ItemTotal}*{table.SalePr}

I then created another formula and placed it on the details section: {@DetailsTotal}

{table.Qty} * {table.SalePr}

Then I created another formula and placed it on the report footer to get the grand total:

SUM({@DetailsTotal})

Thank you sooooo much for sticking around and helping me out, I greatly appreciate it, I've been dealing with this for a week, I can't believe it took me this long, I'm glad it's over with now, have an awesome day!

MS