Print Page | Close Window

Supress certain data

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8936
Printed Date: 03 May 2024 at 4:28pm


Topic: Supress certain data
Posted By: Angeldust
Subject: Supress certain data
Date Posted: 25 Jan 2010 at 4:19am

I'm trying to suppress certain details if the data in the fields is the same, but I am having problems with the formulas.

Below is the details in the report I'm busy with.

 
INVONO INVODATE DOCNO ORDERNO INWARD  TRV  AMOUNT REF_INVONO
1610035 2009/02/04 301005 1387823 2009/01/30 PETERSEN 1,235.09 
1610020 2009/02/04 301005 1387823 2009/01/30 PETERSEN 0.00 
1593660 2008/12/22 301005 1387825 2009/02/06 PETERSEN 0.00 
1613008 2009/02/10 301005 1387825 2009/02/06 PETERSEN 1,235.09 
8063460 2009/02/10 301005 1387825 2009/02/06 PETERSEN 1,235.09 1613008
1593787 2008/12/22 301005 1388045 2009/02/06 MKHWANAZI 0.00 
1593788 2008/12/22 301005 1388045 2009/01/30 MKHWANAZI 0.00 
1593784 2008/12/22 301005 1388045 2009/01/16 MKHWANAZI 0.00 
1593786 2008/12/22 301005 1388045 2009/01/23 MKHWANAZI 0.00 
 

If the Ref Invoice number equals the Invo no, both lines need to be suppressed.
If all data, except invoice number, invoice date and amount matches, then suppress.

 
What is going to be the best way to get the information I need?
Thanks


Replies:
Posted By: kevlray
Date Posted: 25 Jan 2010 at 8:58am
You need to write a formula in the section expert to suppress.  Hopefully you will not need totals on this, that doubles the effort.

Posted By: Angeldust
Date Posted: 25 Jan 2010 at 11:04pm
Thanks, but what formula would I use?  I just can't seem to get one that actually works :)

Posted By: kevlray
Date Posted: 26 Jan 2010 at 11:24am

If you are comparing two rows, then the Next and Previous functions are the way to go.  At least in my experience you will need to check for first and and last records, otherwise the Next and Previous will not return a value to check.

Posted By: Angeldust
Date Posted: 27 Jan 2010 at 1:36am
This is the formula I used, {TAS_INVH.REF_INVONO} = previous({TAS_INVL.INVONO}), but when I try and save the formula it tells me that a string is needed for this section of the formula: previous({TAS_INVL.INVONO}).

Posted By: davejwhite
Date Posted: 27 Jan 2010 at 3:35am
SOunds like a Data Type mismatch - your REF_INVONO field is a String field, whereas your INVONO field is numeric.
 
Try
{TAS_INVH.REF_INVONO} = ToText(previous({TAS_INVL.INVONO}),0,"")
 
The To Text function converts the number to a string.
The second argument (0) tells it to use zero decimal places.
The third argument ("" - empty string) tells it not to sue a thousand separator.

Posted By: Angeldust
Date Posted: 27 Jan 2010 at 3:58am
that makes sense :) thanks.
I tried it, but the problem is that the Invono isn't always right before the ref invoice nbr.  Sometimes it could be quite a few lines above.  I tried without using the previous, but didn't work either.
 

Posted By: davejwhite
Date Posted: 27 Jan 2010 at 4:30am
Oh, I see.

Sounds like a job for an Array. I haven't got time to work this out (sorry), but here's what I'd look at:
1. Create a formula and declare a String Array
2. Store the value of the InvoNo field in the array, using ReDim Preserve so that it doesn't overwrite what it has already stored
3. Loop through the Array with each Ref_InvoNo to see if it is in the Array. This becomes the condition with which you Suppress.
 
Problem will be in going back to the line containing the original Invoice No to suppress that too.
 
Hmmm .... working ....


Print Page | Close Window