Print Page | Close Window

Select statement in Formula editor

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=6565
Printed Date: 18 May 2024 at 2:22pm


Topic: Select statement in Formula editor
Posted By: irfan
Subject: Select statement in Formula editor
Date Posted: 03 Jun 2009 at 3:19pm

Hi,

  I am trying to use a select statement in a formula editor to return value when a  column of a row returned by the main report query is empty.

How can this be done, please give an example using syntax.
 
Thanks


Replies:
Posted By: DBlank
Date Posted: 03 Jun 2009 at 3:46pm
if isnull({table.field}) then X else y

Posted By: irfan
Date Posted: 04 Jun 2009 at 7:52am
Thanks but how do I use a select statement in place of X or Y. Please let me know

Posted By: DBlank
Date Posted: 04 Jun 2009 at 8:15am
I am not sure I am understanding your request exactly. If you are hoping to actually alter the data at the time of pulling it into the report using crystal select expert, that won't work. You would have to do that in some form (e.g. a view or stored procedure in SQL) before you get the data to crystal.
However, I think what you re trying to do you would just as easily get the results by a regular formula in crystal (not in the select expert) then you use the formula field instead of your original field to do grouping or counts or whatever.
I can give you examples of different fomula but I don't know exactly what you want to accomplish here so I am just guessing. Just rememebr that all of your results (the x oy y) have to be the same data types (both text, both date, both numeric, etc.).
Examples:
If isnull({table.field}) then "Hello, I am a null" else "Goodbye, I am not Null"
 
If isnull({table.text_fieldA}) then "Hello, I am a null field" else {table.text_field}
 
If isnull({table.text_fieldA}) then {table.Text_fieldB} else {table.text_fieldA}
 
Are these the kind of examples you wanted?

Posted By: irfan
Date Posted: 04 Jun 2009 at 8:19am

Thanks again, I am trying to look at the data in the report. If I find a null in a column then  I have to use another table not defined in the report to look into and then get the value from there, Is this possible ?

Posted By: DBlank
Date Posted: 04 Jun 2009 at 8:41am
Nope. You would have to bring that table into the report (using the appropriate joins). If you do that it is pretty straight forward
as something like:
If isnull({tableA.text_field}) then {tableB.Text_field} else {tableA.text_field}
 
Or create a view or stored procedure that does all of this and then use that in the report instead of the original table(s).


Print Page | Close Window