How to Select Distinct Records where Input Parameter Exists in a View (Crystal Reports)?

[pressfit]

I have a Data Source (Stored Procedure) that has data in the following format:

+----------+-----------+-------+---------+
| Customer | Territory | Date  | Invoice |
+----------+-----------+-------+---------+
| CU1      | A3        | Date1 | I1      |
| CU2      | B2        | Date2 | I2      |
| CU1      | A3        | Date3 | I3      |
+----------+-----------+-------+---------+

Territory has a hierarchical structure i.e. A3 is under A2. A2 is under A1. For this, have created a View which has this data of the Territories:

+-----------+--------+
| Territory | Parent |
+-----------+--------+
| A1        | A1     |
| A2        | A1     |
| A2        | A2     |
| A3        | A1     |
| A3        | A2     |
| A3        | A3     |
+-----------+--------+

What I would like to do in Crystal Reports is:

1. Join the View and the SP:

SP. Territory = View.Territory

2. Record Selection By Parameter:

If @TerritoryParameter is present in View.Parent

How can this be done? I am getting duplicate results. If the number of Child-Parent links are 3 like in A3->A3, A3->A2, A3->A1, all records of the Customer CU1 are being returned 3 times:

+----------+-----------+-------+---------+
| Customer | Territory | Date  | Invoice |
+----------+-----------+-------+---------+
| CU1      |    A3     | Date1 | I1      |
| CU1      |    A3     | Date1 | I1      |
| CU1      |    A3     | Date1 | I1      |
| CU1      |    A3     | Date3 | I3      |
| CU1      |    A3     | Date3 | I3      |
| CU1      |    A3     | Date3 | I3      |
+----------+-----------+-------+---------+

Business Logic: If the Input Parameter is one of the PARENTS of the Customer's Territory, it means the Customer lies in the Selected Territory and hence, select it. Ignore all records who do not have the Input Parameter as one of their PARENTS.

Note: Input Parameter can take Multiple Values. Suppose the user selects both A1 and A2, only one record should be displayed, not two.

Can anyone please suggest how I can solve this problem? We want to be able to use Territory Selection Filters in our reports. Any and all suggestions are really appreciate.

[DellSC]

You can't use Select Distinct Records when you join a Stored Procedure or Command to anything. Basically what happens in this situation is that Crystal will pull all of the data from both the Stored Proc and the View into memory and filter it there.

You would do better at the "parent" data from the view into the data that's returned from the stored proc. Or build another stored proc that will do that. Or create a Command that will do that. 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/. Also, Crystal handles stored procs as if they were commands, so much of the information in the blog also applies to them.

-Dell

[former_member512104]

Aakash, you can select distinct records by activating the flag in "Database">"Select Distinct Records"

Let me know if that helped you!

[pressfit]

Hello,

The Select Distinct Records option is greyed out for me.

I am currently using OLE DB - Stored Procedure and View to get the data.