Print Page | Close Window

Remove Duplicates From Groups

Printed From: Crystal Reports Book
Category: General Information
Forum Name: Announcements
Forum Discription: Please check this section for the latest announcements from Crystal Reports Forum
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19152
Printed Date: 28 Mar 2024 at 3:25am


Topic: Remove Duplicates From Groups
Posted By: chaba
Subject: Remove Duplicates From Groups
Date Posted: 20 Feb 2013 at 7:14am
Hello Friends,

I have a crystal report that contains below groups...

operation_no,
wc_no,
parent_part,
component_part,
next_op

for groups operation_no and wc_no's,
parent_part and component_part get duplicated.

please see below example...

operation_no: 20
wc_no: BR220
parent_part: 068-8187-1
component_part: 802-790

operation_no: 70
wc_no: FR110
parent_part: 068-8187-1
component_part: 802-790

at operation_no 70, i would like to suppress component_part (hide it from the report).

How can I do this? Please help.

Thank you and very best regards,
Chan



Replies:
Posted By: hilfy
Date Posted: 20 Feb 2013 at 10:34am
How many distinct parent_part/component_part combinations do you expect to have on a report?  I have one possibility for how to handle this that would not be too difficult to implement, but it will only work with up to 1,000 parts.  If there are more than 1,000 potential combinations, then you would have to use a command (SQL Select statement) or a stored procedure to provide the data for your report.  In that case, how good are your SQL skills?
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: chaba
Date Posted: 20 Feb 2013 at 11:24am
Hi,

Thank you so much for your reply.
i am using a database view as the data-source of the report.
>>>How many distinct parent_part/component_part combinations do you expect to have on a report? I would assume this number will always be less 1000.
Could you please let me (and the forum) know how i can solve this problem?
>>>In that case, how good are your SQL skills? I believe that I have a strong knowledge of SQL.


Thanks again for replying to my question.

regards,
Chan


Posted By: hilfy
Date Posted: 20 Feb 2013 at 12:15pm
Ok, two ways of doing this.  The first way uses an array in Crystal to determine whether the part information has already been printed and then suppresses sections when it has.
 
1.  Initalize a Shared Variable to contain an array:
 
StringVar Array parts := [""];
 
Place this formula in the report header.  Nothing will actually appear on the report, but the array will be initialized.
 
2.  Create a formula that concatenates the parent_part and component_part:
 
{parent_part} + {component_part};
 
3.  Create a third formula that will add the value from step 2 to the array:
 
WhilePrintingRecords;
StringVar Array parts;
 
if parts[1] = "" then parts[1] := {@Concatenated_Part}
else  if not {@Concatenated_Part} in parts then
{
  redim preserve parts[length(parts) + 1];
  parts[UBound(parts)] := {@Concatenated_Part};
}
 
Put this formula in its own Group Footer section for the Component_Part group (nothing else should be in the section with it).  Make the section as small as possible and make it the LAST group footer section for Component_part  Once again, it shouldn't show anything - it will just add the value to the array.
 
4.  In the Section Expert, set the suppress formula for all of the parent_part, component_part, and details sections.  The suppress formula will look something like this:
 
StringVar Array parts;
{@Concatenated_Part} in parts
 
This will suppress the section if the parent/component combination has already been printed.  The issue is that Arrays in Crystal have a 1,000 element limit.
 
The second method involve writing a command (SQL Select statement) or a Stored Procedure that will return all of the data for the report, excluding the parts that have already appeared in an earlier operation.  I can think of several different ways to write this depending on how your data is set up.  This way the database is doing all of the work and Crystal doesn't have to.  If your SQL skills are pretty good and you know the database well, this is the route I would take because it will be faster and you won't ever have to worry about the limit on the array size.
 
-Dell
 


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: chaba
Date Posted: 20 Feb 2013 at 4:19pm
wow! Thank you so much for that detailed answer. I will try this tomorrow (now it's my night).

I am sorry for being rude - but I have one question

"Put this formula in its own Group Footer section for the Component_Part group (nothing else should be in the section with it). Make the section as small as possible and make it the LAST group footer section for Component_part Once again, it shouldn't show anything - it will just add the value to the array"

What if I have another group underneath the component_group?

Thanks again for your help and I really appreciate it. I am glad about being a member of this forum.

Regards,
Chan


Posted By: chaba
Date Posted: 21 Feb 2013 at 4:07am
Hi,

As per your instructions,
I created a formula that initializes a shared array variable...
<<
Shared StringVar Array parts := [""];
Shared numberVar x := 1;
>>
I created formula to concatenate parent_part and component_part fields
<<
{SPVG_SO_WI\\.PARENT_PART} + {SPVG_SO_WI\\.COMPONENT_PART}
>>
I created another formula to add concatenated parts into the array
<<
WhilePrintingRecords;
StringVar Array parts;
Shared numberVar x;

if parts[x] = "" then parts[x] := {@concatParts}
else if not({@concatParts} in parts) then
(
redim preserve parts[Ubound(parts) + x];
parts[UBound(parts)] := {@concatParts};
)
>>

I placed the first formula in the report header
and the third in the component_part footer

then I ran the report.

but now I am getting this error...
---------------------------
Crystal Reports
---------------------------
A subscript must be between 1 and the size of the array.
---------------------------
OK   
---------------------------

I googled to find a solution for the above error. but i could not find any.

Could you please advice?

I am sorry for any inconvenience.

Regards,
Chan



Posted By: hilfy
Date Posted: 21 Feb 2013 at 5:23am
Are you ever changing the value of "X"?  If so, that's your problem.  It needs to always be 1.
 
Since there's no way to be sure where the error is coming from, I would comment out part of the formula so that it looks like this:
 
WhilePrintingRecords;
StringVar Array parts;
Shared numberVar x;

if parts[x] = "" then parts[x] := {@concatParts}
//else if not({@concatParts} in parts) then
//(
//redim preserve parts[Ubound(parts) + x];
//parts[UBound(parts)] := {@concatParts};
 
Then run the report and see whether the error happens.  If it does, then we know that's where the error is happening, if it doesn't, then it's in the other part of the formula.  Let me know what happens and I'll try to walk you through it from there.
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Posted By: chaba
Date Posted: 21 Feb 2013 at 5:33am
Thank you for the reply...

I commented the parts that you've asked me to comment...

WhilePrintingRecords;
StringVar Array parts;
Shared numberVar x;

if parts[x] = "" then parts[x] := {@concatParts}
//else if not({@concatParts} in parts) then
//(
//redim preserve parts[Ubound(parts) + x];
//parts[UBound(parts)] := {@concatParts};
//)

I am still getting the same error message.



Posted By: chaba
Date Posted: 22 Feb 2013 at 9:09am
I was able to resolve my problem - with Dell's help...

this is the solution that i used...

I followed Dell's instructions...

first i created the shared variable array (in a formula) and placed it in the report header
<<
Shared StringVar Array parts:=["", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", "", ""];
Shared numberVar x := 1;
>>
I created formula to concatenate parent_part and component_part fields
<<
{SPVG_SO_WI\\.PARENT_PART} + {SPVG_SO_WI\\.COMPONENT_PART}
>>
I created another formula to add concatenated parts into the array
<<
WhilePrintingRecords;
StringVar Array parts;

if not({@concatParts} in parts) then
(
redim preserve parts[Ubound(parts) + 1];
parts[UBound(parts)] := {@concatParts};
)
>>

last but not least i created the suppress condition in the detail section...
<<
({@concatParts} in parts
>>

worked like a charm!

Dell, thank you very much for your help and guidance.

regards,
Chan



Print Page | Close Window