Report Design
 Crystal Reports Forum : Crystal Reports for Visual Studio 2005 and Newer : Report Design
Message Icon Topic: Syntax for Case Statement Post Reply Post New Topic
Author Message
o9z1
Newbie
Newbie


Joined: 01 Feb 2008
Online Status: Offline
Posts: 12
Quote o9z1 Replybullet Topic: Syntax for Case Statement
    Posted: 07 Feb 2008 at 9:15am
I have a field on my report that I want to return like this...Here is my SQL code If I were to do it in SQL.  How can I return the same thing on my report in Crystal Syntax?

SUM(CASE KILSHEET.Contract WHEN 1 THEN ((TOT_CARC_WEIGHT / 100) * 1.75) ELSE 0 END)


Edited by o9z1 - 07 Feb 2008 at 10:23am
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 07 Feb 2008 at 12:05pm
I would create a manual running total and replace the Case statement with an IIF() function.
Global NumberVar ContractTotal;
ContractTotal := ContractTotal + IIF({KILSHEET.Contract}=1, (TOT_CARC_WEIGHT / 100) * 1.75, 0);

Then in the report footer (or where ever you want the total to be printed), put this formula to print the value:
Global NumberVar ContractTotal;
ContractTotal;

I have all the Crystal Reports functions documented with sample code in Chapter 7 of my book Crystal Reports Encyclopedia.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
o9z1
Newbie
Newbie


Joined: 01 Feb 2008
Online Status: Offline
Posts: 12
Quote o9z1 Replybullet Posted: 07 Feb 2008 at 12:23pm
I am getting the following error:

Error in formula <Running Total Condition Formula>
'Global Numbervar ContractTotal;

The field name is not known
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 07 Feb 2008 at 12:44pm
That's weird. You are defining the variable here. Are you using Crystal Syntax?
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
o9z1
Newbie
Newbie


Joined: 01 Feb 2008
Online Status: Offline
Posts: 12
Quote o9z1 Replybullet Posted: 07 Feb 2008 at 1:08pm
Yes, I am using crystal syntax
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 07 Feb 2008 at 2:18pm
Do you have an apostrophe as the first character on that line, or is that a typo in your message? There should not be an apostrophe on the line anywhere.

Global Numbervar ContractTotal;
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
o9z1
Newbie
Newbie


Joined: 01 Feb 2008
Online Status: Offline
Posts: 12
Quote o9z1 Replybullet Posted: 08 Feb 2008 at 5:57am
that was just how the error shows:

'error'
IP IP Logged
o9z1
Newbie
Newbie


Joined: 01 Feb 2008
Online Status: Offline
Posts: 12
Quote o9z1 Replybullet Posted: 08 Feb 2008 at 6:16am
Is there another alternative to doing this with just an immediate if or CASE statement?
IP IP Logged
BrianBischof
Admin Group
Admin Group
Avatar

Joined: 09 Nov 2006
Online Status: Offline
Posts: 2458
Quote BrianBischof Replybullet Posted: 08 Feb 2008 at 8:59am
To be honest, I don't know what is going on with your reports. I looked at both posts you have on the forum and the code I give you works fine on my computer. Yet you get strange errors when you run it. There is something odd happening, but I have no idea where the problem is.

Anyway, another way to do you SELECT statement is to create a formula with only the IIF() statement.

IIF({KILSHEET.Contract}=1, (TOT_CARC_WEIGHT / 100) * 1.75, 0);
And add that to your report in the Details section. Then right-click on it and select Insert > Summary. That will give you a total at the end of the report.
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 08 Feb 2008 at 10:06am
I just noticed something here, re-reading the thread.  Where exactly are you putting this formula?  You should just be creating a new formula field, and adding the field to the details section.  It sound instead like you are trying to assign and manipulate variables inside a running total condition.  Which could throw errors.


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.016 seconds.