Announcements
 Crystal Reports Forum : General Information : Announcements
Message Icon Topic: Remove Duplicates From Groups Post Reply Post New Topic
Author Message
chaba
Newbie
Newbie


Joined: 20 Feb 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote chaba Replybullet Topic: Remove Duplicates From Groups
    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
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3668
Quote hilfy Replybullet 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
IP IP Logged
chaba
Newbie
Newbie


Joined: 20 Feb 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote chaba Replybullet 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
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3668
Quote hilfy Replybullet 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
 
IP IP Logged
chaba
Newbie
Newbie


Joined: 20 Feb 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote chaba Replybullet 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
IP IP Logged
chaba
Newbie
Newbie


Joined: 20 Feb 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote chaba Replybullet 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

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3668
Quote hilfy Replybullet 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
IP IP Logged
chaba
Newbie
Newbie


Joined: 20 Feb 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote chaba Replybullet 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.

IP IP Logged
chaba
Newbie
Newbie


Joined: 20 Feb 2013
Location: Canada
Online Status: Offline
Posts: 6
Quote chaba Replybullet 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
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.047 seconds.