Calculated fields in Domain wizard

[raskolnikov]

Hello, apologies if this is a very basic question. I'm a complete newbie on a 30 day trial of JasperServer. I was wondering where I can get more information about the expression language used in the 'Calculated Fields' tab of the Domain design wizard. I would like to simulate an SQL case statement on a particular field.

... Case when marital_status = 'M' then 'Married' when marital_status = 'S' then 'Single' else 'Other' end ...

I've tried finding more information about how to implement this logic on the online help and by referring to the public JasperServer documentation, but no joy.

Please could anyone point me in the right direction?

             

 

 

[kkelleher]

In your JS Pro eval installation, look in the docs directory for the user guide. Chapter 7 discusses the DomEL language's syntax.

Thanks,

Kristen

[raskolnikov]

Thanks Kristen. I've checked and the section on the DomEL syntax doesn't really give any examples or pointers about how to implement an SQL case statement on a field. Any help on how to do this using DomEL would be greatly appreciated.

For example, say I had a continuous variable 'Salary' (integer) and I wanted to define a calculated field 'SalaryBand' (string) depending on the value of 'Salary'.

If 'Salary' < 10000 then 'SalaryBand = 'Low'

If 'Salary' between 10000 and 40000 then 'SalaryBand' = 'Medium'

If 'Salary' > 40000 then 'SalaryBand' = 'High'.

Can this logic be implemented using DomEL?

[andrewsok]

The config file applicationContext-semanticLayer.xml declares all functions that DomEL supports.

There is "if" functions there :

                <entry key="if">

                    <value>"case when (" + sqlArgs[0] + ") then (" + sqlArgs[1] + ") else (" + sqlArgs[2] + ") end"</value>

                </entry>

If you call "if(f1, f2, f3)", f1 would be your condition field (boolean), f2 is the field reference to be used then f1==true, and f3 is the field to be used when f1==false

Your requirement would look like following :

you declare SalaryBand field and assign an expression 

if("Empoyee"."Salary" < 10000, 'Low', if("Empoyee"."Salary" < 40000, 'Medium', 'High')) 

[raskolnikov]

Brilliant! Thanks for this andrewsok, everything is working now.

[kkelleher]

We discussed this request, and now plan to add examples of each of the DomEL functions to the docs in an upcoming release. Thanks for posting!

-Kristen

[andre.erasmus]

works for me

[andre.erasmus]

works for me