Versions Compared

Old Version 4

changes.mady.by.user Former user

Saved on

compared with

New Version Current

changes.mady.by.user Former user

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.
Comment: Migration of unmigrated content due to installation of a new plugin

...

Excerpt

May 17, 2006
Submitted by Doug Moran, Pentaho Team Developer

The article this week addresses a common task that community users are asking about over and over - how do you specify parameters to a report, and pass a list of valid values for the user to select the parameter from? While there are several ways to do this, we are going to step through one example using the Pentaho Design Studio. The Design Studio eliminates the frustration of writing XML, and should make the parameter passing a bit easier to understand and implement.

For the sake of brevity, I will modify one of the existing samples from the Pentaho pre-configured installation's (PCI) sample solution. Using the JFreeReport HTML sample from the PCI, I will build a page that will prompt the user for which region they would like to report on, give the user a combobox filled with regions to select from, then display the report with the data filtered by the selected region. In this exercise, we demonstrate

  1. how to set up an input parameter ,
  2. how to use a default in-memory result-set to provide the list of values that are valid for the input parameter,
  3. how to map parameter options to "nice " counterparts for display,
  4. how to modify an existing report to accept the input parameter as part of the report query,
  5. and how to use a secured filter component to prompt the user for parameter value selection.

Resources Before You Get Started

Once again, be sure you have the following resources. I've listed the version numbers I used; perhaps our community members could post any successes or problems should you try this with other versions or configurations.

Step-By-Step: How to Build Your Solution

  1. The first step in this exercise is to get the Design Studio installed and running on your computer. Refer to the Design Studio User's Guide to accomplish this step.
  2. You should have the Pentaho sample solution available on your computer. The sample solution comes bundled in the Pentaho Pre-Configured Install. If you haven't yet, unzip the PCI to a location on your computer that you will remember.
  3. The common way to work on Pentaho solutions is to set up your solution files as an Eclipse Simple Project.  Since we are using the samples as our solution for this exercise, I will create an Eclipse Simple Project called "pentaho-samples", and point to the <pentaho-demo>/pentaho-solutions directory as my  project directory.
    Image Added
  4. Once you have your Eclipse project set up, open the file <pentaho-samples>/samples/reporting/JFree_Quad.xaction.

...

  1. This

...

  1. action

...

  1. sequence

...

  1. already

...

  1. builds

...

  1. a

...

  1. JFreeReport.

...

  1. The

...

  1. document

...

  1. will

...

  1. open

...

  1. to

...

  1. the

...

  1. first

...

  1. tab

...

  1. in

...

  1. the

...

  1. Action

...

  1. Sequence

...

  1. Editor,

...

  1. the

...

  1. General

...

  1. tab.

...

  1. NOTE:

...

  1. If

...

  1. you

...

  1. don't

...

  1. want

...

  1. to

...

  1. modify

...

  1. the

...

  1. sample

...

  1. directly,

...

  1. do

...

  1. a

...

  1. Save

...

  1. As...,

...

  1. and

...

  1. name

...

  1. the

...

  1. new

...

  1. file

...

  1. Test_Quad.xaction.

...

  1. The

...

  1. new

...

  1. sample

...

  1. will

...

  1. show

...

  1. up

...

  1. in

...

  1. the

...

  1. PCI

...

  1. as

...

  1. a

...

  1. duplicate

...

  1. of

...

  1. the

...

  1. JFree_Quad

...

  1. sample,

...

  1. but

...

  1. will

...

  1. of

...

  1. course

...

  1. contain

...

  1. your

...

  1. new

...

  1. solution.

...

  2. Switch

...

  1. to

...

  1. the

...

  1. "Step

...

  1. 2.

...

  1. Define

...

  1. Process"

...

  1. tab

...

  1. in

...

  1. the

...

  1. Action

...

  1. Sequence

...

  1. Editor.

...

  1. Here

...

  1. is

...

  1. where

...

  1. the

...

  1. bulk

...

  1. of

...

  1. our

...

  1. work

...

  1. will

...

  1. be

...

  1. done.

...


  1. Image Added
Defining the Inputs

