?
Solved

Suppress if duplicated (field) leaving blank lines

Posted on 2004-09-20
8
Medium Priority
?
2,671 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 189 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
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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 101

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 101

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 186 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

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2
Suggested Courses

839 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