Skip to main content
Pentaho Documentation

Create ETL Logging Reports

  1. Open a new report in Report Designer.
  2. Create two parameters named Start Date and End Date, both with a Value Type of Date and a Prompt Display Type of Date Picker.
  3. Create a parameter named Number of Rows with a Value Type of Integer and a default value of 50.
  4. Create a table data set named PeriodSelection with an ID column of type Integer and a Value column of type String. Enter these ID/Value pairs into the table.
    • 1, "24 Hours"
    • 7, "7 Days"
    • 30, "30 Days"
  5. Create a parameter named Period Selection with the these settings.
    • Value Type = Integer
    • Prompt Display Type = Single Selection Button
    • Query = PeriodSelection
    • Prompt Value = ID
    • Prompt Display Name = Value
    Check the Validate Values and Use first value if default value formula results in NA boxes.
  6. Create a new metadata data set. In the Metadata Data Source editor under XMI file, point to the metadata file in the solutions folder under the BA Server at biserver-ee/pentaho-solutions/PDI Operations Mart Sample Reports/[MyBusinessModel.xmi].
  7. Create a query against the metadata data set named Status and add the following field to the Selected Columns list: Dim execution > Execution status.
  8. Add a parameter named Status Selection with the these settings.
    • Value Type = String
    • Default Value = [start,end]
    • Prompt Display Type = Multivalue List
    • Query = Status
    • Prompt Value = Execution Status
    • Prompt Display Name = Execution Status
    Check the Validate Values and Use first value if default value formula results in NA boxes.
  9. Create a query against the metadata data set named TypeSelection, add the Dim executor > Executor type field to the Selected Columns list. Add the following condition: Dim executor > Executor type is not null.
  10. Add a parameter named with these settings.
    • Value Type = String
    • Default Value = [job,transformation]
    • Prompt Display Type = Multi Selection Button
    • Query = TypeSelection
    • Prompt Value = Executor type
    • Prompt Display Name = Executor type
    Check the Validate Values and Use first value if default value formula results in NA boxes.
  11. Create a query against the Metadata data set named LastRun and add these fields to the Selected Columns list.
    • Dim executor > Executor name
    • Fact execution > Executor timestamp
    • Dim execution > Execution status
    • Fact execution > Duration
    • Dim executor > Executor type
  12. Add these conditions to the query.
    • Dim execution > Execution status in {Status Selection}, with default value "start|end"
    • Dim executor > Executor type in {Kettle Type}, with default value "transformation"
    • Fact execution > Execution Timestamp >= {Start Date}
    • Fact execution > Execution Timestamp <= {End Date}
  13. Add the following order to the query: Fact execution > Execution timestamp (Descending - DESC).
  14. Click OK twice to exit the Query Editor and the Metadata Data Source Editor.
  15. Drag a Message field from the panel on the left onto the report under Report Header, enter Last Run Jobs and Transformations and format as necessary.
  16. Drag 5 Message fields onto the Group Header band and fill them with the this text.
    • Date/Time of Execution
    • Name of Job or Transformation
    • Type
    • Execution Status
    • Duration (sec)
    Format as necessary.
  17. Drag the these fields onto the Details band and fill them with the corresponding values.
    • Date field: Execution Timestamp
    • String field: Executor Name
    • String field: Executor Type
    • String field: Execution Status
    • Number field: Duration
    Align the field widths to the Group Header message field widths, in order to align the headers with the values.
  18. Review the report, selecting various parameter values to verify the report is working correctly.