Solved

Suppress if duplicated (field) leaving blank lines

Posted on 2004-09-20
8
2,529 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

717 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