Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Checking if the same value exists. Post Reply Post New Topic
Author Message
Kaemus
Newbie
Newbie


Joined: 07 Sep 2017
Location: Poland
Online Status: Offline
Posts: 4
Quote Kaemus Replybullet Topic: Checking if the same value exists.
    Posted: 07 Sep 2017 at 8:28pm
Hi,

I have been battling this issue for couple of days
and finally I'm forced to ask here.

The data table provides the following data:

          DocType      DocNr     Value
                1             doc1      100
                1             doc2      150
                1             doc3      300
          Group 1 summary       550
                2             doc4      100
                2             doc5      600
                2             doc6      800
          Group 2 summary       1500

I'd like to add column checking if a given value is repeated
at least once in other rows (like doc1 and doc4 in the above
example). Usually the repated values would be in two different
groups.

I was trying to create a subreport, load all data to a shared array
(I didn't manage to load numeric values into array) and then wanted
to use the shared array in main report to check the condition in loop.

I also thought about using SQL expression but I can't use my parameter
there (the parameter is limiting the date of documents).

I'll be very grateful for your hints.

Kris.

IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 08 Sep 2017 at 5:22am
The one way I can think of to handle this is to use a subreport. Basically you'll link up the same tables as in your main report along with the same selection criteria with the addition of the condition that the value = the value from the main report. Suppress all of the sections in the subreport except either the report header or the report footer section. Assuming that you just want to show an "X" when there's a duplicate, you'll have a single formula that looks like this:

if count({value field}) > 1 then 'X' else ''

Put this formula in the section that is not suppressed.

Put this subreport in the same row as your data and link to the date parameter you mention and the value field.

-Dell
IP IP Logged
Kaemus
Newbie
Newbie


Joined: 07 Sep 2017
Location: Poland
Online Status: Offline
Posts: 4
Quote Kaemus Replybullet Posted: 10 Sep 2017 at 8:41pm
Thank you very much, it works.

Now I'm trying to add another condidtion I have
to apply - I need to get the duplicates only between
two types of documents (not within the same type).

I hope to solve it by myself : )

Kris.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 11 Sep 2017 at 9:29am
Cool! Good luck! Let me know if you need help with it - I can see how to do it still with the subreport.

-Dell
IP IP Logged
Kaemus
Newbie
Newbie


Joined: 07 Sep 2017
Location: Poland
Online Status: Offline
Posts: 4
Quote Kaemus Replybullet Posted: 12 Sep 2017 at 2:02am
Hi again.

I managed to do that using groups in subreport, following the logic
from your post.

The subreport returns DistinctCount of types to the main report
(it's "one" or "multiple").

Now I need to filter the main report - remove all records with "multiple".

I tried the following:
1. Using shared variable passed from subreport to the main report.
I looked OK until I wanted to use it in select expert and got a message
saying it can not be used in formula becouse it has to be evaluated later

2. Suppressing a section based on the value of shared value.
For some reason it doesn't work - it doesn't suppress whole row but
only the field with shared variable.
Before suppressing I had to divide details section into A and B (the shared
variable was apearing one row below the desired one).

Thank you for your help.

Kris.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 12 Sep 2017 at 3:36am
You can't use values from a subreport in the Select Expert because the subreport doesn't get run until after the data in the main report has been selected.

Put the shared value in a section ABOVE the the desired one so that it gets evaluated BEFORE the section is rendered.

If I had realized that you needed to filter your data based on this, I would have recommended that you use a Command (SQL Select Statement) instead of linking tables to get ALL of the data for your report. You can do this type of filtering in SQL directly. For information about how to work with commands, see my blog post here: https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

-Dell
IP IP Logged
Kaemus
Newbie
Newbie


Joined: 07 Sep 2017
Location: Poland
Online Status: Offline
Posts: 4
Quote Kaemus Replybullet Posted: 12 Sep 2017 at 10:26pm
Thanks, I'll go this direction.

Kris.
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.031 seconds.