Solved

Suppress if duplicated (field) leaving blank lines

Posted on 2004-09-20
8
2,356 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

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…
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 …
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

747 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now