Home » Developer & Programmer » Reports & Discoverer » Crystal Report query / formula (merged)
Crystal Report query / formula (merged) [message #232664] Sun, 22 April 2007 21:30 Go to next message
kdastageerbasha
Messages: 6
Registered: April 2007
Location: Singapore
Junior Member
I have created a crystal report using formula.
But its taking too much time to generate that report and also no values in the report.So i want to find out the formula is correct or not.so how can i check the formula part.
please any suggestion.

by
DAS
Crystal formula [message #232672 is a reply to message #232664] Sun, 22 April 2007 22:48 Go to previous messageGo to next message
kdastageerbasha
Messages: 6
Registered: April 2007
Location: Singapore
Junior Member
Hi All,

How can check the following formula which i used in crystal reports in PL/SQL.Because i want to test this is correct or not.

crystal formula:

IF {A_SECS.ISSUE_DATE} <= {?As At Date} THEN
(
IssuedVolume := {A_SECS.ISSUED_VOLUME};
)
ELSE
IssuedVolume := 0;


In PL/SQL ??

How??

A_SECS is view here

Please help..
Re: Crystal formula [message #232696 is a reply to message #232672] Mon, 23 April 2007 01:03 Go to previous messageGo to next message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, do not open a new topic for the same problem - continue discussion in original one.

Does Crystal Report really allow you to use such a syntax? I mean,

IF table__name.column_name <= some_value THEN ...

Wouldn't you rather have to select this column value into a variable and then use it in the IF-THEN-ELSE? Such as
DECLARE
  l_issue_date a_secs.issue_date%type;
  l_issued_volume NUMBER;
BEGIN
  SELECT a.issue_date
    INTO l_issue_date
    FROM a_secs a
    WHERE some_condition_here;  -- in order to get one and only one value

  IF l_issue_date <= sysdate    -- sysdate is used just as an example; I don't know what "?As At Date" is
  THEN 
     l_issued_volume := l_issue_date;
  ELSE
     l_issued_volume := 0;
  END IF;
END;
Re: Crystal formula [message #232730 is a reply to message #232696] Mon, 23 April 2007 02:23 Go to previous messageGo to next message
kdastageerbasha
Messages: 6
Registered: April 2007
Location: Singapore
Junior Member
Thanks Little,

I am very sorry to put into two separate queries.I need another suggestion,

My Crystal Report taking too much time to generate a report.How to make it fast pls explain via scan method or some other in detail

by
DAS
Re: Crystal formula [message #232775 is a reply to message #232730] Mon, 23 April 2007 04:28 Go to previous messageGo to next message
skooman
Messages: 913
Registered: March 2005
Location: Netherlands
Senior Member
I know nothing about Crystal Reports, but based on the fact that it it a reporting tool, I would guess it uses some sort of SQL, not PL/SQL (also because Crystal Reports works on various databases if I'm not mistaken, so it wouldn't use a proprietary part like PL/SQL).

If so, track/trace the report to find out where the time is spent (database or reporting side). If it's the database, please post the SQL statement (including tracefile), we might be able to help you. If it's the reporting side, I would suggest to find a Crystal Reports forum or something...
Re: crystal conditional logic [message #232896 is a reply to message #232775] Mon, 23 April 2007 20:56 Go to previous messageGo to next message
kdastageerbasha
Messages: 6
Registered: April 2007
Location: Singapore
Junior Member
Dear All,

I have certain condition in my crystal report formula
like =,<,<=,>= and etc.

So how can i rearrange my condition depends the conditional operator priority in order to make it fast the calculation.

Example
IF {A_SECS.SEC_TYPE} = "REPO MRF" AND
{A_REPOS.SEC_REF} = {A_TRANSACTIONS.SEC_LENDING_UNDERLYING_SEC_REF} AND //This is beyond essential to get the underlying nominal!
{A_REPOS.MATURITY_DATE} >= {?As At Date} AND
{A_TRANSACTIONS.PAYMENT_DATE} <= {?As At Date} AND
{A_TRANSACTIONS.BUSINESS_TRANSACTION_CODE} = "Open Reverse" //AND
//{A_TRANSACTIONS.ELEMENTARY_TRANSACTION_CODE} = "RalocResOut" AND
// {A_SECSSMALL.MATURITY_DATE} > {?As At Date} AND
//{A_TRANSACTIONS.TRANSACTION_CANCELLATION_FLAG} = "Active"
THEN
RIRepoMRF := RIRepoMRF + {A_TRANSACTIONS.NOMINAL}/1000000;

by
das
Re: crystal conditional logic [message #233130 is a reply to message #232896] Tue, 24 April 2007 19:40 Go to previous messageGo to next message
kdastageerbasha
Messages: 6
Registered: April 2007
Location: Singapore
Junior Member
Dear All,

Sorry i am posting so many queries becoz i am working in crystal report platform,

IF (UPPERCASE({A_TRANSACTIONS.ELEMENTARY_TRANSACTION_CODE}) = "BUY" AND
(UPPERCASE({A_TRANSACTIONS.TRANSACTION_ORIGIN}) = "E-APPS" OR
UPPERCASE({A_TRANS_INSTRUCTIONS.INSTRUCTION_COMMENT}) = "AUCTION"))
OR (UPPERCASE({A_TRANSACTIONS.ELEMENTARY_TRANSACTION_CODE}) = "OPENBALANCE" AND
UPPERCASE({A_TRANSACTIONS.BUSINESS_TRANSACTION_CODE}) = "OPENBALANCE")

if i use like this in crystal reports its taking too much time so i want to minimize the execution speed please anybody can rearrange this query to make it fast

by
DAS
Re: crystal conditional logic [message #233148 is a reply to message #233130] Wed, 25 April 2007 00:37 Go to previous message
Littlefoot
Messages: 21813
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Are you sure that it is IF-THEN-ELSE construct that produces a bottleneck in this report?

I know nothing about Crystal Reports, but, generally speaking and referring a simple case such as
IF a = 'ABC' OR b = 'EFG'
THEN ...
I don't see a way how to make it run faster. If there were functions involved in your IF-THEN-ELSE, you might consider breaking it into nested IFs, so that you wouldn't have to wait for all of them to become TRUE in order to continue computing. It would look like this:
IF function_1 (some_parameter) = 'ABC' THEN
   IF function_2 (another_parameter) = 'EFG' THEN
      IF function_e (third_parameter) = 'HIJ' THEN
         var_x := 0;
      END IF;
   END IF;
END IF;
So, if function_1 is different from 'ABC', all other IFs wouldn't be executed at all and it *might* be faster, I guess.

But, seeing your IF, using the UPPERCASE function shouldn't make a noticeable difference.

Once again: I'm not used to use Crystal Reports and I have no idea whether using a table directly in IF is OK or should it be used differently (as I've said in my previous post).
Previous Topic: input parameter - other option for flashlight search
Next Topic: Freight Charges not appearing in Order / Invoice Detail Report
Goto Forum:
  


Current Time: Thu Jul 04 11:41:04 CDT 2024