We help IT Professionals succeed at work.

Data Report : Hide Duplicates

eddie_kho
eddie_kho asked
on
in the detail section, i want to hide the duplicates value..
how to do this?

99/99/9999  xxxxxxxxx   xxxxxxx   xxxxxx
                                         xxxxxxx   xxxxxx
                                         xxxxxxx   xxxxxx

99/99/9999  xxxxxxxxx   xxxxxxx   xxxxxx
                                         xxxxxxx   xxxxxx
                                         xxxxxxx   xxxxxx

99/99/9999  xxxxxxxxx   xxxxxxx   xxxxxx
                                         xxxxxxx   xxxxxx
                                         xxxxxxx   xxxxxx
Comment
Watch Question

are using data reports or crystal reports?
if you are using data reports then its is difficult to hide the repeated values.in that case use "unique" key in your SQL statement when you are writing command.

if you are using crystal reports ,there you can suppress duplicate values.

there are lot of limitations are there when you are using data reports.for the limitations visit the microsoft site.

Commented:
You're probably best of designing your query on which the report is based to eliminate duplicates. You can use things like group by, select distinct, etc.

If you like, post up the report's source, and let us know what you want it to produce, and we'll try and help you out.

Author

Commented:
lakavaram:
i'm using data report and data environment.
in case of using 'distinct' or 'unique' i won't get the multiple record that i want to but  just one record, right?

Tables Structure:
------------
TRANS_DETAIL
------------
*Date_      -> date of transaction
*Cd_Sales   -> sales is our employee id
*Nota       -> number/code
*Cd_Cust    -> customer id
*Cd_Item    -> item id
*Cd_Type    -> type of transaction : sell, drop, or retur
 Amount     -> amount of item that's sold/dropped/returned
--------
MS_SALES
--------
*Cd_Sales
 Name
 Address
-------
MS_CUST
-------
*Cd_Cust
 Name
 Address
-------
MS_ITEM
-------
*Cd_Item
 Name
 Price
-------
MS_TYPE
-------
*Cd_Type
 Name

Then I created a Command (named it Command1) in Data Environment (DataEnvironment1), the SQL statement:

SELECT Trans_Detail.*, Ms_Sales.Name, Ms_Cust.Name, Ms_Item.Name, Ms_Type.Name FROM Trans_Detail, Ms_Cust, Ms_Sales, Ms_Item, Ms_Type WHERE (Trans_Detail.Date_ BETWEEN Param1 AND Param2) AND (Trans_Detail.Cd_Sales BETWEEN Param3 AND Param4) AND (Trans_Detail.Cd_Cust BETWEEN Param5 AND Param6) AND (Trans_Detail.Cd_Item BETWEEN Param7 AND Param8) AND (Trans_Detail.Cd_Type BETWEEN Param9 AND Param10)
AND (Ms_Sales.Cd_Sales = Trans_Detail.Cd_Sales)
AND (Ms_Cust.Kd_Cust = Trans_Detail.Cd_Cust) AND (Ms_Item.Cd_Item = Trans_Detail.Cd_Item) AND (Ms_Type.Cd_Type = Trans_Detail.Cd_Type)
ORDER BY Trans_Detail.Cd_Sales, Trans_Detail.Date_

Then I created a Data Report (DataReport1):
DataReport1.DataSource = DataEnvironment1
DataReport1.DataMember = Command1

I retrieved the structure and the design was look like this:
-----------
PAGE HEADER
-----------
bla..bla.. bla...
page number, datem etc.. etc..
------------
GROUP HEADER
------------
Sales : [Cd_Sales] - [Ms_Sales.Name]

========================================= (column header)
 Date     Customer        Nota     Item    Type  ..etc, etc
========================================= (column header)
------
DETAIL
------
[Date_]   [Cd_Cust]-[Ms_Cust.Name]  [Nota] [Cd_Item]-[Ms_Item.Name]....etc,etc


99/99/9999  xxx-xx30xx xxxxx  xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
            xxx-xx30xx xxxxx  xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
            xxx-xx30xx xxxxx  xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
