Solved

Suppress if duplicated (field) leaving blank lines

Posted on 2004-09-20
8
2,421 Views
Last Modified: 2008-01-09
CR 9 - I have a field in a detail section of a sub report, with the "Can Grow" option selected on the format sub report options.  I have a field in the detail section of the sub report with "Suppress if Duplicated" turned on.  The suppression works (you dont see the data), but it is adding one line for each field value it suppresses.  I need the can grow option, since I don't know how many records will be retrieved, but I don't want a whole bunch of blank lines if the data retrieves a whole bunch of duplicates.

Any ideas.

Thanks
0
Comment
Question by:smeyer4314
8 Comments
 
LVL 42

Accepted Solution

by:
frodoman earned 63 total points
ID: 12106205
If you can arrange it so that the entire detail section is suppressed then you can use Report -> Section Expert -> Details -> Suppress Blank Section.  This will not allocate the space if the entire section is blank / suppressed.

frodoman
0
 

Author Comment

by:smeyer4314
ID: 12106370
That won't work because the section will never be blank.  In this case I have multiple samples and each sample is assigned to one or more product lines (A, B, C....)

The simplest case is as follows:

Record 1  -> A
Record 2 -> A
Record 3 -> A
Record 4 -> A

I want just one line that says
          A
not one line with A and three blank lines (suppress if duplicated)
          A
           blank
           blank
           blank

The next level of complexity is that the records can have more then one assignment ie
Rec 1 - > A
Rec 1 -> B
Rec 1 -> C
Rec 2 -> A
Rec 3 -> A
Rec 3 -> D

Should come out as
          A
          B
          C
          D
Not
           A
           B
           C
            blank
            Blank
             D

Don't worry about being able to know which record generated which A,B,C,D
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12106402
Why won't a section be blank?  In your last example:

Not
           A
           B
           C
            blank
            Blank
             D

If you suppress blank section then you would get:

A
B
C
D

I'm assuming that each row in your example is a single detail section, right?  Also I'm talking about doing this in the subreport itself, not in the main report.

frodoman
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 42

Expert Comment

by:frodoman
ID: 12106429
Continuing...

This is the only way that you're going to eliminate the empty space.  Suppress simply hides the text but the space for the field is still maintained and suppressing the blank section is the only way to get rid of it.  If you can't arrange the report so the section will be completely blank, then there is no way to do what you want.

The other option of course is to pull your data so that the duplicate records never make it to Crystal.  This would likely involve using a stored procedure instead of hitting the tables directly and of course this isn't an option in many situations.

frodoman
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12106511
Another way which is a little more difficult to deal with is to use separate detail sections for each line.

Make the fields height = 0
Make the section height = 0
Set all fields and sections CAN GROW

mlmcc
0
 
LVL 42

Expert Comment

by:frodoman
ID: 12106551
Good thought - in CR9 you can't actually set it to zero but you can set it to a value so small that it's virtually zero (I believe 0.04 inches is the smallest CR9 will allow).  

frodoman
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 12108558
I generally just use the mouse to make the fields/sections as small as possible.. I don't think I have ever tried setting the height small through the property window.

mlmcc
0
 
LVL 5

Assisted Solution

by:ajitanand
ajitanand earned 62 total points
ID: 12120010
Hello,

You are correct. Even if you suppress the Sub Report's section that holds the Details Fields, for hiding duplicates, it will take the Empty Space in the Main Report to the extent, that is equal to the height of the SubReport that you have inserted in the Main Report in design Time.

This holds true in case your sub report is linked to the main report based on a key field as:

MasterReport - Page Header
--------------------------
Master Report - Details Header
----> Master Report - Detail Section - Record 1 - Key Field Value = 1
--------->Sub Report - Record 1.1 - Key Field Value = 1
--------->Sub Report - Record 1.2 - Key Field Value = 1
----> Master Report - Detail Section - Record 2
--------->Sub Report - Record 2.1 - Key Field Value = 2
--------->Sub Report - Record 2.2 - Key Field Value = 2
----> Master Report - Detail Section - Record 3
--------->Sub Report - Record 3.1 - Key Field Value = 3
--------->Sub Report - Record 3.2 - Key Field Value = 3
--------->Sub Report - Record 3.3 - Key Field Value = 3
--------------------------
MasterReport - Page Footer


If the above is right, then you need have 2 options, one easy other lengthy:

Option 1. The easy way, still leaves some (very) minimum empty space, but still can cause irregular spaces between sections in main report if you have some mainReport fields that have SubReport Fields linked in a manner, such that all SubReport rows for this row for mainReport have duplicate rows, and all need to be suppressed:

Step 1. Set the Sub Report to Suppress Duplicate Fields
Step 2. Set the Sub Report to Suppress Blank Sections (which contains the duplicate fields)
Step 3. Through Main Report design mode, drag through Mouse to set the Sub Report place-holder's size to its minimum possible height.


Step 2. The lengthy way requires you to have another field in the outer table for the MainReport, that can help you predict the no. of visible rows in the subreport for each MainReport Detail row. Then you can set a condition to suppress the entire subreport for that main report row if the subreport has all dupliactes, and hence need to be suppressed.


One final note on your comment - "Don't worry about being able to know which record generated which A,B,C,D"

If this is the case, then why dont you use a SELECT DISTINCT clause, or a GROUP BY clause or a Sub Query in a WHERE Clause for fetching your Sub Report Data? that way you wont have the duplicate records in the first case!!! Isn't this correct?

If you can paste your queries for SubReport + MainReports, I can help you get the right Data at the first place, which will be even MUCH faster!

Hope this helps.

rgds,
Ajit Anand
0

Featured Post

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

822 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question