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
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?
