Style Expressions 101

[wselwood]

Style expressions 101: the basics

Style expressions allow you to control the style of an element depending on some run time expression. This is going to start really simply, if you know how to set up style expressions feel free to skip part one.

Part 1: How to set up style expressions

Let’s start with an example style expression. We need to hide the total line from the CSV output of our report because it’s going to be loaded by some thing else which will be confused by the extra line. However the excel and html versions will be read by people who can’t add stuff up in their heads really quickly.

Step One: Select the object you want to hide.
Step Two: In the properties list select the Style Expressions option. There is a little “…” button on the right.
Step Three: Press Add Expression
Step Four: Select the Style Key you are after. In our case it is the visible one and press ok
Step Five: in the large text area enter the following text: =NOT(ISEXPORTTYPE("table/csv"));
Step Six: Press Ok

You’re done; the report should now hide what ever you just applied that expression to when it’s outputting as CSV.

Part 2: What the heck was that magic string we just entered?

The style expression strings are in LibFormula Syntax (More information here: http://wiki.pentaho.com/display/Reporting/LibFormulaSyntax)

To explain the basics though we’ll pull apart the formula we just used:
=NOT(ISEXPORTTYPE("table/csv"));

= :makes it understand it’s a formula
NOT :Means what ever is in side the brackets we want the opposite
ISEXPORTTYPE :This returns true or false depending on the export type provided and the export type of the report.

There are several options for the ISEXPORTTYPE string
“table” any thing which uses the table layout will return true
“table/excel” excel output
“table/csv” CSV output
“table/html” html output
“table/rft” RTF output
“pageable” for any of the pageable outputs
“pageable/pdf” for PDF output

(thanks nanuganga for the info about pageable/pdf)

You can do all kinds of things with these expressions,

Part 3: what on earth do all those style keys do?
Some of the ones I use are listed below. This is not a complete list. Just the ones I have used.

Visible: this makes the item display or not. Expects true or false. When false this will completely remove the item from displaying, you shouldn’t even get an empty column (unless there is some thing else to create that column some where else in the report)

Paint: For boxes this controls the colour of the box. For text this controls the colour of the font. Expects a string with the hex value of the colour.

Padding-[bottom|left|right|top]: controls the cell padding. Expects a string representing the number of point of padding.

Backgroundcolor: the background colour of text. Expects a string of the hex value for the colour, much like the Paint style key.

A lot of them should be fairly obvious. Play, learn and if you find any useful tricks put them below.

Part 4: Useful tricks and examples.

We have already covered making things vanish at the top. So here we will cover some other useful tricks

Trick 1: Changing the background colour of a box to make it excel safe (Excel has a limited colour pallet (http://www.mvps.org/dmcritchie/excel/colors.htm), when you pick a colour it tries to find the nearest match, which is some times some thing completely weird)

Paint: =IF(ISEXPORTTYPE("table/excel"); "#C0C0C0"; "")

The IF command is very helpful its basic syntax is as follows
IF(some kind of expression that returns true or false; what is returns if the answer was true; what is returns if the expression is false)

If you return an empty string from the style expression it uses the default as there was no expression being involved.

Trick 2: Adding padding to the edges of the HTML and PDF exports with out adding an extra column to the CSV export. Excel ignores this setting if you want it to add cells set the report configuration setting:
org.pentaho.reporting.engine.classic.core.modules. output.table.xls.EmulateCellPadding=true
(See Taqua's comment below)

Padding-left: =IF(ISEXPORTTYPE("table/csv"); "0"; "3")


Any one wants to add to this feel free. As usual I'll keep an eye on this thread, post any questions you have below.
All information contained here is wrong. If any of it is correct I accept no responsibility.

Wil

[Taqua]

Groovy. I made it sticky so that it does not get lost.

For some reason excel seems to ignore this setting. (This might be a bug with the old version I am stuck with at the moment)

This is a limitation in excel. Excel-Cells cannot have padding. Therefore we would now have the choice to either insert empty cells to simulate paddings or to proceed as if no paddings were defined. As the most common use-case for the Excel export is to work with the report in Excel, empty cells are counter productive.

If you really want to have paddings via the empty-cells work-around, then you can opt-in for that via the report-configuration:

org.pentaho.reporting.engine.classic.core.modules.output.table.xls.EmulateCellPadding=true

(Excel has a limited colour pallet, when you pick a colour it tries to find the nearest match, which is usually some thing completely weird)

If you encounter weird colors mappings, please tell us. We try our best to map the non-excel colors into the visually nearest color (so that you barely see a difference). if our algorithm fails, then it is a bug that needs fixing.

[wselwood]

Thanks Taqua

The excel behaver on the cell padding is actually what I wanted, but wasn't sure if was intended, I'll remove that comment now.

The colours I get which are weird to me are #FACEAB which is kind of a vomit green colour but it comes out in excel a bright yellow. Also some light Gray colours come out white, which is technically the nearest colour but people might want it to be a darker Gray than the white.

I have edited the main post with your hints.

Thank you

Wil

[wselwood]

Another trick to add to the (small) pile derived with help from the fokes in the ##pentaho IRC room (mainly bugg_tb and pstoellberger).

Getting more descriptive names for excel sheet in a work book.

You can get extra sheets in an excel work book by inserting page breaks. However by default they have names sheet 0, sheet 1, sheet2 ... sheet n

so if our first page is a summary and we would rather have a summary say some thing like "summary" then we can use the good old style expressions to do this.

So the first thing to do is create a page number report function we can use to tell what page we are on. Then we need to start our style expression on the page header. Select the page header from the list of items and then pick style expressions from the properties as normal.

The style key we are after is computed-sheetname

the code we are after is some thing like the following:

=IF([pageFunction]=1; "Summary"; "Sheet " & [pageFunction]);

Ok some new things in this one if you haven't read the wiki link in part 2 of the original post. The use of values in the report and joining strings together. Of course you will need to change [pageFunction] to be what ever you want your page function to be named.

If you want your sheets to start at 1 rather than 2 like they will with the above example make sure your page function starts from 0 and then in the “if” make sure you check for 0 rather than 1.

Please let me know if you come across any problems with this.

[wselwood]

A big thank you to Taqua for taking the time to put this up (Yeah I know its been there a while but still think it might be worth adding to here to make it easier to find)

http://wiki.pentaho.com/display/Reporting/JFR8StyleKeys

A complete list of all the style keys and what magic they do.

Wil

[Fie]

Hi Taqua,
FYI I found a weird colourmapping:
Colour 127;133;126 (#505041) is dark grey in pdf and html. Conversion to excel colour gives 204;255;204 (#ccffcc), which is very bright light green.
I have also used two types of grey for background colour (236;240;249 (#ECF0F9) and 225;230;244 (#E1E6F4)). They both turn into the same blueish grey (204;204;255). But this is less 'weird' than the green colour.

Cheers,
Fie

[Fie]

Any one wants to add to this feel free.

Wil

I think this trick might be useful:

Prevent merged cells in excel.
For example you would like to put the title of the report in excel, but report designer makes it a merged cell. This is not friendly for users who would like to sort the data or do other processing of the data, since Excel has restrictions on merged cells.
Then you'll need two style expressions and one standard property:

style expressions:

overflow-x: =IF(isexporttype("table/excel");"true";"")

min-width: =IF(isexporttype("table/excel");"50";"")
(if the title is placed top-left, you should fill in the width of you first column, in my case 50)

standard property:
In the properties window (category: output) set the "Wrap Text In Excel" to false.

[wselwood]

Nice Fie. I take it those are set on the title object? I can think of more than a few uses for this.

Wil

[Fie]

You can use it for any field,that you would like to display in excel, but which is not part of the item band.
I have used it for the date of generation, title and a message field with inputparameters (period).
I can imagine you would like to use it for a logo field as well. Haven't tried that yet.... (image field is not text, hard (not) to wrap...)
But indeed it can be useful for more issues, like group headers, page footers etc.
Cheers!

[wselwood]

Ok with the advent of the new report designer (3.5) a lot of stuff has changed so time for an update. I’ll follow the same basic format any problems, questions or things not understood please post below.

Style Expressions 101 Mk2

Step 1) How to set up Style Expressions:

This bit has changed the most in the new version. The entire way style expressions are created has changed. The new way is a lot more intuitive and simple but I’ll still go over it as it’s important.

Using the same original example we need to make some thing hidden in one of the report output types. E.G the report footer in CSV output.

1) Select the object you want to hide.
2) Make sure the style tab is selected in the bottom right panel.
3) Scroll down to the visible line in the table, it’s in the “size and position” section
4) Click on the plus in the formula column
5) Enter “=NOT(ISEXPORTTYPE("table/csv"))” in the text box that pops up
6) Press Close.

