Sum formula not working

[irenavassilia]

I need to create a formula in crystal reports with the following condition:

select sum(quantity), itemnumber from uxdetail where fiscalyear = '2006' and fiscalperiod between 1 and 4 and itemnumber = '00112X25000023' group by itemnumber

The quantity field is on the Detail line and the fiscalperiod will be between the parameter entry.

This is my formulas but all i get is 0's:

if {UXDETAIL.FiscalYear} = {?Year} and {UXDETAIL.FiscalPeriod} in 1 to {?Month} then
sum({UXDETAIL.Quantity})


Any help would be greatly appreaciated.

 

[lbass]

Your formula should be the following, and should be placed in the detail section:

if {UXDETAIL.FiscalYear} = {?Year} and {UXDETAIL.FiscalPeriod} in 1 to {?Month} then
{UXDETAIL.Quantity}

Then right click on this and insert a sum on it at the group level or report level. If you want to look only at the specific itemnumber, build that into the formula as well:

if {UXDETAIL.FiscalYear} = {?Year} and {UXDETAIL.FiscalPeriod} in 1 to {?Month} and
{table.itemno} = '00112X25000023' then
{UXDETAIL.Quantity}

The reason you are getting zeros now is that you are placing the formula in a footer section which if evaluating only the previous detail row and if it meets teh criteria, it will give you the sum for all rows, which isn't what you want.

-LB

 

[irenavassilia]

I thought for sure this would work but its still giving me all 0's.

Makes no sense because i know there are values for that condition through the sql query...

Not sure what else it could be.

 

[irenavassilia]

The sum formula needs to be put in the detail section.
And for each record i need to sum the quantity from prior year to date.

so whatever the year is lets say 2007 and period 4 it means sum the quantity between january and april for that year.

if {UXDETAIL.FiscalYear} = {?Year} and {UXDETAIL.FiscalPeriod} in 1 to {?Month} then
{UXDETAIL.Quantity}

^^ Funny thing about that formula is it needed a true before the quantity, thats why it wasnt working.
i.e.
if {UXDETAIL.FiscalYear} = {?Year} and {UXDETAIL.FiscalPeriod} in 1 to {?Month} then
true;
{UXDETAIL.Quantity};

Anyways if anyone has any idea how to get the correct sum and put it in the detail line please let me know thanks a bunch.

 

[dgillz]

You typically do not write formulas to get sums in crystal. LB's approach is correct. Write a formula to display the requested value if the 2 conditions are met, or zero if they are not met. Place this in the details section. Suppress it if you do not want to see the field there. Then right click, insert, grand total, or group, as the case may be.

Please also advise your crystal version, and how you created your formula. The "true;" should not be needed.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[irenavassilia]

Hi dgillz,

I did LB's approach and it didnt work. This formula is in the detail line and when i click on insert i have two options - summary and grandtotal.

1) Summary is kind of useless here because you can only put it on a group.
2) Grandtotal is useful but the problem is in the detail line the formula is not producing the correct values, and without the true; i have no values at all 0's again...

I still dont understand why i'm getting all 0's i know for a fact there are lots of quantity values between that selection... (puzzled)

I'm using Crystal Reports 10 btw.

*Sigh* Anyone know any really good Crystal Reports Books???
I'm getting a hang of it but obviously not as fast as i wanted.

Thanks.

 

[irenavassilia]

Sorry:

You can use the summary and assign it in a formula but the numbers are still incorrect.

 

[dgillz]

Place {UXDETAIL.Quantity} in the detail section to see if all that displays are zeros.

If you are not summarizing a group, what are you summarizing?

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[lbass]

Please show some sample data and also show the summary you are trying to display. Are you trying to show a running total?

-LB

 

[irenavassilia]

Ok so i ran the following sql query in the sql analyzer:

select itemnumber, quantity, soldhome
from uxdetail
where fiscalyear = '2006'
and fiscalperiod between 1 and 7
and custgroupdesc = 'Retail'
order by itemnumber

This is the output:

ItemNumber Quantity SoldHome
======================== ===================== =====================
00101X4KG00100 4.0000 52.520
00101X4KG00100 4.0000 52.520
00101X4KG00100 4.0000 52.520
00101X4KG00100 8.0000 105.040
00101X4KG00100 4.0000 0.000
00101X4KG00100 2.0000 26.260
00101X4KG00100 4.0000 52.520
00101X4KG00215 1.0000 0.000
00101X4KG00215 1.0000 0.000
00101X4KG00215 1.0000 0.000
00101X4KG00215 1.0000 0.000
00101X4KG00215 1.0000 0.000
00101X4KG00215 1.0000 0.000
00101X4KG00215 1.0000 0.000
00101X4KR00100 1.0000 0.000
00101X8KG00101 10.0000 246.000
00101X8KG00101 2.0000 0.000
00101X8KG00101 4.0000 98.400
00101X8KG00101 10.0000 246.000
00101X8KG00101 1.0000 24.110
00101X8KG00101 10.0000 246.000
00101X8KG00101 10.0000 246.000
00101X8KG00216 20.0000 495.000
00101X8KG00216 25.0000 618.750
00101X8KG00216 25.0000 618.750

Theres many more but i think this will give you an idea.

So lets say i need Year Prior To Date:

Here is the formula in the detail line:

numbervar YearMinus;
YearMinus := (tonumber({?Year})-1);

if {UXDETAIL.FiscalYear} = {?Year} and {UXDETAIL.FiscalPeriod} in 1 to {?month} then
{UXDETAIL.Quantity};

Its giving me all 0's.

I'm out of ideas.

 

[irenavassilia]

Sorry the year is 2007.

 

[irenavassilia]

I dont understand whats going on with my detail line everything if condition that includes year and month criteria needs a true; before the field... which isnt right i should be using it like you've said before...

WEIRD

 

[dgillz]

Please re-read my post. I did not ask you to run a query in SQL query analyzer, I asked you to place the field {UXDETAIL.Quantity} in the detail section of the crystal report, to see if crystal is picking up the value properly.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"If the phone doesn't ring, it's me".....Jimmy Buffet

 

[irenavassilia]

Yes its picking up the values.

I fixed it, i displayed the fiscal year and fiscal period on the report which where way off due to incorrect conversion that i had.

So it wasnt reading the data properly.

Thanks.