Jasper Studio - Unable to get distinct count to work?

[DavidT_UK]

Hello,

 

I'm having an issue with a studio report due to duplicated rows.  Our query draws data out fine but some entries (it's looking at orders) have multiple lines against them that are duplicates but we don't need to see. Initially I tried using the 'print repeated values' option and it worked to a degree but some lines are not quite duplicates as they have a different value and line number, so using that option removes the data from all of the columns but leaves the columns that have updates.  Without the repeated values option it's like this:

 

Sys Ref - Description - SupRef - Line - Code - Entry Dat - Year - Value 

1236300 - DECEMBER 2015 - 0011 - 1 - 8800GT - 05/01/16 - 2015 - 422800.50  

1236300 - DECEMBER 2015 - 0011 - 1 - 8800GT - 05/01/16 - 2015 - 422800.50

1236300 - DECEMBER 2015 - 0011 - 2 - 8870GT - 05/01/16 - 2015 - 677500.50  

1236300 - DECEMBER 2015 - 0011 - 2 - 8870GT - 05/01/16 - 2015 - 677500.50  

1236300 - DECEMBER 2015 - 0011 - 2 - 8870GT - 05/01/16 - 2015 - 677500.50

With print repeated values off it ends up looking like this: (same 5 lines but with just the duplicates row data removed, blank lines in place)

 

   Sys Ref - Description - SupRef - Line - Code - Entry Dat - Year - Value 

* 1236300 - DECEMBER 2015 - 0011 - 1 - 8800GT - 05/01/16 - 2015 - 422800.50  

* 

*                                                           - 2 - 8870GT -                            - 677500.50  

*

*

 

What we actually need it to look like is this: (it recognises 2/3rds of the row is a duplicate but that row is valid due to the slight change but is shown only once).

 

Sys Ref - Description - SupRef - Line - Code - Entry Dat - Year - Value 

1236300 - DECEMBER 2015 - 0011 - 1 - 8800GT - 05/01/16 - 2015 - 422800.50  

1236300 - DECEMBER 2015 - 0011 - 2 - 8870GT - 05/01/16 - 2015 - 677500.50  

 

Basically, what I need is if the SysRef, Desc, SupRef, Entry date andyear are the same but the Line, Code and Value are different just display that 2 varient  of the line the once and then carry on.  In SQL this is a really easy distinct command, however in Studio I've found the Distinct option under variables but it doesn't appear to doanything and the help documentaion isn't helpful in how to apply it effectively.

I'm fairly certain that the software can do what we need, but I need help with this; can anyone advise on how to get a distinct count to work?

 

Thanks 

 

[kmn]

For removing duplicate rows you can create a dummy group[Without any field and exclude Group header or Footer While Creating].

Select all the fields in Detail band and in print when expression give $V{DummyGroup_COUNT}.intValue()==1

This will eliminate duplicate values.

[DavidT_UK]

The dummy group method 2/3rd works; I'm now missing duplicates but it has had a side effect of removing those rows that are almost duplicated except for having different data in 2 or 3 of the columns (they are otherwise the same).

Technically they are not duplicates in our DB but for the report we don't need to know about them if there is more than one, but when they are adjusted slightly than we need to see that (it displays as line 2,3,4 etc. on the report).  It's also putting a gap between the lines it has blanked out rather than pulling them up together.