formula limitation
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Tips and Tricks
Forum Discription: Have you learned some great tricks to share with the group? Post them here!
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=20221
Printed Date: 03 May 2024 at 6:11am
Topic: formula limitation
Posted By: Alif
Subject: formula limitation
Date Posted: 14 Nov 2013 at 2:58pm
hi i have an issue in cryatal report, i want to calculate the difference of date from application_id that has increasing status.
The database uses runcontrol id to retrive the data from the database. User datermine the runcontrol id and specify the date range.(i.e runcontrol_id = 1, date_from = 1-1-2012, date_to = 31-5-2012).
applid status Datetimestamp date to date from
ZA120017748 1 2012-01-11-11.32.22.000000 1/1/2012 5/31/2012
ZA120017748 2 2012-01-11-11.39.47.000000 1/1/2012 5/31/2012
ZA120017748 2 2012-01-15-16.14.18.000000 1/1/2012 5/31/2012
ZA120017748 3 2012-03-01-15.27.18.000000 1/1/2012 5/31/2012
ZA120017748 5 2012-03-12-11.16.30.000000 1/1/2012 5/31/2012
ZA120017748 7 2012-04-15-13.34.04.000000 1/1/2012 5/31/2012
ZA120017749 1 2012-02-11-11.43.10.000000 1/1/2012 5/31/2012
ZA120017749 2 2012-02-11-11.47.45.000000 1/1/2012 5/31/2012
ZA120017749 2 2012-02-15-16.13.28.000000 1/1/2012 5/31/2012
ZA120017749 2 2012-03-02-10.52.47.000000 1/1/2012 5/31/2012
ZA120017749 3 2012-06-13-16.39.22.000000 1/1/2012 5/31/2012
ZA120017749 5 2012-06-14-13.29.33.000000 1/1/2012 5/31/2012
ZA120017749 7 2012-06-19-11.15.58.000000 1/1/2012 5/31/2012
ZA120017750 1 2012-03-11-11.43.10.000000 1/1/2012 5/31/2012
ZA120017750 2 2012-03-11-11.47.45.000000 1/1/2012 5/31/2012
ZA120017750 2 2012-03-15-16.13.28.000000 1/1/2012 5/31/2012
ZA120017750 2 2012-03-02-10.52.47.000000 1/1/2012 5/31/2012
ZA120017750 3 2012-06-13-16.39.22.000000 1/1/2012 5/31/2012
ZA120017750 5 2012-06-14-13.29.33.000000 1/1/2012 5/31/2012
ZA120017750 7 2012-06-19-11.15.58.000000 1/1/2012 5/31/2012
STEP PROCESS CONDITION(DAYS) JAN FEB MAR APR MAY
1 ADMINISTRATOR MUST BE < 7
2 WAITING MUST BE < 10
3 VERIFYING MUST BE <7
4 APPROVING MUST BE < 10
I can only calculate the days difference for one month one (i.e january), once it started with february and the rest, the data is not correct anymore.
My issue with crystal report is that each row of the table is used to pass through each formula, after that the next line is used to pass through the same formula again. This is different from other language that use the code to check the whole data.
-------------
alif
|
Replies:
Posted By: lockwelle
Date Posted: 27 Nov 2013 at 5:01am
really? All of the reporting systems that I have used do 1 row at a time...but regardless of that, what are you using to calculate the date difference? Is a comparison to the prior records value?
To compare to the prior record use the PREVIOUS()function. The major caveat is that it won't respect group boundaries, so you might want to use a global or shared variable to hold the 'prior' value and reset it in the group header so that there is no error.
DateDiff(days, oneDate, otherDate) should work just fine.
I don't know how you are filling the different columns, that might make for a more complex formula or set of formulas so that everything aligns. You might be able to use a crosstab style report, but maybe not...I don't use them too often so they are not my forte...they don't seem as flexible as I would like...maybe it's just because i haven't used them to much.
of course my personal favorite is to do as much as possible in a stored procedure, which is way more flexible and easier than trying to do same thing all in CR...and I know that not everyone is allowed to write stored procs so this may not be an option for you.
HTH
|
Posted By: Alif
Date Posted: 12 Dec 2013 at 8:16pm
thanks for the reply,
yes that is true crystal used 1 row to pass through all the formula. i used DateDiff(days, oneDate, otherDate) to count the difference. i can't use group coz i'm given a report design as below.
STEP PROCESS CONDITION(DAYS) JAN FEB MAR APR MAY
1 ADMINISTRATOR MUST BE < 7 12 15 19 8 5
2 WAITING MUST BE < 10 12 15 19 8 5
3 VERIFYING MUST BE < 7 12 15 19 8 5
4 APPROVING MUST BE < 10 12 14 18 8 5
The report is put in the page header, if there is 12 application created in january all the way until approving should be 12. if 1 application takes more than 7 days to verify (i.e month february) the approving will be 14. Using group doesn't help as it crates group header. Tried PREVIOUS()function and global and shared variables also doesn't help due to the 1 row to pass all the formula. Anyway can you please elaborate about the stored procedure?
-------------
alif
|
Posted By: lockwelle
Date Posted: 13 Dec 2013 at 4:53am
a stored procedure is basically a batch file of SQL commands that run on the database. The result of the stored procedure, usually a final SELECT statment becomes the 'data' of the report...
So if you create the table that you want to display in the stored procedure, there is basically nothing left for the report to do, except, actually display the data.
There is a lot that you can do in a stored procedure, but this is really not the place to go into it. Just as a idea, you can create temporary tables that allow you to manipulate the data in a way that CR just can't...hence the flexibility of the stored procedure.
I am sure that there are 100s of sites to assist in stored procedures (and hopefully some co-workers as well).
|
Posted By: hello
Date Posted: 14 Feb 2014 at 10:37am
If you have more than one formula field in any one section, maybe your formulas are getting processed in the wrong order.
If I have a lot of formula fields in the detail section, I usually place
WhileReadingRecords
at the beginning of the first formula, then place
EvaluateAfter <previous formula field name>
on each subsequent formulas.
|
|