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).
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
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.
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 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).
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
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/
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
IP Logged
