Emailing Reports from the Pentaho User Console.

With the recent release of Pentaho’s BI Server 3.5.2 and Tom Barber’s (aka magicaltrout) post I thought I would put together a quick tutorial on how to use a new feature of 3.5.2 which allows end users to email reports directly from the Pentaho User Console (PUC).

The Scenario.

I need to create a public schedule which will run out of business hours. Coincidently after creating the public schedule I’m approached by a Sales Manager who requests that I run the Top N Analysis report which is needed for tomorrow’s weekly meeting. As this particular report takes a while to run I will assign it to public schedule (created earlier) which will run the report out of business hours and email to the Sales Manager before his/hers weekly meeting.

Step 1 – Setup SMTP.

The first step is to make sure you have setup the Pentaho BI Server to talk to your SMTP server correctly. The configuration file for the SMTP server is email_config.xml which is located under the /pentaho-solutions/system/smtp-email/ directory. The contents of the email_config.xml looks like this:

<email-smtp> <!-- The values within <properties> are passed directly to the JavaMail API. For a list of valid properties see http://java.sun.com/products/javamail/javadocs/index.html --> <properties> <!-- This is the address of your SMTP email server for sending email. e.g. smtp.pentaho.org --> <mail.smtp.host></mail.smtp.host> <!-- This is the port of your SMTP email server. Usually this is 25. For GMail this is 587 --> <mail.smtp.port>25</mail.smtp.port> <!-- The transport for accessing the email server. Usually this is smtp. For GMail this is smtps --> <mail.transport.protocol>smtp</mail.transport.protocol> <!-- Usually this is 'false'. For GMail it is 'true' --> <mail.smtp.starttls.enable>false</mail.smtp.starttls.enable> <!-- Set to true if the email server requires the sender to authenticate --> <mail.smtp.auth>true</mail.smtp.auth> <!-- This is true if the email server requires an SSL connection. Usually 'false'. For GMail this is 'true' --> <mail.smtp.ssl>false</mail.smtp.ssl> <!-- Output debug information from the JavaMail API --> <mail.debug>false</mail.debug> <!-- For GMail this is 'false' --> <!--mail.smtp.quitwait>false</mail.smtp.quitwait--> </properties> <!-- The is the address of your POP3 email server for receiving email. e.g. pop.pentaho.org --> <!-- It is currently not used --> <mail.pop3></mail.pop3> <!-- This is the default 'from' address that emails from the Pentaho BI Platform will appear to come from e.g. joe.pentaho@pentaho.org --> <mail.from.default></mail.from.default> <!-- This is the user id used to connect to the email server for sending email It is only required if email-authenticate is set to true This is never sent or shown to anyone --> <mail.userid></mail.userid> <!-- This is the password used to connect to the email server for sending email It is only required if email-authenticate is set to true This is never sent or shown to anyone --> <mail.password></mail.password> </email-smtp>

Below is the email_config.xml settings I used for my GMail account:

<email-smtp> <properties> <!-- This is the address of your SMTP email server for sending email. e.g. smtp.pentaho.org --> <mail.smtp.host>smtp.gmail.com</mail.smtp.host> <!-- This is the port of your SMTP email server. Usually this is 25. For GMail this is 587 --> <mail.smtp.port>587</mail.smtp.port> <!-- The transport for accessing the email server. Usually this is smtp. For GMail this is smtps --> <mail.transport.protocol>smtps</mail.transport.protocol> <!-- Usually this is 'false'. For GMail it is 'true' --> <mail.smtp.starttls.enable>true</mail.smtp.starttls.enable> <!-- Set to true if the email server requires the sender to authenticate --> <mail.smtp.auth>true</mail.smtp.auth> <!-- This is true if the email server requires an SSL connection. Usually 'false'. For GMail this is 'true' --> <mail.smtp.ssl>true</mail.smtp.ssl> <!-- For GMail this is 'false' --> <mail.smtp.quitwait>false</mail.smtp.quitwait> </properties> <!-- The is the address or your POP3 email server for receiving email. e.g. pop.pentaho.org --> <!-- It is currently not used --> <mail.pop3></mail.pop3> <!-- This is the default 'from' address that emails from the Pentaho BI Platform will appear to come from e.g. joe.pentaho@pentaho.org --> <mail.from.default>prashant.g.raju@gmail.com</mail.from.default> <!-- This is the user id used to connect to the email server for sending email It is only required if email-authenticate is set to true This is never sent or shown to anyone --> <mail.userid>prashant.g.raju@gmail.com</mail.userid> <!-- This is the password used to connect to the email server for sending email It is only required if email-authenticate is set to true This is never sent or shown to anyone --> <mail.password><!-- passoword --></mail.password> </email-smtp>