At the top of this tab, you should see a Process Inputs box. For our solution we need two process inputs. One is the input that will hold the list of Region values that we want to define. We will call this input REGION_SELECTIONS. We also need an input that will hold the selected Region. We will call this input REGION. 
 

  1. Right-click on the folder named Inputs in the Process Inputs box. Select Add... | Input from the menu.
  2. In the new editor page, type REGION in the textbox labeled "Name".  Leave the Type defaulted to string.
  3. Check the box labeled "Has Default Value". Set the default value to Central by typing Central in the text box labeled "Default Value".
  4. Add another input following the same steps, naming it "REGION_SELECTIONS".
  5. This time, change the value in the Type combobox to "result-set" by scrolling down in the list of options.
  6. Once again, select the "Has Default Value" checkbox. You should notice that the dialog has changed for setting the default value. You should see a two column table, columns named key0 and key1.
  7. Change the name of the columns to Name and Value, in that order. Select the first row in the table, right-click and select the "Manage Columns" option. You will be prompted with a dialog populated with the column names. Rename key0 to Name, and key1 to Value.
    Image Added
  8. Now we can enter the list of values that are valid for our REGION parameter. The values we enter in the name column are the descriptions we want to display in the prompt page. The values we enter in the Values column are the corresponding values that are to be passed to the report query. This is very useful when you have, for example, ugly codes that describe your products that you must pass to your query, but you want the descriptive names to be displayed to your users for product selection. Enter the following values in the table:
    Image Added
  9. Save your action sequence now.
Fleshing Out the Process

Now that we have defined our inputs, we need to perform two more tasks: 1.) associate the REGION input with our report query, and 2.) provide the mechanism for displaying the REGION_SELECTIONS values to our user.

  1. Click on the "report" entry in the Process-Actions box. You should see the editor page populated with  the report definition.
  2. Locate the report query, halfway down the page. Add the following where clause in the middle of the existing query:
    Code Block
    
WHERE QUADRANT_ACTUALS.REGION='{REGION}'

...

  1.  Image Added
  2. Notice that the {REGION} 
...
  1.  string 
...
  1.  shows 
...
  1.  up 
...
  1.  as 
...
  1.  a 
...
  1.  Report 
...
  1.  Parameter, 
...
  1.  just 
...
  1.  above 
...
  1.  the 
...
  1.  query. 
...
  1.  This 
...
  1.  is 
...
  1.  called 
...
  1.  a 
...
  1.  replaceable 
...
  1.  parameter, 
...
  1.  and 
...
  1.  in 
...
  1.  one 
...
  1.  more 
...
  1.  step 
...
  1.  we 
...
  1.  will 
...
  1.  associate 
...
  1.  the 
...
  1.  value 
...
  1.  selected 
...
  1.  from 
...
  1.  the 
...
  1.  REGION_SELECTIONS 
...
  1.  with 
...
  1.  this 
...
  1.  replaceable 
...
  1.  parameter.
...
  2. Right 
...
  1.  click 
...
  1.  in 
...
  1.  the 
...
  1.  Process 
...
  1.  Actions 
...
  1.  box. 
...
  1.  Click 
...
  1.  on 
...
  1.  the 
...
  1.  Add... 
...
  1.  menu 
...
  1.  option, 
...
  1.  then 
...
  1.  select 
...
  1.  the 
...
  1.  Secure 
...
  1.  Filter. 
...
  1.  This 
...
  1.  will 
...
  1.  add 
...
  1.  a 
...
  1.  secure 
...
  1.  filter 
...
  1.  component 
...
  1.  to 
...
  1.  your 
...
  1.  action 
...
  1.  sequence. 
...
  1.  The 
...
  1.  secure 
...
  1.  filter 
...
  1.  component 
...
  1.  allows 
...
  1.  you 
...
  1.  to 
...
  1.  filter 
...
  1.  data 
...
  1.  by 
...
  1.  a 
...
  1.  selected 
...
  1.  value, 
...
  1.  and 
...
  1.  protect 
...
  1.  your 
...
  1.  queries 
...
  1.  by 
...
  1.  not 
...
  1.  allowing 
...
  1.  the 
...
  1.  URL 
...
  1.  back 
...
  1.  to 
...
  1.  the 
...
  1.  server 
...
  1.  to 
...
  1.  execute 
...
  1.  with 
...
  1.  any 
...
  1.  other 
...
  1.  values 
...
  1.  than 
...
  1.  those 
...
  1.  provided 
...
  1.  as 
...
  1.  options 
...
  1.  via 
...
  1.  the 
...
  1.  component.
...
  2. Drag 
...
  1.  the 
...
  1.  secure 
...
  1.  filter 
...
  1.  component 
...
  1.  above 
...
  1.  the 
...
  1.  report 
...
  1.  entry 
...
  1.  in 
...
  1.  the 
...
  1.  Process 
...
  1.  Actions 
...
  1.  box.
...
  2. Select 
...
  1.  the 
...
  1.  Secure 
