DavidT_UK

Thanks; I've adjusted the class to be 'net.sf.jasperreports.types.date.DateRange' but it's the expression itself I have no clue about (hence the post!) I found something online that seemed was doing the same thing but worked, but it was from almost a decade ago so I tried a variation using the fields we have: ($F{createdate}.compareTo({$P{CreateDate_0}}.getStart()) >= 0 && $F{createdate}.compareTo({$P{CreateDate_0}.getEnd()) <= 0 That just causes a crash with a 'can't find field' error. 'Createdate' is the field and 'CreatDate_0' is the parameter.

Hi, We've got a generic report built in studio that just lists a certain type of spend, and we've been asked to schedule it. Not normally a problem as you can just enter 'month-1' in the date parameter within the schedule screen or in the report, however we had to use Studio for this one due to a duplicate line issue (related to GRN lines etc). I've got all the generic filters available, so equals, before, after, between, on or after etc but I can't use Month-1 when entering details for the parameter like I can with Adhoc. I'm assuming this is an easy amendment to the expression? It currently looks like this, as we have it running from 01/08/23 -to date which is too much data: new java.sql.Timestamp(new java.util.GregorianCalendar(2023,8,1,0,0,0).getTimeInMillis()) I've played around with the expression editor but my attempts so far just cause a crash when run.

Hi, I've got an adhoc report that lists orders from suppliers, but it's also listing duplicates (invoicing related). There is an option in custom expressions for this, but it doesn't seem to be working. When I use command CountDistinct("Order Reference", 'Total') I'm getting an 'Aggregate expressions are not allowed in fields; please create a measure if you wish to use an aggregated expression' I've tried it with several of the different options like RowTotal/RowGroup etc but its the same. Thoughts?

TT, I'd already used the grouping to get the data how we thought it was wanted, and it would be perfect if we didn't need more grouping on top of that. I can't post a pic of it atm, but this is a short version of our current output , which is grouped by Nominal description and Period Description (months as shown below). *Nominal 1* - Fin Value August Totals 10.00 July Totals 10.00 June Totals 10.00 Nominal 1 Totals 30.00 --- *Nominal 2* - Fin Value August Totals 10.00 July Totals 20.00 June Totals 10.00 Nominal 2 Totals 40.00 --- *Nominal 3* - Fin Value August Totals 10.00 July Totals 10.00 June Totals 10.00 Nominal 3 Totals 30.00 ----------- Grand Total 100.00 The full report has 11 nominals in it, 5 are 'income' and 6 are 'expense' Noms, so the customer has asked for us to sub total the 2 groups and then grand total that (so in the example above it would be a group of Nom 1 and a group of Nom2 & 3). I've been told I can create a calculated field that does the grouping, but I've not been able to get it to work.

Hi, we've got a fairly standard report built in Adhoc (table view) that just lists a bunch of nominal codes with values, grouped by the nominal and the period. The output is great, its exactly what is wanted and displays the nominal description (grouped) as a sub heading, with a column for the value and lines under the nom description showing the period (June, July, August etc) with that periods value to the right of it. We are using 'Totals Data' and we get a grand total of all of these noms at the bottom. The customer has come back to us and advised that the Noms used are actually 2 groups, and they want what we have already + a subtotal on both groups with a grand total at the end. I've tried creating a calculated measure and a calculatedfield that we can use to groups the 2 sets of Noms together, but can't get it to work. Is it possible in Adhoc to do this?

Thanks everyone, that's sorted the issues I was having and it's looking fine now :D

Thanks Duggu, that is perfect! Much appreciated.

Hello, I've got a report that when run outputs a number 1 (it's a string field) but I need it to actually display as 'INV' (the 1 means invoice in the application). I'm fairly new to Jasper Studio so I'm having trouble on what I suspect is a very easy variable. Can anyone help? I've tried looking for it but everything being posted is overly complicated and much more than i need. Thanks.

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.

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

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 - 5252A456781436300 - 1 - DECEMBER 2015 - 2015 - 1,800.90 - 8800A123451436300 - 1 - DECEMBER 2015 - 2015 - 1,800.90 - 8800A123451436300 - 2 - DECEMBER 2015 - 2015 - 1,800.90 - 8801A123451436300 - 2 - DECEMBER 2015 - 2015 - 1,800.90 - 8801A12345The 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?