Pentaho Report Designer.pdf

8/18/2019 Pentaho Report Designer.pdf

1/89


2/89


3/89


4/89


5/89


6/89


7/89


8/89


9/89


10/89


11/89


12/89


13/89


14/89


15/89


16/89


17/89


18/89


19/89


20/89


21/89


22/89


23/89


24/89


25/89


26/89


27/89


28/89


29/89


30/89


31/89


32/89


33/89


34/89


35/89


36/89


37/89


38/89


39/89


40/89


41/89


42/89


43/89


44/89


45/89


46/89


47/89


48/89


49/89


50/89


51/89


52/89


53/89


54/89


55/89


56/89


57/89


58/89


59/89


60/89


61/89


62/89


63/89


64/89


65/89


66/89


67/89


68/89


69/89


70/89


71/89


72/89


73/89


74/89


75/89


76/89


77/89

PRD Advanced Concepts

Conditional Hide Report ElementsTo hide report elements based on certain conditions, we can define a formula under


78/89

'Style' -> 'visible'.

One interesting use case is to hide the labels of the report if no data is returned.For example, to hide all labels of the report if the field 'field1' has a null value:

Add a sub-band on the report and insert the labels that you want to hide in the sub-

band. Thus we will not have to define the same formula for each label, but rather

only for the sub-band.

Highlight the sub-band and go to 'Style' -> 'visible' properties.

Add the following formula =IF(ISNA([field1]);"False";"True")

We could also hide the report elements based on whether we have entered a value for

a user parameter or not.

Highlight the report element and go to 'Style' -> 'visible' properties

Enter the below formula =IF(LEN([param1])=0;"False"; "True")

where param1 is the parameter name.


79/89

Calculate Date

Sometimes it is very useful to use a calculated earlier date (e.g. yesterday, a


80/89

y ( g y y,

month ago) as a default parameter value on a report. For example, if you

want a report to run every day for the previous day, you might want to

add a calculation of 'yesterday' for the default value of the parameter.

To calculate 'yesterday' in the report designer use the following formula in

the 'default value formula' or 'post processing formula' of the parameter:

=DATEVALUE(DATE(YEAR(TODAY()-1);MONTH(TODAY()-1);DAY(TODAY()-1)))

To calculate '12 month ago' in the report designer use the following formula inthe 'default value formula' or 'post processing formula' of the parameter:

=DATEVALUE(DATE(YEAR(TODAY());MONTH(TODAY())-12;DAY(TODAY())))

Configure JNDI for Pentaho Report Designer

It is recommended to use JNDI connection on the Pentaho BI server for security purposes and to simplify


81/89

the maintenance of the connection details for the reprorts.

For the report to reference the JNDI connection as configured in the Enterprise Console, JNDI connection

needs to be referenced in the Report Designer, before the report is published to the server.

To be able to preview your report in the Report Designer using JNDI connection, you will need to

configure the connection details.

The JNDI connections for the Pentaho Report Designer are configured in the following file:.pentaho\simple-jndi\default.properties

Add your connection details in this file and you will be able to connect to the data using the JNDI name in

the Report Designer.

As an example, use the following format to define a new JNDI connection

SampleJNDIName/type=javax.sql.DataSource SampleJNDIName/driver=com.mysql.jdbc.Driver

SampleJNDIName/user=SampleUser SampleJNDIName/password=SamplePassword

SampleJNDIName/url=jdbc:mysql://localhost:3306/SampleData

Set parameter value on user log in

In the example below we will determine the value of the

location parameter depending on the user log in If you


82/89

location parameter depending on the user log in. If you

want the user to see a certain report for their location

only, this is the way to do it.Assuming you are storing the user names and corresponding

locations in a data base table called dim_user (mysql

dump attached)

Add a query to your report to retrieve the location of the

user. To identify the login of the user

${env::username} in your query

select * from dim_user where username=${env::username}

Add a parameter to your report and configure as shown.

Make sure you set the post-processing formula (this is

setting the value of the parameter) and tick "Use first

value if default value formula results in N/A" You canmake this parameter hidden as it does not require any

user input - tick "Hidden".

Dynamic SQL in Report Designer

We can inject parameters into the SQL query in the Report Designer using


83/89

the syntax

SELECT ${param1} FROM ${param2} WHERE field=${param3}

It is also possible to change the SQL statement completely, based on

user's selection.

With this option we can determine the query behaviour based on some

parameterization (i.e. Fields in select statement, sorting/grouping etc)

Let’s see an example:


* Create a table dataset


84/89


* Create a parameter associated to the table dataset


85/89


* Create a JDBC Custom query


86/89

... And choose the JDBC

data source


87/89


* Define the dynamic query associated to the master report


88/89

Then define the query as an expression “injecting” the name of the

parameter


89/89

Date post:	06-Jul-2018
Category:	Documents
Upload:	naua2010
View:	268 times
Download:	3 times

Pentaho Report Designer.pdf

Documents