Time for action – configuring the layout

Next, we will create a new report, create its data set, and add a crosstab. Then we will configure the crosstab to show us the values of amount analyzed after crossing years-months with ratings.

  1. Create a new report and save it with the name 18_Adding_Crosstabs.prpt.
  2. Create a JDBC data set and configure it as follows:
    • Connections= sakila db
    • Query Name= DateRatingAmount
    • Query= 
      SELECT YEAR(payment.payment_date) AS payment_year, 
MONTH(payment.payment_date) AS payment_month, film.rating, SUM(payment.amount) AS sum_amount
FROM payment
INNER JOIN rental ON rental.rental_id=payment.rental_id
INNER JOIN inventory ON inventory.inventory_id=rental.inventory_id
INNER JOIN film ON film.film_id=inventory.film_id
WHERE film.rating IN ("G","PG","R")
payment_year, payment_month, film_rating
  3. In the Report Header section, add a crosstab object: Time for action – configuring the layout
  4. Make the crosstab Inline, and when asked about the data set to use, choose DataRatingAmount and click OK. After doing this we will see the Crosstabs Editor.
  5. Next, configure the crosstab.
  6. In the Row Axis section, add the fields payment_year and payment_month, in that order. Then modify their Titles Header section to be Year and Month, respectively.
  7. In the Column Axis section, add the field rating, and in Title Header type Ratings.
  8. In the Data Cell section, add the field sum_amount, and choose the Aggregation criteria as sum.
    Time for action – configuring the layout
  9. Next, click OK to continue.
  10. Return to the main report and enlarge the crosstab so it fills the page width.
  11. Go back to the crosstab and modify its final presentation as we please: bold, fonts, formats, and so on.

What just happened?

We created a new report and named it 18_Adding_Crosstabs.prpt. Then we created its JDBC data set and established its SQL query. We added a crosstab to the Report Header section, made it be Inline, and established its data set. Then we configured the sections Row Axis, Column Axis, and Data Cell for the crosstab.

Have a go hero

In this chapter we propose that you hide a graphic if the user executing the report is not Admin.

The steps you should follow, broadly speaking, are:

  1. Create a copy of the report 11_Adding_Graphics.prpt and save it with the name 19_Adding_Style_Sheets_Plus.prpt.
  2. Select the second graphic and configure its Style.visible with a formula that will return True if the user currently logged in is Admin, and return False otherwise.

Pop Quiz – environment variables, stylesheets, and crosstabs

Which of the following statements are true?:

Q1. PRD's environment variables let us know some information about the BA Server where the report is being executed.

Q2. In order for PRD to include the values of the environment variables, they must be previously enabled.

Q3. PRD uses CSS3 to implement stylesheets.

Q4. We can manually create our .prptstyle file and then include it using PRD.

Q5. In stylesheets, we can create CSS Rules only if we know the class or ID of the object in question.

Q6. Crosstabs in PRD function dynamically when executed in Pentaho BA Server.

..................Content has been hidden....................

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