SQL queryString arithmetic operations and functions

[martin.clarke]

OK guys.  I'm lost.  Can anyone point me in the direction of how to perform arithmetic operations and functions on java.math.BigDecimal fields please?

Well half an hour later I found

Jaspersoft Studio Documentation > Tutorials > Variables and Expressions

Expression Editor: what it is and how it works

---

A user that is editing an expression for a report, with language set to Groovy or Java, can see this interface:

...

My language according to the Dataset and Query Dialog is SQL.  Java isn't an option.

My query is

  SELECT w.ID, w.AUTHOR, w.STARTDATE, w.timeworked, i.summary

  from db.worklog w, db.issue i

  where AUTHOR = "<whoever>" and w.issueid = i.id

  Group BY w.STARTDATE

and returns 'timeworked' in seconds. 

I merely want to do what I can do in MySQL: 'round(w.timeworked/3600,2)' which returns hours to 2 decimal points.

Kind regards

Martin

 

[butlerc]

Few ways to do this;

1. Create another field in your SQL that has exactly what you need and just display that instead.

Re changing between java/groovy this is a report level setting.  Goto Advanced properties and under Report tab see language,  Default is JAVA, Groovy makes your life easier for logical expressions (although an experienced java developer would disagree with me im sure). 

Change report to Groovy then use the following expression in the field name: $F{TIMEWORKED}.divide(3600).setScale(2,java.math.RoundingMode.UP)

 

 

 

 

[martin.clarke]

Jaspersoft^® Studio

http://www.gravatar.com/avatar/699c906be679b559cf9f9d0a05378066.jpg?d=identicon&s=128&r=G

martin.clarke

4

Joined: Apr 20 2015 - 2:28am

Last seen: 12 sec ago

Thanks butlerc.  I added the field in the first place and it failed: Unknown column name timeworked in result set ... soooooo, I wondered intuitively if an alias was required.  I've never seen that before with any SQL (Oracle, SQLServer, MySQL) in 20 years, but you never know!

It was : ) 

 select w.ID, w.AUTHOR, w.STARTDATE, round(w.timeworked/3600,2) timeworked, i.summary  from db.worklog w, db.issue i  where AUTHOR = "<whoever>" and w.issueid = i.id  group by w.STARTDATE[/code]

As Java is the default I'll stay with that, but I'll look around as one does.

Is there ANY decent documentation anywhere?  That's rhetorical.  There's no book on Jaspersoft Studio and every book of iReports and JasperReports is very badly rated on Amazon.

Thanks again butlerc, you were a vital part of the solution!

Regards

Martin

 

[martin.clarke]

*

[martin.clarke]

The Jasper documentation is unbelievable. 

How does one align output? 

Everything aligns left. 

I've changed <textElement> to <textElement textAlignment="Right"> to no effect.

Any ideas?

[martin.clarke]

WAH-HOO

I can see the Expression Editor!

The trick is now ... how do I use it to format ANYTHING?!

[marianol]

There is also another way to do what you need if you only want to "DISPLAY" the numebr with 2 decimals you can use the Number Pattern in the textfield Properties, like so:

 The dialog that is shown appears when you click on the [ ... ] icon next to Pattern. That will keep the number with the full decimal and display with the mask that you select.

To work with alignement of the text inside the box you just need to select it form the alignment options in the same Textfield properties window just below "Pattern Expresion"

I think that your issues are coming because you do not have the Properties window open in JSS, it should be under the Palette View on your left ( you can move them around as you can see  in my screenshot I have it next to report output at the bottom of the screen)  if it's not there is because you have closed it, to see it again you can go to Window -> Reset Perspectve and will bring everything to the default view. 

[martin.clarke]

Thank you Marianol.  I have been busy with other things!  I've just caught up and truly appreciate your excellent response, which I've been able to follow.