You can test if you have configured Pentaho BI Server to talk to the SMTP server correctly by running theBurst Sales Report which is located under the BI Developer Examples/Reporting/ solution folder.
Click on the report and fill out the prompts including your email address:

After clicking the Run button you should receive an email for each employee and the report as a PDF attachment.

Step 2 – Create a Public Schedule.

The next step is to create a Public Schedule. To do this you must have access to the Pentaho Administration Console (PAC), after logging into PAC click on the Administration menu item and then the Scheduler tab:

To create a new schedule click on the  icon (Create schedule), this will display a Schedule Creatorwindow with the Schedule tab active:

• Public Schedule – Allow users access to the schedule : Check this option to make this schedule public
• Name : Name of your schedule i.e. Ad hoc Reports
• Group : Group which your schedule belongs to i.e. Ad hocs
• Description : Description of your schedule i.e. Use this schedule to run lengthy ad hoc reports out of business hours
• Recurrence : How often do you want the schedule to run i.e. Daily
  • Recurrence pattern : On which days you want the schedule to run (this will be different for Monthly, Yearly etc. recurrences) i.e. On weekdays
  • Range of recurrence : The date the schedule is to start and end i.e. The schedule is start on the 2nd of March 2010 till no end date

After filling out the options the Schedule Creator window now looks like this:

You can also assign files directly to this schedule by clicking on the Selected Files tab:

---

---

---

Within the Selected Files tab you are able to assign reports (and xactions) to the schedule, in this example I will not be assigning any reports to this schedule. If you want to add reports click the  icon (Add item) and use the file browser to locate your report:

When you have found your report click on the Select button to add it to the schedule.

After completing all the options available on the Schedule tab click the OK button. You should now see theAd hoc reports schedule in the schedule list:

Step 3 – Setup and Email the Report.

The next step is to assign the report to the schedule, setup the parameters (including the email address) and schedule the report all from the PUC. Log into the PUC with a user who has access to the report (Top N Analysis) and can schedule reports – in this example I’m using the user Joe. The report that needs to be sent out is Top N Analysis which is located under the /Steel Wheels/Reporting/ solutions folder. Right click on the report under the Files section and select the Share option:

Within the Properties window click the Advanced tab and check the Use Public Schedules option and move the Ad hoc Reports option under the Available: list to the Current: list using the > arrow and click the OKbutton:

Right click on the report again and select the Schedule… option:

After clicking the Schedule… option the Top N Analysis report will open up in a new tab:

The Report Parameters section is where you define the default parameters for this report. The Sales Manager has requested that the report parameters are:

Before entering this parameters make sure you uncheck the Auto Submit option

• Product Line : Classic Cars
• Region : APAC
• Year : 2003
• Top N Customers : 10
• Output Type : PDF

The Schedule Report section is the new improvement with Pentaho’s BI Server 3.5.2:

• Report Name : The title of the report this is also displayed in the Email subject line i.e. Top 10 Classic Car Customers for APAC
• E-mail To : The report recipient’s email addresses i.e. prashant.g.raju@gmail.com. If you would like to specify more than one address seperate the emails with a , (comma) i.e. prashant.g.raju@gmail.com,prashant@analysethis.com.au
• Subscription : Which public subscription this report belongs to i.e. Ad hoc

After filling out all the options click the View Report button to preview the report before you schedule it:

Once you are happy with the report preview click the Schedule button. If successful you should receive the following message:

Step 4 – Check the Report Schedule.

---

---

---

You can check the status of the report by clicking on the  icon (Workspace) on the main toolbar. You are able to see the status of the Ad hoc public schedule under the All Schedules (Admin Only) section and the status of the Top 10 Classic Car Customers for APAC under the Public Schedules section:

Step 5 – The Email.

When the sales manager receives the email it will look similar to this:

Step 6 – Delete the Scheduled Report.

After the report has successfully run and has been emailed to the Sales Manager you will need to remove it from the Ad hoc schedule (if you don’t the Sales Manager will keep receiving this email on weekdays out of business hours). Click on the  icon (Workspace) on the main toolbar and click on the Delete link next the report under the Public Schedules section:

Final Notes.

I’m not sure that the example I provided is a viable solution as in most cases the users can log in and run the report themselves. I personally see this functionality beneficial when running large reports which need to be run and sent out of business hours i.e. extracts.

Enjoy.