Active TopicsMemberlistSearchHelpRegisterLogin

Tips and Tricks
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Tips and Tricks
Message Icon Topic: formula limitation Post Reply Post New Topic
Author Message
Alif
Newbie
Newbie
Avatar

Joined: 13 Nov 2013
Location: Brunei Darussalam
Online Status: Offline
Posts: 3
Quote Alif Replybullet Topic: formula limitation
    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
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
IP IP Logged
Alif
Newbie
Newbie
Avatar

Joined: 13 Nov 2013
Location: Brunei Darussalam
Online Status: Offline
Posts: 3
Quote Alif Replybullet 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
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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).

IP IP Logged
hello
Groupie
Groupie
Avatar

Joined: 05 Feb 2014
Online Status: Offline
Posts: 85
Quote hello Replybullet 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.

Edited by hello - 14 Feb 2014 at 10:39am
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.