99/99/9999  xxx-xx30xx xxxxx  xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
            xxx-xx30xx xxxxx  xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
            xxx-xx30xx xxxxx  xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc
                              xxxxxx-xx30xx  x-x10x etc,etc

hope this is clear enough for u...
hi

for writing your query use visula link expert tools.there you can see the result of your query.there only you can eliminate the duplicate values.still you are geting the duplicate values there is problem in your query.in data reports there is no other facility to supress duplicate values.let me know whether your visula link tool is giving proper result or not?

Commented:
in case of using 'distinct' or 'unique' i won't get the multiple record that i want to but  just one
record, right?

With select distinct, you'll get all the records, but no duplicates. Isn't this what you want? If not it could either be that the grouping is not set up correctly on your report, or you have several very similar records which look like they are the same, but in fact are different (for example the Trans_Detail.Date field is varying in the time part, but looks the same 'cause you're only showing the date part).

Author

Commented:
i'm sorry, there must have been a misunderstood here....
i wasn't meant a duplicate value in the query, i meant to hide the duplicate values on the report not on the query.
there's no problem with my query...

lakavaram:
i couldn't find 'visual link tool', would u pls tell me more detail where i can found it...

for example the Trans_Detail contains:
-----------------------------------------------------------
Date_   Cd_Sales   Nota   Cd_Cust  Cd_Item  Cd_Type  Amount
-----------------------------------------------------------
09/22/01  001    0922998   00001   000001   1   100
09/22/01  001    0922998   00001   000001   2   200
09/22/01  001    0922998   00001   000002   1   150
09/22/01  001    0922999   00002   000003   1    80
09/24/01  001    0924999   00001   000003   1   100
09/24/01  001    0924999   00001   000003   1   100
09/24/01  001    0924999   00001   000003   1   100
09/24/01  001    0924999   00001   000003   1   100
09/24/01  001    0924999   00001   000003   1   100
-----------------------------------------------------------
then i want the report to looks like:

Sales : 001 - Peter
-----------------------------------------------------------
  Date  Customer   Nota    Item    Transaction  Amount
-----------------------------------------------------------
09/22/01  00001   0922998   000001   1   100
                                        2   200
                               000002   1   150
09/22/01  00002   0922999   000003   1    80

09/24/01  00001   0924999   000001   1   230
                               000002   1   110
                               000003   1    40
                                        2   123
                                        3    10
-----------------------------------------------------------

Commented:
OK, understand now. Unfortunately, the data report doesn't offer any facility to remove duplicates like this, you might have to create a new query.
It might be best to make a view of the query so that you only have one source query, the distinct version just reads
select distinct * from vQuery1

I couldn't follow your example (odd data?), do you mean:

Sales : 001 - Peter
-----------------------------------------------------------
 Date  Customer   Nota    Item    Transaction  Amount
-----------------------------------------------------------
09/22/01  00001   0922998   000001   1   100
                                       2   200
                              000002   1   150
09/22/01  00002   0922999   000003   1    80

09/24/01  00001   0924999   000001   1   100
-----------------------------------------------------------
its not possible to suppress the values in datareports.it is having lot of limitations.if you want to generate complex reports then you have to go for crystal reports.

to check your query result at design time when creating command in dataenvironment right click on commad and select design.then you will get two windows ,in one window you are having list of all tables in your database.drag all the tables on to the linking window.there you can create link between all the tables,then it will generate query for you.run the query there and test your query result.

Author

Commented:
andyclap:
what i tried to show u in the example is that it'll hide
the 'Date' and/or 'Customer' and/or 'Nota' and/or 'Item' when the line before is the same value (duplicate).

lakavaram:
i've got what u meant.. it works like what we can see in MSAccess, right?

after all, i think the answer for my problem is: data report doesn't support hiding duplicate.
but don't worry... i'll give point to both of u...
pls contact me at eddie_kho@hotmail.com if u find any other answer for my problem, thanks alot...

Author

Commented:
i'll give point for andyclap at another question...

Commented:
Thanks, glad we could help, even if the answer was no :)

Explore More ContentExplore courses, solutions, and other research materials related to this topic.