Jump to content
Changes to the Jaspersoft community edition download ×

Ad-Hoc - Duplicate rows / Hide grouping or use distinct query?


david.thompson

Recommended Posts

Hi,

We have just started using Jasper Reporting Ad-Hoc/Studio which is linked into/getting it's data and domain from the E5 application (Finance program).  We have a 100+ SQL reports we run that up until Jasper came along output the data fine, however obviously it had the full flexibility of being able to use SQL commands without limitations.  On attempting to reproduce one such report in Ad-Hoc it appeared to work perfectly fine except for the fact that some rows were duplicated.  When looking at the SQL I found it was using a decode and substrings to help sort the data and remove the duplicates (in the example below it just shows the 1st, 2nd and 4th row, but Ad-hoc shows all 5).

Example:

Sys Ref - Trans Line - Desc - Year - Amount - AccDesc

1435700 - 1 - Rental of equipment - 2015 - 5,100.25 - 5252A45678
1436300 - 1 - DECEMBER 2015 - 2015 - 1,800.90 - 8800A12345
1436300 - 1 - DECEMBER 2015 - 2015 - 1,800.90 - 8800A12345
1436300 - 2 - DECEMBER 2015 - 2015 - 1,800.90 - 8801A12345
1436300 - 2 - DECEMBER 2015 - 2015 - 1,800.90 - 8801A12345

The Trans line and Acc Desc columns are the ones I figured I could use a distinct on in ad-hoc to filter/sort as required, except I'm not sure how to do it.  I did think about grouping, but it looks horrible in table form and really doesn't work in Crosstab (I've removed 7 other columns from the example).  If I could have it grouped without it actually showing the grouping that would be great but I can't see that it can do that either.  We want to avoid having to use Jasper Studio for these reports as much as possible, and this report is 98% there except for the duplicate rows.

Can anyone help at all?

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...