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 subreport object. Now we begin to work on the subreport.

  2. In the Data tab, we go to the node's Parameters, right-click on it, and select the option Edit Sub-report Parameters....

    Here we add an Import Parameter and configure it as follows:

    • Outer Name = customer_id
    • Inner Name = importCustomerId
  3. We create a JDBC type data set with the following characteristics:
    • Connections = sakila db
    • Query Name = LastDateAmount
    • Query =
        SELECT 
  YEAR(payment.payment_date) AS payment_year, MONTH(payment.payment_date) AS payment_month, SUM(payment.amount) AS sum_amount
  FROM payment 
  WHERE payment.customer_id = ${importCustomerId}
  GROUP BY 1, 2
  ORDER BY 1, 2 DESC
  LIMIT 0,3

    Next, we click on OK to continue.

  4. In the Report Header section, we add a bar sparkline and a line sparkline, and establish their sizes and positions so they look like the following:
    Time for action – creating our first sparkline
  5. First we configure the bar sparkline and then the line sparkline. We click on the Bar-sparkline and establish the values it needs in order to draw the bars:
    • Attributes.value = =MULTIVALUEQUERY("LastDateAmount";"sum_amount")

    What the MULTIVALUEQUERY function does in this case is it receives as its first parameter the name of the data set that we are going to work with, and as its second parameter, the name of the field in the data set that we will use. What the MULTIVALUEQUERY function will do is create a string with the values of sum_amount separated by commas. For example, the field sum-amount in the Subreport's data set, for the customer CURTIS, IRBY from the country Canada will return three rows as shown in the following screenshot:

    Time for action – creating our first sparkline

    So, in this case, after applying the MULTIVALUEQUERY function, we get the following:

    Time for action – creating our first sparkline

    Attributes.value receives numerical data separated by commas. The technique suggested in the preceding section can be used for this purpose or some other, for example, using the group_concat function in SQL, or by a PDI transformation, and so on.

    Note

    There is another very interesting function: SINGLEVALUEQUERY. This receives the same parameters as MULTIVALUEQUERY, but returns only the first row.

  6. We configure the rest of the bar sparkline's details:
    • Attributes.spacing = 4
    • Style.lastcolor = #cc9329
  7. Now we edit the line sparkline as follows:
    • Attributes.value = =MULTIVALUEQUERY("LastDateAmount";"sum_amount")
    • Attributes.spacing = 4
    • Style.lastcolor = #cc9329

    The two sparklines we added to our report have the same data, but this way we can see more clearly the way each one represents information.

    If we preview, we will see the following screenshot:

    Time for action – creating our first sparkline

What just happened?

We added the field customer_id to our report's principal data set in order to work with this field later. In the Details section, we added a subreport and configured it to import the value of the field customer_id. We also created a data set for the subreport and added two sparklines to the Report Header section. First we configured the bar sparkline and then the line sparkline. Finally, we explained the MULTIVALUEQUERY function.

Have a go hero

We propose that you make a copy of the report we just created and modify it so it looks like the following:

Have a go hero

What you should do is create a pie sparkline, place it in the Details Section, and configure it so it shows the portion of the pie corresponding to the current Amount.

The steps you should follow, broadly speaking, are as follows:

  1. Create a copy of the report 15_Adding_Hyperlinks_Sparklines.prpt and save it with the name 16_Adding_Hyperlinks_Sparklines_Plus.prpt.
  2. Place a pie sparkline in the Details Section to the right-hand side of the number-field that has the value sum_amount, and configure it as follows.
    • Attributes.value = =[sum_amount]/[TotalAmount]
    • Attributes.low-slice = 0.15
    • Attributes.medium-slice = 0.25
    • Style.low-color = #cf4646
    • Style.high-color = #2ead03

Pop quiz: Hyperlinks and Sparklines

Q1. State whether the following statements are true or false.

  1. Hyperlinks in PRD only let us reference and pass values to the parameters of other PRD reports.
  2. Hyperlinks in PRD let us navigate within our reports in the same way we would in a traditional web page.
  3. To create a hyperlink in PRD, we must use the Pentaho BA Server.
  4. A sparkline in PRD has a very minimalist format, but we can still assign values to the X and Y axes.
  5. In PRD, sparklines let the end user make a rapid analysis of data at the detail level.
  6. The three types of sparklines in PRD receive an array of comma-separated data as their input.
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset