pentaho report designer.pdf
Post on 06-Jul-2018
271 Views
Preview:
TRANSCRIPT
-
8/18/2019 Pentaho Report Designer.pdf
1/89
-
8/18/2019 Pentaho Report Designer.pdf
2/89
-
8/18/2019 Pentaho Report Designer.pdf
3/89
-
8/18/2019 Pentaho Report Designer.pdf
4/89
-
8/18/2019 Pentaho Report Designer.pdf
5/89
-
8/18/2019 Pentaho Report Designer.pdf
6/89
-
8/18/2019 Pentaho Report Designer.pdf
7/89
-
8/18/2019 Pentaho Report Designer.pdf
8/89
-
8/18/2019 Pentaho Report Designer.pdf
9/89
-
8/18/2019 Pentaho Report Designer.pdf
10/89
-
8/18/2019 Pentaho Report Designer.pdf
11/89
-
8/18/2019 Pentaho Report Designer.pdf
12/89
-
8/18/2019 Pentaho Report Designer.pdf
13/89
-
8/18/2019 Pentaho Report Designer.pdf
14/89
-
8/18/2019 Pentaho Report Designer.pdf
15/89
-
8/18/2019 Pentaho Report Designer.pdf
16/89
-
8/18/2019 Pentaho Report Designer.pdf
17/89
-
8/18/2019 Pentaho Report Designer.pdf
18/89
-
8/18/2019 Pentaho Report Designer.pdf
19/89
-
8/18/2019 Pentaho Report Designer.pdf
20/89
-
8/18/2019 Pentaho Report Designer.pdf
21/89
-
8/18/2019 Pentaho Report Designer.pdf
22/89
-
8/18/2019 Pentaho Report Designer.pdf
23/89
-
8/18/2019 Pentaho Report Designer.pdf
24/89
-
8/18/2019 Pentaho Report Designer.pdf
25/89
-
8/18/2019 Pentaho Report Designer.pdf
26/89
-
8/18/2019 Pentaho Report Designer.pdf
27/89
-
8/18/2019 Pentaho Report Designer.pdf
28/89
-
8/18/2019 Pentaho Report Designer.pdf
29/89
-
8/18/2019 Pentaho Report Designer.pdf
30/89
-
8/18/2019 Pentaho Report Designer.pdf
31/89
-
8/18/2019 Pentaho Report Designer.pdf
32/89
-
8/18/2019 Pentaho Report Designer.pdf
33/89
-
8/18/2019 Pentaho Report Designer.pdf
34/89
-
8/18/2019 Pentaho Report Designer.pdf
35/89
-
8/18/2019 Pentaho Report Designer.pdf
36/89
-
8/18/2019 Pentaho Report Designer.pdf
37/89
-
8/18/2019 Pentaho Report Designer.pdf
38/89
-
8/18/2019 Pentaho Report Designer.pdf
39/89
-
8/18/2019 Pentaho Report Designer.pdf
40/89
-
8/18/2019 Pentaho Report Designer.pdf
41/89
-
8/18/2019 Pentaho Report Designer.pdf
42/89
-
8/18/2019 Pentaho Report Designer.pdf
43/89
-
8/18/2019 Pentaho Report Designer.pdf
44/89
-
8/18/2019 Pentaho Report Designer.pdf
45/89
-
8/18/2019 Pentaho Report Designer.pdf
46/89
-
8/18/2019 Pentaho Report Designer.pdf
47/89
-
8/18/2019 Pentaho Report Designer.pdf
48/89
-
8/18/2019 Pentaho Report Designer.pdf
49/89
-
8/18/2019 Pentaho Report Designer.pdf
50/89
-
8/18/2019 Pentaho Report Designer.pdf
51/89
-
8/18/2019 Pentaho Report Designer.pdf
52/89
-
8/18/2019 Pentaho Report Designer.pdf
53/89
-
8/18/2019 Pentaho Report Designer.pdf
54/89
-
8/18/2019 Pentaho Report Designer.pdf
55/89
-
8/18/2019 Pentaho Report Designer.pdf
56/89
-
8/18/2019 Pentaho Report Designer.pdf
57/89
-
8/18/2019 Pentaho Report Designer.pdf
58/89
-
8/18/2019 Pentaho Report Designer.pdf
59/89
-
8/18/2019 Pentaho Report Designer.pdf
60/89
-
8/18/2019 Pentaho Report Designer.pdf
61/89
-
8/18/2019 Pentaho Report Designer.pdf
62/89
-
8/18/2019 Pentaho Report Designer.pdf
63/89
-
8/18/2019 Pentaho Report Designer.pdf
64/89
-
8/18/2019 Pentaho Report Designer.pdf
65/89
-
8/18/2019 Pentaho Report Designer.pdf
66/89
-
8/18/2019 Pentaho Report Designer.pdf
67/89
-
8/18/2019 Pentaho Report Designer.pdf
68/89
-
8/18/2019 Pentaho Report Designer.pdf
69/89
-
8/18/2019 Pentaho Report Designer.pdf
70/89
-
8/18/2019 Pentaho Report Designer.pdf
71/89
-
8/18/2019 Pentaho Report Designer.pdf
72/89
-
8/18/2019 Pentaho Report Designer.pdf
73/89
-
8/18/2019 Pentaho Report Designer.pdf
74/89
-
8/18/2019 Pentaho Report Designer.pdf
75/89
-
8/18/2019 Pentaho Report Designer.pdf
76/89
-
8/18/2019 Pentaho Report Designer.pdf
77/89
PRD Advanced Concepts
Conditional Hide Report ElementsTo hide report elements based on certain conditions, we can define a formula under
-
8/18/2019 Pentaho Report Designer.pdf
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.
-
8/18/2019 Pentaho Report Designer.pdf
79/89
Calculate Date
Sometimes it is very useful to use a calculated earlier date (e.g. yesterday, a
-
8/18/2019 Pentaho Report Designer.pdf
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
-
8/18/2019 Pentaho Report Designer.pdf
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
-
8/18/2019 Pentaho Report Designer.pdf
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
-
8/18/2019 Pentaho Report Designer.pdf
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:
Dynamic SQL in Report Designer
* Create a table dataset
-
8/18/2019 Pentaho Report Designer.pdf
84/89
Dynamic SQL in Report Designer
* Create a parameter associated to the table dataset
-
8/18/2019 Pentaho Report Designer.pdf
85/89
Dynamic SQL in Report Designer
* Create a JDBC Custom query
-
8/18/2019 Pentaho Report Designer.pdf
86/89
... And choose the JDBC
data source
-
8/18/2019 Pentaho Report Designer.pdf
87/89
Dynamic SQL in Report Designer
* Define the dynamic query associated to the master report
-
8/18/2019 Pentaho Report Designer.pdf
88/89
Then define the query as an expression “injecting” the name of the
parameter
-
8/18/2019 Pentaho Report Designer.pdf
89/89
top related