| Wiki Markup |
|---|
{scrollbar}
{ |
| Excerpt |
|---|
...
How |
...
to |
...
create |
...
a |
...
parameterize |
...
report |
...
that |
...
uses |
...
data |
...
from |
...
a |
...
collection |
...
in |
...
MongoDB. |
...
...
the
...
end
...
of
...
this
...
guide
...
you
...
should
...
understand
...
how
...
data
...
can
...
be
...
read
...
from
...
MongoDB
...
and
...
used
...
in
...
a
...
report.
...
The
...
data
...
we
...
are
...
going
...
to
...
use
...
contains
...
data
...
about
...
the
...
flow
...
of
...
visitors
...
to
...
a
...
web
...
site.
...
This
...
guide
...
shows
...
how
...
to
...
create
...
a
...
report
...
that
...
shows
...
the
...
most
...
popular
...
landing
...
pages
...
for
...
the
...
sample
...
web
...
site.
...
Intro
...
Video
Prerequisites
In order follow along with this how-to
...
guide
...
you
...
will
...
need
...
the
...
following:
...
MongoDB
...
A
...
single-node
...
local
...
cluster
...
is
...
sufficient
...
for
...
these
...
exercises
...
but
...
a
...
larger
...
and/or
...
remote
...
configuration
...
will
...
work
...
as
...
well.
...
You
...
will
...
need
...
to
...
know
...
the
...
address
...
and
...
port
...
that
...
MongoDB
...
is
...
running
...
on
...
and
...
have
...
a
...
user
...
id
...
and
...
password
...
for
...
the
...
server
...
(if
...
applicable).
...
These
...
guides
...
were
...
developed
...
using
...
the
...
MongoDB
...
version
...
2.0.2.
...
You
...
can
...
find
...
MongoDB
...
downloads
...
here:
...
http://www.mongodb.org/downloads
...
Kettle
A desktop installation of the Kettle design tool called 'Spoon'.
...
Download
...
...
Pentaho Report Designer
Pentaho Report Designer (PRD)
...
is
...
a
...
desktop
...
tool
...
for
...
creating
...
highly
...
formatted
...
reports
...
that
...
can
...
be
...
exported
...
to
...
many
...
popular
...
formats.
...
Reports
...
created
...
with
...
PRD
...
can
...
be
...
published
...
to
...
a
...
Pentaho
...
BI
...
Server
...
so
...
they
...
can
...
be
...
accessed
...
using
...
a
...
browser.
...
Download
...
...
Data
To follow this guide you need to have a populated MongoDB collection. If you do not have any data in MongoDB yet you can use the Write Data To MongoDB guide to add some data to your MongoDB installation. You will also need the transformation created in Create a Report with MongoDB. The instructions in this guide assume that the demo data set is available in a collection called PageSuccessions in a database called Demo and that you have access to the transform called 'top_landing_pages.ktr'.
...
Step-By-Step
...
Instructions
...
We
...
will
...
create
...
the
...
report
...
using
...
two
...
tools.
...
First
...
we
...
will
...
use
...
Spoon
...
to
...
modify
...
the
...
data
...
transformation
...
that
...
selects
...
data
...
from
...
MongoDB
...
and
...
sorts
...
it
...
into
...
descending
...
order.
...
Then
...
we
...
will
...
use
...
PRD
...
to
...
create
...
a
...
report
...
using
...
the
...
data
...
transformation
...
as
...
its
...
data
...
source.
...
Setup
...
Start
...
MongoDB
...
if
...
is
...
not
...
running.
...
Modify
...
the
...
Data
...
Transformation
...
Start
...
Spoon
...
on
...
your
...
desktop.
...
- Open the Transformation: Choose 'File'
...
- ->
...
- 'Open'
...
- from
...
- the
...
- menu
...
- system.
...
- Select
...
- the
...
- 'top_landing_pages.ktr'
...
- file.
- Edit the MongoDb Input Step: Double-click
...
- on
...
- the
...
- 'MongoDb
...
- Input'
...
- step
...
- to
...
- edit
...
- its
...
- properties.
...
- Enter
...
- this
...
- information:
...
- Host
...
- name,
...
- Port,
...
- Authentication
...
- user
...
- and
...
- password:
...
- the
...
- connection
...
- information
...
- for
...
- your
...
- MongoDB
...
- installation.
...
- Database:
...
- 'Demo'
...
- or
...
- another
...
- database
...
- if
...
- you
...
- want.
...
- Collection:
...
- 'PageSuccessions'
...
- Query
...
- expression:
...
- {
...
- "$query"
...
- :
...
- {
...
- "url"
...
- :
...
- "$
...
- {page
...
- }"
...
- },
...
- "$orderby"
...
- :
...
- {
...
- "Count"
...
- :
...
- -1
...
- }
...
- }
...
The
...
- $
...
- {
...
- }
...
- syntax
...
- is
...
- used
...
- to
...
- specify
...
- that
...
- a
...
- parameter
...
- should
...
- be
...
- inserted.
...
- In
...
- this
...
- case
...
- the
...
- parameter
...
- is
...
- called
...
- "page".
...
The
...
- window
...
- should
...
- look
...
- like
...
- this:
- this:
- Define the Transformation Parameter: In order the for parameter in the JSON query to work we need to define the parameter at the transformation level. Choose 'Edit' -> 'Settings...'
...
- from
...
- the
...
- menu
...
- system.
...
- Click
...
- on
...
- the
...
- 'Parameters'
...
- tab.
...
- Enter
...
- 'page'
...
- for
...
- the
...
- parameter
...
- and
...
- '—firstpage—'
...
- for
...
- the
...
- default
...
- value.
- Preview the Data: With the 'Json Input' step selected click on the Preview toolbar button (the green arrow with the magnifying glass
) or right-click on the step and choose 'Preview'. The 'Transformation debug dialog' will open. Click on 'Quick Launch'. You will should see the data returned by the MongoDB query with the extracted fields.
- Change the parameter value: Now we will preview the step with a different parameter. Open the 'Transformation debug dialog' window again. Click on 'Configure', the 'Execute a transformation' window will open. In the parameters section enter '/about' for the value of the page parameter.
Click on the 'Launch' button.
The data displayed is now for the '/about' url, . Try parameter values of '/demo' or '/events' to see how the data changes. - Save the Transformation: Choose 'File' -> 'Save as...' from the menu system. Save the transformation as 'page_successions' into a folder of your choice.
Start Pentaho Report Designer
When PRD starts click on the 'Report Wizard' button or choose 'File' -> 'Report Wizard...' from the menu.
- Select a Template: On the 'Look and Feel' stage of the wizard select a report template and click on 'Next'
- Add a Data Source: On the 'Data Source' stage click on the '+' icon in the top right to add a new data source.
- Choose the Data Source Type: From the 'Choose Type' list click on 'Pentaho Data Integration'.
- Add a Query: In the 'Pentaho Data Integration Data Source' window click on the '+' icon to add a new query. A default query, called 'Query 1' is added. Change the name of the query to 'Top Landing Pages'. Then click on the 'Browse' button and select the 'page_successions.ktr' file created above. Finally select the 'Json Input' step.
If you want you can click the 'Preview' button to see the data generated by the 'Json Input' step. Click on 'OK' to close the 'Pentaho Data Integration Data Source' window. - Select the Query: In the 'Report Design Wizard' click on the 'Top Landing Pages' query to select it and then click on the 'Next' button.
- Layout the Fields: In the 'Layout Step' of the wizard click on 'URL' and then click the button to add 'URL' to the 'Group Items By' box. Add 'NextURL' and 'Count' to the 'Selected Items' box. This will position these two fields as two columns in the report.
Click on the 'Next' button. - Format the Fields: In this step you can change the formatting of the fields. Click on the 'URL' field to highlight it, then change the 'Group Header Label' to 'Source Page: '. Click on the 'NextURL' field to highlight it, then change the Display Name to 'Destination Page'. Click on 'Count' to highlight it, then change the data format to '#,###;(#,###)'
...
- and
...
- select
...
- 'Sum'
...
- from
...
- the
...
- 'Aggregation'
...
- list.
- Finish the Wizard: Click on 'Finish'.
...
- The
...
- wizard
...
- will
...
- close
...
- and
...
- you
...
- will
...
- see
...
- your
...
- report
...
- in
...
- design
...
- mode.
- Change the Titles: Double-click
...
- on
...
- the
...
- report
...
- title
...
- and
...
- change
...
- it
...
- to
...
- 'Pages
...
- Successions'.
...
- Double-click
...
- on
...
- the
...
- first
...
- subtitle
...
- and
...
- change
...
- it
...
- to
...
- 'In
...
- descending
...
- order'.
...
- Double-click
...
- on
...
- the
...
- second
...
- subtitle
...
- and
...
- remove
...
- the
...
- text.
...
- Notice
...
- that
...
- there
...
- are
...
- lot
...
- of
...
- style
...
- properties
...
- you
...
- can
...
- set
...
- for
...
- these
...
- report
...
- elements.
...
- Add
...
- a
...
- Parameter
...
- Data
...
- Source
...
- :
...
- Next,
...
- we
...
- need
...
- to
...
- create
...
- a
...
- data
...
- source
...
- that
...
- will
...
- be
...
- used
...
- to
...
- populate
...
- a
...
- UI
...
- control
...
- so
...
- that
...
- users
...
- can
...
- select
...
- a
...
- valid
...
- value
...
- for
...
- the
...
- report's
...
- parameter.
...
- On
...
- the
...
- right
...
- panel
...
- click
...
- on
...
- the
...
- 'Data'
...
- tab.
...
- Click
...
- on
...
- the
...
- 'Add
...
- Datasources'
...
- toolbar
...
- button
...
- and
...
- choose
...
- the
...
- 'XML'
...
- option.
- Define the Parameter XML Data Source: In the 'XML Data Source Editor' window click on the 'Browse' button and select the 'pages.xml' file. Click on the green '+' button to add a new Available Query. For the 'Query Name' enter 'PageList'. For the 'Query' enter '/pages/page'.
...
Click on the 'Preview...'
...
- button
...
- and
...
- you
...
- will
...
- see
...
- the
...
- values
...
- read
...
- from
...
- the
...
...
- '
...
- file.
...
- Add
...
- a
...
- Parameter
...
- :
...
- Next
...
- we
...
- will
...
- create
...
- a
...
- report
...
- parameter
...
- and
...
- UI
...
- control.
...
- On
...
- the
...
- data
...
- tab
...
- click
...
- on
...
- the
...
- 'Add
...
- a
...
- new
...
- Master-report
...
- Parameter'
...
- toolbar
...
- button
...
-
. The 'Add Parameter...'
...
- window
...
- opens.
...
- Double-click
...
- on
...
- the
...
- 'XML'
...
- DataSource
...
- folder
...
- to
...
- open
...
- it,
...
- then
...
- click
...
- on
...
- the
...
- 'PageList'.
...
- Fill
...
- in
...
- these
...
- fields:
...
- Name:
...
- 'Page'
...
- Label:
...
- 'Page'
...
- Value
...
- Type:
...
- 'String'
...
- Display
...
- Type:
...
- 'Drop
...
- Down'
...
- Query:
...
- 'PageList'
...
- Value
...
- and
...
- Display
...
- Name:
...
- 'url'
...
- Connect the Parameter: Finally we need to associate the report parameter with the transformation parameter. On the right panel, double-click on the 'Page Successions' data source to edit it. Click on the 'Edit Parameter' button, the 'Transformation Parameters' window will open. Click on the green + button to add a row to the table. For 'DataRow Column' choose 'Page' from the dropdown list. For 'Transformation Parameter' choose 'page' from the dropdown list.
Click on the 'OK' button to close the 'Transformation Parameters' window and then click on the 'OK' button in the 'Pentaho Data Integration Data Source' window. - Preview the Report: Click on the preview icon (the eye towards the top left) to preview your report. Notice that there is a dropdown list above the report. You can use this control to change the parameter for the report. You can alter the size of the parameter panel using the grab bar between it.
- Run or Export the Report: Click on the run button (green arrow on the toolbar) or choose 'File' -> 'Export' from the menu system. Select the output format for your report. If you used the run button the report will run and the appropriate application will be opened to view the report. If you chose the export option you will be prompted for the location and name of the file that will be exported. With either method you will be given the opportunity to select the parameter value before the report runs.
Check The Results
- Using the run or export options you will be able to create and view PDF, Excel, HTML, and other file types. Try these options and check that the exported files contain the expected data.
Summary
During this guide you learned how to read data from a MongoDB collection and use it as the data source for a parameterized report.
Other guides in this series cover to sort and group MongoDB data, and combine data from MongoDB with data from other sources.
| Wiki Markup |
|---|
{scrollbar} |