...
  1.  Filter 
...
  1.  in 
...
  1.  the 
...
  1.  Process 
...
  1.  Actions 
...
  1.  box, 
...
  1.  so 
...
  1.  that 
...
  1.  the 
...
  1.  editor 
...
  1.  page 
...
  1.  displays 
...
  1.  the 
...
  1.  options 
...
  1.  for 
...
  1.  the 
...
  1.  Secure 
...
  1.  Filter.
...
  2. Drag 
...
  1.  the 
...
  1.  REGION 
...
  1.  Input 
...
  1.  parameter 
...
  1.  FROM 
...
  1.  the 
...
  1.  Process 
...
  1.  Inputs 
...
  1.  box 
...
  1.  TO 
...
  1.  the 
...
  1.  Selections 
...
  1.  box 
...
  1.  of 
...
  1.  the 
...
  1.  Secure 
...
  1.  Filter 
...
  1.  page.
...
  2. Next, 
...
  1.  select 
...
  1.  REGION 
...
  1.  in 
...
  1.  the 
...
  1.  Selections 
...
  1.  box.
...
  2. A 
...
  1.  Title 
...
  1.  and 
...
  1.  Filter 
...
  1.  boxes 
...
  1.  appear. 
...
  1.  In 
...
  1.  the 
...
  1.  Title 
...
  1.  textbox, 
...
  1.  add 
...
  1.  the 
...
  1.  prompt 
...
  1.  for 
...
  1.  the 
...
  1.  REGION 
...
  1.  parameter 
...
  1.  (ie., 
...
  1.  What 
...
  1.  region 
...
  1.  would 
...
  1.  you 
...
  1.  like 
...
  1.  to 
...
  1.  see 
...
  1.  data 
...
  1.  for?).
...
  2. In 
...
  1.  the 
...
  1.  Filter 
...
  1.  box, 
...
  1.  select 
...
  1.  <REGION_SELECTIONS>. 
...
  1.  You 
...
  1.  should 
...
  1.  note 
...
  1.  that 
...
  1.  some 
...
  1.  new 
...
  1.  options 
...
  1.  are 
...
  1.  now 
...
  1.  available 
...
  1.  to 
...
  1.  you, 
...
  1.  named 
...
  1.  Filter 
...
  1.  Details.
...
  2. In Filter Details, 
...
  1.  change 
...
  1.  the 
...
  1.  Selection 
...
  1.  Style 
...
  1.  to 
...
  1.  "pulldown".
...
  2. Type 
...
  1.  "Value" 
...
  1.  in 
...
  1.  the 
...
  1.  text 
...
  1.  box 
...
  1.  labeled 
...
  1.  "Value 
...
  1.  Column", 
...
  1.  and 
...
  1.  type 
...
  1.  "Name" 
...
  1.  in 
...
  1.  the 
...
  1.  text 
...
  1.  box 
...
  1.  labeled 
...
  1.  "Name 
...
  1.  Column". 
...
  1.  Remember 
...
  1.  these 
...
  1.  are 
...
  1.  the 
...
  1.  names 
...
  1.  we 
...
  1.  gave 
...
  1.  our 
...
  1.  columns 
...
  1.  when 
...
  1.  we 
...
  1.  were 
...
  1.  describing 
...
  1.  our 
...
  1.  table 
...
  1.  of 
...
  1.  values 
...
  1.  in 
...
  1.  the 
...
  1.  REGION_SELECTIONS 
...
  1.  input 
...
  1. parameter.
     Image Added
  2. Save your action sequence now. 
Step-By-Step: How to Test Your Solution
Here is where the Pentaho Pre-configured Install comes in. Follow the steps given to test out your new solution in the server.
  1. First, make sure your PCI is up and running. If you don't know how to get it started, or are unsure as to whether it's already running, see the Pentaho Getting Started Guide for how to successfully start the server.
  2. Next make sure that the solution folders you are using in the PCI contains your modified JFree_Quad.xaction!! 
  3. Switch to the Test tab of the Action Sequence Editor. Verify that the "Server URL" at the top of the page is indeed the URL to your PCI by clicking the "Test Server" button. You will see see the home page of the PCI if all is operating properly.
  4. You should see the URL for the action sequence we want to test in the textbox labeled "Generated URL". Just after that text box is a "Run" button.
  5. Click the "Run" button to test your action sequence. You should see a prompt page asking for the region. When you select submit, you will recieve your report filtered down to the region you selected.
     Image Added
Should you encounter any problems or if you just want to peruse the solution, you can find the xml for this exercise here.