Ta Da. One style expression. The report will now hide the object you just applied that to if it’s run as a csv output.

Part 2) What was that magic string?

This section is almost identical to the old one so I am not going to bother copying it again. The only major thing to note if you’re used to using the old style expressions is that the new ones are a lot stricter on syntax. You used to be able to get away with having extra junk at the end of your formula, extra semi-colons at the end of if statements and after the formula. No more. These will cause the formula to fail. So be careful when copying old formula around. I have tried to make sure all the formula in here are updated but I might have missed one.

Part 3) This was about what all the style keys do.

With the new PRD it is a lot easier to work out where to put your style expressions. The new formula column keeps things clear and easy to tell what attributes may be effected by style expressions. Some of the names will no longer match up to what they were before. (computed sheet name has become “sheetname” for instance) how ever you can easily test what an attribute does before using the style expression to make sure you have what you want by simply editing the attribute.

Part 4) Examples.

I have tried to update all the examples here so they have the right syntax. Any issues with them let me know

Trick 1: changing the colour to get excel colours.

This one is mostly the same. Seems I got the right formula before.

=IF(ISEXPORTTYPE("table/excel"); "#C0C0C0"; "")

This needs to be applied to either the “text-color” or “bg-color” attributes. (Strangely the “text-color” attribute seems to be the border colour of boxes)

Trick 2: adding Padding to html and pdf output

Again the formula is the same:

=IF(ISEXPORTTYPE("table/csv"); "0"; "3")

The attributes you need are in the Padding section. They should be pretty self explanatory as to which ones you need.

Trick 3: Sensible sheet names on multipage excel documents

The formula (make sure you don’t have the ‘;’ on the end like in the old example)

=IF([pageFunction]=1; "Summary"; "Sheet " & [pageFunction])

This one needs to be applied to the sheetname attribute in the excel section of the report header.

For Fie’s example the two formulas are the same but they now need to be applied to the x-overflow and width attributes.

Any one wants to add to it, feel free.

Good luck

Wil