Technical Questions: Query in a formula

Query in a formula

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=6491
Printed Date: 02 May 2024 at 1:31pm

Topic: Query in a formula

Posted By: Kausthub R
Subject: Query in a formula
Date Posted: 27 May 2009 at 10:33pm

I'm using CR XI

I need to write a query inside a formula field which looks like this,

If(Condition1)

then Query1

Else Query2

Is this possible?

If not what is the alternative?

I have tried a lot but no success.

Please help me out!!!

Thanks in advance.

Replies:

Posted By: rahulwalawalkar
Date Posted: 28 May 2009 at 1:36am

not sure what you are trying to do,you can try using SQL Expression field and see it works,not sure of the syntax though .

you can also try creating stored procedure which accepts parameter from the report and depending on the parameter value excute the query.

Cheers

Rahul

Posted By: Kausthub R
Date Posted: 28 May 2009 at 3:42am

The expression what i need is like this:

if {table.field1}="Value1"

then

(select "table"."field2" from "table" where "table"."field3"="Value3"

and "table"."field1"="Value1")

else

(select "table"."field2" from "table" where "table"."field3"="Value3"

and "table"."field1"="Value2")

Value's can be hard coded.

Is this possible using a SQL expression field?

Posted By: rahulwalawalkar
Date Posted: 28 May 2009 at 3:48am

not sure never tried it in SQL Expression,but if you use a word of caution before the report executes it will compile the query to it may take a while before the report fetches the data.

What I would suggest is to create a stored procedure and pass parameter from the report and depending on that parameter value execute the select statement in your procedure.

Cheers

Rahul

Posted By: lockwelle
Date Posted: 28 May 2009 at 6:43am

I doubt that it will work. Due to the way that Crystal works, it will have already have read the data, and will not read again.

As suggested before, create a stored proc and in it, you can create the field and populate it with the data that is desired. A view would work as well.

Last suggestion, and for something like this, I don't like, but you could create a subreport to get the value that you need....Crystal will do a data read again for a subreport. Reason I don't like it, it will incur lots of hits to the DB for something that could have been gotten in another manner much more effectively (via view or stored proc).

Posted By: Kausthub R
Date Posted: 02 Jun 2009 at 9:15pm

Stored procedure must work fine,

Thanks Rahulwalawalkar & Lockwelle for your fast replies.