Chapter 12. Making a Difference – Reports with Hyperlinks and Sparklines

In this chapter we will see how to create and configure Hyperlinks. Hyperlinks give us great flexibility in designing our reports, as they let us drill down (see Detail section). Using Hyperlinks, we can create a network of reports and also re-use reports.

Here we will also see a very interesting object that we can add to our reports, which will present specific information graphically: the sparkline.

In this chapter we will do the following:

• Create and configure a Hyperlink on a text-field object

• Create and configure a Hyperlink on each portion of a pie chart

• Create and configure a bar-type sparkline

• Create and configure a line-type sparkline

Finally, we propose that you modify the report we create in this chapter and add a pie-type sparkline.

Starting practice

In this chapter we will copy a report we created earlier and modify it step-by-step so that it ends up looking like the following screenshot:

This report will have the feature that, when we click on the name of a country, a new report will open with more detailed information about that country. We will also configure the pie chart so that when we click on one of its areas, a report will open with more information about the selected item.

In the report detail, we will add two sparklines that will analyze the payment history tendency of each client.

First we will talk about hyperlinks, then about sparklines.

Time for action – configuring the layout

In the following section, we will make a copy of a report created in the previous chapter and configure its layout according to the needs of this chapter.

1. We create a copy of the report we created earlier. Open the report 05_Adding_Groups.prpt and save it with the name 15_Adding_Hyperlinks_Sparklines.prpt.

2. We modify the report so that it looks like the following screenshot:

   

   • In the Report Header section, we assign the following value to the Attributes.value property of the label: Adding Hyperlinks & Sparklines.

   In the Group Header section, we leave a blank space where we can add a Sub-report later.

   • In the Details Header section, we move the labels Customer and Amount to the left-hand side and add a label to show the value Trend.

   In the Details section, we eliminate the horizontal line and move the two objects found here to the left so that they stay below their respective headers. We increase the width of this section, as we will later place a Sub-report...

Learning about hyperlinks

A Hyperlink lets us access an external resource, which could mean opening a website or a report, downloading a file from the Internet, and so on. It also lets us navigate internally; that is, go to specific positions within a web page, report, and so on.

A Hyperlink has two well-differentiated parts:

• The link, which is the element that will contain the Hyperlink. Usually, when we hover over the link, the pointer changes to a hand, and if the link is a text, it will be blue and underlined.

• The target, which is the element the Hyperlink will point to.

In PRD, Hyperlinks work similarly. For example, we can select an object (link) and use it to create a Hyperlink whose target is another report. But it doesn't stop there. PRD lets us send values to that report's Parameters, that is, we can obtain detailed information from the link we click on, and by doing so, simulate the drill down typical of an OLAP analysis.

Up to here, this is all pretty common, but PRD goes a bit further...

Time for action – creating our first hyperlink

Now we will create a Hyperlink that lets us open a PRD report. We will create the Hyperlink on a text-field that contains the name of the country currently under analysis. Clicking on the Hyperlink will open a report that gives us information about that country.

1. We create a Hyperlink on the text-field object that has the value country in the field Attributes.field. We select the object, right-click on it, and in the context menu choose Hyperlink... as shown in the following screenshot:

   

   This will open the Hyperlink's Editor as shown in the following screenshot:

   

2. The purpose of this Hyperlink is to link to a Pentaho BA Server solutions report and assign a value to its Parameter, according to the country the user clicks on.

   To accomplish this, we complete the Editor's formula as follows:

   • Location: = Pentaho Repository.

   • Include server URL in path = True. With this option, the Hyperlink includes in its definition the value of Server URL:.

   • To complete the...

Time for action – hyperlinks in charts

We will add and configure a Sub-report in our report. Then we will add a pie chart to the Sub-report and configure it so that each of its portions has a Hyperlink to a PRD report.

1. In the space that we left blank in the Group Header section, we add a Sub-report.

2. We enlarge the Sub-report to occupy the empty space and then double-click on it to begin configuring its contents.

   We configure its Import Parameter. In the Data tab, we right-click on Parameters and choose the option Edit Sub-report Parameters....

   In the table on the left-hand side (Import Parameter), we click on the button to create a new row and then establish the following:

   • Outer Name = co untry_id

   • Inner Name = importCountryId.

   

   We add a JDBC type Data Set with the following configuration to the Subreport:

   • Connections = sakila db

   • Query Name = RatingAmount

   • Query =

     SELECT
       film.rating,
       SUM(payment.amount) AS sum_amount
     FROM film 
     INNER JOIN inventory ON film.film_id = inventory.film_id
     INNER...

Learning about sparklines

Sparklines are very small graphics that contain very specific information. Sparklines have few characteristics to configure and are very minimalist. They cannot include axes, labels, descriptions, effects, or numbers. To get a better idea of what sparklines are, here are some examples:

As you can see, they do not contribute any information out of context. That is why their principal role is to accompany the data present in the report, and because of their small size, we can include them in the detail level of our reports!

PRD lets us include sparklines in our reports, granting us the possibility of analyzing the variation of an item's value over time, making specific comparisons, showing what percentage of a total an item's value represents, and so on.

If we compare sparklines with the kinds of charts that we have already seen in PRD, we could erroneously conclude that sparklines display very little information to the end user. But as we saw before, this characteristic...

Time for action – creating our first sparkline

We will modify our principal data set to practice each of the following points. We will create a subreport, and within it we will create and configure two sparklines.

1. We modify the SQL query of our data set to conform to the needs of this guide. We add the field customer_id at the end of the list of fields in the SELECT statement. Our query should look like the following:

     SELECT country.country_id, country.country, customer.first_name,customer.last_name, SUM(payment.amount) sum_amount, payment.customer_id
     FROM payment 
     INNER JOIN customer ON customer.customer_id=payment.customer_id
     INNER JOIN address ON address.address_id=customer.address_id
     INNER JOIN city ON city.city_id=address.city_id
     INNER JOIN country ON country.country_id=city.country_id
     WHERE country.country_id IN (20,24,29,34,48,67,74)
     GROUP BY payment.customer_id
     ORDER BY country.country, customer.first_name

   In the Details section, beside the label Trend, we place a...

Summary

We created a copy of the report 05_Adding_Groups.prpt and modified its layout.

We explained what a hyperlink is, how to use it, and what it is used for. We talked about the different types of hyperlinks that can be created in PRD. We detailed the interaction between PRD and the Pentaho BA Server when using hyperlinks.

We created a hyperlink on a text-field that returns the value of the field country. We configured this hyperlink to open the report 13_Adding_Subreports.prpt and passed it the desired country as a parameter.

We explained how and where the information related to Hyperlinks is saved and analyzed the DRILLDOWN function.

We added a subreport to the Report Header Section and configured it and added a pie chart to it. We configured a hyperlink on this pie chart, so that clicking on each portion of the pie chart shows the report 09_Using_Formulas.prpt filtered by the value of the selected portion.

We explained what sparklines are and what they are used for. We explained the characteristics...