Print Page | Close Window

Previous count formual

Printed From: Crystal Reports Book
Category: General Information
Forum Name: Announcements
Forum Discription: Please check this section for the latest announcements from Crystal Reports Forum
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19086
Printed Date: 28 Mar 2024 at 5:08am


Topic: Previous count formual
Posted By: kevinp70
Subject: Previous count formual
Date Posted: 12 Feb 2013 at 2:50am
I'm trying to come up with a formula that will look to see if there was a previous record number assigned and count what the previous number was.

Sooo.....

if {u_case.cs_charter} = "3"
but
{u_case.cs_prev_charter} in ["1", "2", "7"]
then use the previous
{u_case.cs_date_filed} in {?@subsdate} to {?@subedate}

I suck at this but trying. Thanks



Replies:
Posted By: hilfy
Date Posted: 14 Feb 2013 at 11:47am
How is your data grouped and sorted?  Is this something that needs to be done when selecting data in the query or is it for a formula that will be displayed on the report?
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: kevinp70
Date Posted: 11 Apr 2013 at 4:42am
So this code takes into account where if cases having been previously a chapter "13" are also incorporated into the equation/report; however, I'd like to also include where if cases having previously been a chapter "7" are also incorporated as well, but I don't know how to add the chapter "7" to the code (see below). Seems easy enough but for what ever reason I'm struggling with this seemingly simple fix. I really appreciate any assistance that can be offered -- thank you.

If IsNull({u_case.cs_prev_chapter}) Then
{u_case.cs_type} = "bk" and {u_case.cs_date_filed} >= {?startdate} and {u_case.cs_date_filed} <= {?enddate}
Else
{u_case.cs_type} = "bk" and {u_case.cs_date_filed} >= {?startdate} and {u_case.cs_date_filed} <= {?enddate} and {u_case.cs_prev_chapter} = "13"


Posted By: hilfy
Date Posted: 11 Apr 2013 at 4:57am
Try this:
 
If IsNull({u_case.cs_prev_chapter}) Then
{u_case.cs_type} = "bk" and {u_case.cs_date_filed} >= {?startdate} and {u_case.cs_date_filed} <= {?enddate}
Else
{u_case.cs_type} = "bk" and {u_case.cs_date_filed} >= {?startdate} and {u_case.cs_date_filed} <= {?enddate} and {u_case.cs_prev_chapter} in ["7","13"]
 
However, if you're using this in the Select Expert, please be aware that the use of the "If...Then...Else" construct means that Crystal will query for all of the data and do the filtering in memory instead of pushing the filter down to the database and having the database filter the data prior to returning it to Crystal.  If you're data doesn't have many rows, this is not a big deal.  However, if you're working with a fairly large data source, this can cause a report to be really slow.  A better way to handle this in the Select Expert is to change the syntax to something like this:
 
((IsNull({u_case.cs_prev_chapter}) and
{u_case.cs_type} = "bk" and {u_case.cs_date_filed} >= {?startdate} and {u_case.cs_date_filed} <= {?enddate} )
OR
({u_case.cs_type} = "bk" and {u_case.cs_date_filed} >= {?startdate} and {u_case.cs_date_filed} <= {?enddate} and {u_case.cs_prev_chapter} in ["7", "13"]))
 
Note where I put the parentheses (bold, red)  they MUST be included in order for this to work correctly!
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: kevinp70
Date Posted: 11 Apr 2013 at 5:54am
No go... So what you sent me displays basically the same values in my report as I get if I were to use:
{u_case.cs_type} = "bk" and {u_case.cs_date_filed} >= {?startdate} and {u_case.cs_date_filed} <= {?enddate}

So imagine my report has 3 columns. Column 1 is the "7" column, column 2 is "13" column, however, if column 3 (previous chpt column) lists a "13" then column 1 should subtract from that row and add it to column 2 row value and if column 3 (previous chpt column) lists a "7" then column 2 should subtract from that row and add it to column 1 row value. thanks !!



Print Page | Close Window