Print Page | Close Window

Syntax for Case Statement

Printed From: Crystal Reports Book
Category: Crystal Reports for Visual Studio 2005 and Newer
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=2260
Printed Date: 03 May 2024 at 3:49am


Topic: Syntax for Case Statement
Posted By: o9z1
Subject: Syntax for Case Statement
Date 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)


Replies:
Posted By: BrianBischof
Date 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 http://www.amazon.com/exec/obidos/ASIN/0974953601/bischofsystem-20 - 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>

Posted By: o9z1
Date 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

Posted By: BrianBischof
Date 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>

Posted By: o9z1
Date Posted: 07 Feb 2008 at 1:08pm
Yes, I am using crystal syntax

Posted By: BrianBischof
Date 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>

Posted By: o9z1
Date Posted: 08 Feb 2008 at 5:57am
that was just how the error shows:

'error'

Posted By: o9z1
Date Posted: 08 Feb 2008 at 6:16am
Is there another alternative to doing this with just an immediate if or CASE statement?

Posted By: BrianBischof
Date 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>

Posted By: Lugh
Date 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.


Print Page | Close Window