Print Page | Close Window

Problem with formula field - constantly returns 0

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Writing Code
Forum Discription: .NET programming API, report integration
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17238
Printed Date: 03 May 2025 at 2:28pm


Topic: Problem with formula field - constantly returns 0
Posted By: NelP
Subject: Problem with formula field - constantly returns 0
Date Posted: 07 Aug 2012 at 11:39pm
Hi,

I have a Crystal Report which uses data from a Data Table.
I have grouping and calculating sum within the groups. The grouping is by the field vid.

I want to perform calculation where if vid="Pri" the field izn of that row should be summarized, and if vid="Isp" then the field izn should be summarized and then subtracted from the first sum (when vid=Pri).

For the first sum (if vid="Pri") the calculation is ok (for the group with vid="Pri"), but if vid="Isp", (for the group with vid="Isp"), the result of the sum function is always 0, i.e. it doesn't calculate correctly. So I can not perform the last calculation (summarize when vid is Pri and subtract the sum, when vid=Isp).

The first formula which calculates correctly is:

if({DataTable3.vid}="Pri") then Sum ({DataTable3.Iznos}, {DataTable3.vid})

and the formula for the other sum, when vid=Isp is

if({DataTable3.vid}="Isp") then Sum ({DataTable3.Iznos}, {DataTable3.vid})

Can anybody help me please what is the mistake I make in the bold line for having the result 0, instead of the correct result?

Thank you in advance


Replies:
Posted By: hilfy
Date Posted: 08 Aug 2012 at 5:11am
First, you'll need to create two formulas, I'll call them {@Pri} and {@Isp}:
 
{@Pri}
If {DataTable3.vid} = 'Pri' then {DataTable3.Izno} else 0
 
{@Isp}
If {DataTable3.vid} = 'Isp' then {DataTable3.Izno} else 0
 
You'll then create a final formula that will do the rest of your calculation.  (I'm assuming that you're doing this for the whole report, not some internal group.)
 
Sum({@Pri}) - sum({@Isp}) 
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: NelP
Date Posted: 09 Aug 2012 at 9:30pm
I did it like that, similar to your idea.

I made two formulas:
@Vk pri and
@Vk isp.

@Vk pri=if({DataTable3.vid}="Pri") then Sum ({DataTable3.Iznos}, {DataTable3.vid}) since if vid=Pri it should return the sum of Iznos of rows where vid=Pri for that group; and

@Vk isp=if(GroupName ({DataTable3.vid})="Isp") then Sum ({DataTable3.Iznos}, {DataTable3.vid}) which should also return the sum of the group if vid=Isp.

I tried this as well:

@Vk isp=if(GroupName ({DataTable3.vid})="Isplata") then Sum ({DataTable3.Iznos}, {DataTable3.vid}) but again I got 0 as a result for the formula.
Actually the both formula should be almost the same, since they differ and should return different values depending whether the group has value Pri for vid or Isp. But I don't know why the first formula returns correct result and the second doesn't. What's wrong with the second formula (@Vk isp)?

I got correct sums for the groups separately. But when implementing the sum in the report footer I can't get correct result for almost the same formula (only different value for "vid" field).

The first formula (@Vk pri) works fine, but the second don't. This is my problem.
And I want to have at the end @Vk pri - @Vk isp as a result formula, but @Vk isp is always 0.
And this all formulas should be in the report footer
Thanks in advance

Posted By: hilfy
Date Posted: 10 Aug 2012 at 3:10am
DO NOT do the Sum in your IF Statement!  That will NOT work.  Follow the steps I gave you to create the formulas without the sum and then sum those formulas at the end of the report.
 
The reason it won't work your way is that it's doing the sum based on the value of VID in the last record of the report.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: NelP
Date Posted: 13 Aug 2012 at 12:54am
THANK YOU VERY VERY MUCH Hilfi!!!


Print Page | Close Window