Login / Register  search  syndication  about

          George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

Using Crystal Reports "Next" and "Previous" Functions

Today  I am going to write about  the "Next" and  "Previous" Functions available with Crystal Reports.  As their names describe the intent of these functions is to provide you a value of your selected field for either  the Next or the Previous record as shown in your report.

For example if I wanted to determine the number of days it has been since a History record has been written against an Account in SalesLogix,  I could use these two functions along with the DateDiff function  to organize our data.  Let me give you an idea of how!

First, create a SalesLogix report with the History table.  Make sure to create two groups, group 1 is for the Account Name - sorted ascending, group 2 is by History Complete Date - sorted ascending by second.  The group level fields are used in the report.  Use your Section Expert to have group1 overlay group2.  Set the fields in the two groups so they would line up into columns if they were next to each other.  Also,  I  suggest suppressing all group footers and the detail section in your report.

Now create a formula field called "Next" and add the following formula:

IF {HISTORY.ACCOUNTNAME} = Next ({HISTORY.ACCOUNTNAME}) then CSTR(Next ({HISTORY.COMPLETEDDATE})) else ""

This formula states if the current records Account Name does not equal the next records Account name then do not show the next records Complete date.   If you do not include the account comparison in the formula then "Next" will display a value for the next record even if it is not from the same account. 

Add this field anywhere in History Group 2.  If you put this field by the complete date you will see that the field displays the date of the next records complete date.  The last record under the group for the Account will be blank.  What I want is this last History record that was added for this Account and none of the others so I will use this blank field to my advantage.  My group 2 suppression formula looks something like this.

{@Next}<>""

This should bring you down to just one record per account.  Since none of the data showing has a "Next" value so remove that field from the report.

Let's go ahead and create another field called "Previous" using the following formula:

IF {HISTORY.ACCOUNTNAME} = Previous ({HISTORY.ACCOUNTNAME}) then CSTR(CDATE(Previous ({HISTORY.COMPLETEDDATE}))) else ""

This field is very similar to the "Next".  Add this field to the report and you will see that you now have the date of the previous History record.  If the value is blank then there is not a previous record in the system

So you now know the Complete date of  the last History record along with the Complete Date of  record prior to the last History record, but how long has it been since the last History record?  Create one final formula field called "Days Since" using this formula:

DateDiff ("d",{HISTORY.COMPLETEDDATE},CurrentDate)

Add this to your group 2 and you will have all the information you need to see what Accounts are in need of some attention.

 

Thanks

Geo

 

 

 

What's This?
  
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.


Related Content
   Determining User Rights in the Infor CRM (Formerly Saleslogix) LAN Client
I recently had a client ask how to get a query to tell which users have rights to add, edit, and delete v
Posted on Apr 03, 2015 by Kris Halsrud to Kris Halsrud's Blog
 
   How do I wrap text in Textboxes in Infor CRM (formerly Saleslogix)?
Recently an Infor CRM administrator contacted me in regards to many of their textboxes for Notes in the I
Posted on Apr 01, 2015 by Dale Richter to Infor CRM Questions & Answers
 
   InforCRM (formerly Saleslogix) Mobile - Clearing Out Earlier Customizations
If you started with an earlier version of SalesLogix Mobile, let's say version 7.5.4, and customized
Posted on Mar 30, 2015 by Mark Duthoy to SalesLogix Support
 
   How do you set the Base Directory when Deploying your Web Client on Infor CRM 8.1?
Question:How do I make sure my Base Directory is set correctly on Infor CRM ? Recently I had a custo
Posted on Mar 06, 2015 by Dale Richter to Infor CRM Questions & Answers
 
   How do I fix a corrupt Picklist in Infor CRM version 8.1?
I had a user ask how to fix a corrupt picklist. The picklist did not show any values when trying to e
Posted on Feb 06, 2015 by Dale Richter to Infor CRM Questions & Answers
 
Comments

 

Twitter Trackbacks for Using Crystal Reports "Next" and "Previous" Functions - George Jensen's Blog [customerfx.com] on Topsy.com said:

Pingback from  Twitter Trackbacks for                 Using Crystal Reports &quot;Next&quot; and &quot;Previous&quot; Functions - George Jensen's Blog         [customerfx.com]        on Topsy.com

January 22, 2010 4:54 PM
 

Chaitra said:

Hi,

I am working on crystal report conversion to SSRS.I wanted to know how to convert this NEXT formula from crystal report to SSRS

April 14, 2010 2:10 AM
 

Sojan said:

How to get the previous and next of a calculated value

December 9, 2010 7:21 AM
 

Sojan said:

I want to get the pervious of a summary value (which is created from a TransValue field in stored procedure)

December 9, 2010 7:23 AM
 

eddie villano said:

hi guys, i just want to share this one.

If you are looking for better reporting software, please take a look at Windward Reports. With Windward you design reports in Microsoft Word, Excel, or PowerPoint so report design is a lot faster and easier - and non-programmers can design reports.

reporting software

www.windwardreports.com

April 21, 2011 11:16 PM
 

Kunal said:

Hi is there ssrs equivalent to next() method of crystal report please reply

July 21, 2011 8:06 AM
 

Dee said:

Can this work at a Group level,

i.e to get a total of the Previous Group?

May 11, 2012 3:23 AM
 

pookeye said:

I would also like to know if this works on a group level,  I tried doing next(GroupName)   it doesnt work...    i was reading up online and it mentioned something about changing the next function to include groups, but i was unable to find out how to go about doing this.

September 4, 2013 3:43 PM
 

Rob said:

I have been beating my head for a week trying to solve a problem my regional managers approached me with and these simple expressions solved it for me. THANK YOU!!

June 11, 2014 10:31 AM
 

r said:

r

February 5, 2015 1:56 AM
 

r said:

f

February 5, 2015 1:56 AM

Leave a Comment

(required)  
(optional)
(required)  
Add
All contents Copyright © 2015 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register