Crystal Report query / formula (merged) [message #232664] |
Sun, 22 April 2007 21:30 |
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 |
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 #232730 is a reply to message #232696] |
Mon, 23 April 2007 02:23 |
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 |
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 |
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 |
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 |
|
Littlefoot
Messages: 21823 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).
|
|
|