Analyse This

After the new release of Pentaho’s Report Designer 3.5 (PRD) I thought I would get stuck into trying out some of the new charting features, of which Survey Scales (and of course Sparklines) interested me the most. Survey Scales are a way of graphically representing values on a predefined scale i.e. survey responses.

Below is a tutorial which I have put together on how to implement Survey Scales within a report using Pentaho’s Report Designer 3.5.

Sample Data

I have decided to create my own data set for this example which is survey responses from athletes. If you would like you can download the Excel workbook and import it into your database of choice.

Here is a quick preview of what the sample data looks like:

After importing the data into your database start PRD.

Create a New Connection

Once PRD has started create a new connection to the database which contains the sample data. In this example I have called my connection SurveyResults.

Make sure to test the new connection before we start building the query.

Build the Query

You will need to build a query to retrieve the survey results, in this example I have called the query GetAllSurveyResults.

Make sure to preview the query before we start building our report.

After clicking OK you should now see a JDBC (SurveyResults) connection, GetAllSurveyResults query and fields under the Data tab.

Report Structure

Here is how I will be structuring my report:

• Page Header
  • Athlete Name (Group)
    • Question Title and Description (Group)
      • Answer (Survey Scale Chart)
    • End of Question Title and Description (Group)
  • End of Athlete Name (Group)
• Page Footer

As you can see I’m creating a report which will group our survey results by Athlete Name and then by Question Titles and Descriptions to which the Athletes provided Answers to.

Page Header and Footer

By default I have a template which already has a default Page Header and Footer which I have modified for this tutorial.

Creating the Athlete Name group

After you have created your Page Header and Footer the next step is to create the first Group based on the Athlete Name. Right click on the first Groups option under the Structure tab and select the Edit Group option.

This will open up an Add Group dialog box, click on the ATHLETE_NAME field and then the arrow pointing to the right to add it to the Selected Fields list and click OK.

Now you need to add the ATHLETE_NAME field into the report. Make sure the Structure tab is active and expand the Groups option and select the Group Header.

To make the Group Header visible on the report canvass click on the Attributes tab and uncheck the hide-on-canvass check box.

Now you should be able to see the Group Header on the report canvass.

At the same time under the Structure tab, select the Style tab and scroll down to the page-behavior section and check the pagebreak-after option. You have now made the report create a page break after each ATHLETE_NAME group.

The next step is to drag and drop the ATHLETE_NAME field from the Data tab onto the Group Header section on the report canvass.

Creating the Question Title and Description group

To create the Question Title and Description group right click on the Groups section under the Structure tab and click on the Add Group option.

This option will open up an Add Group window. Click on the QUESTION_TITLE and QUESTION_DESCRIPTION fields and then the > button to move these fields to the selected fields list and click OK.

After clicking OK the Group Header, Details and Footer for the Question Title and Description are automatically shown on the report canvass. Drag and drop the QUESTION_TITLE, QUESTION_DESC fields from the Data tab onto the Group Header section (located under the Group Header section for the Athlete Names).

Now you are ready to add in the Survey Scale chart – before you continue make sure that your report is grouping correctly by viewing the report in the Print Preview mode.

Adding in the Survey Scale chart

For your information here is a quick overview of what elements make up a Survey Scale chart.

On the left are the elements which make up a Survey Scale chart (the labels are options which you can set up to be either a set value or an expression under the Attributes tab). On the right are the values I have put in for this tutorial, ANSWERS is a field from our GetAllSurveyResults query. In my data set:

• The lowest value can be a 0
• The highest value can be a 10
• The lower-bound value (the lowest answer Athletes should be answering) is 7
• The highest-bound value (the highest answer Athletes should be answering is 10)

You will need to drag and drop the (survey-scale) chart object onto the Details section (which sits between the Question Title and Description Group Header and Footer).

Double click on the chart object so that a drop down arrow appears and from the available list select the ANSWERS value.

To finish off click on the survey scale chart object then under the Attributes tab find the survey-scale section. As you can see I have static values but you can also create dynamic values as an expression.

To see if you have successfully created a report using Survey Scale charts view the report in the Print Preview mode.

Downloads

Below are links to download the Sample Data, Pentaho Report file or view the tutorial report generated as a PDF.

Enjoy.