Emailing Reports from the Pentaho User Console

Emailing Reports from the Pentaho User Console.

Posted under BI Server, Open Source, Pentaho, Tutorials, 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 the Burst 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 Creator window 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 the Ad 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:

PUC 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 OK button:

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

PUC 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:

PUC Scheduled Report Successful

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.

Posted by Prashant Raju on Mar 5, 2010 — 28 Comments — Share

28 Comments for “Emailing Reports from the Pentaho User Console.”

Posted by aTK on Mar 11, 2010

Dont have the field “E-mail To” in the “Schedule Report Section” using the BI 3.5.2.

I dont see the field in your screenshot too. Anything wrong ?
Posted by Prashant Raju on Mar 11, 2010

@aTK,

Good pick up I have updated the screenshots to include the Email To: field (must of got my 3.5 and 3.5.2 mixed up).

You must setup your email_config.xml correctly for the Email To: field to appear, have you done this and tested it with the test report?

Prashant.
Posted by aTK on Mar 11, 2010

Hello Prashant,

I configure normally the email_config.xml and the Step 1 is OK & working.

I follow the other step without any difficulties, again Report Name & Subscription are ok but again i don’t have the field “email to:”.
For now, I dont have found the origin of the problem.

regards,
Posted by Prashant Raju on Mar 11, 2010

@aTK

You are using 3.5.2? Not 3.5?

Prashant.
Posted by aTK on Mar 11, 2010

3.5.2.stable.41050.
Posted by aTK on Mar 11, 2010

Just have found the solution :

in email_config.xml :

but you NEED to enter something, because there is a test on the userid in the file ..\system\custom\xsl\SubscriptionUtil.xsl

Regards,
Posted by aTK on Mar 11, 2010

Sorry, something wrong in my post :

in email_config.xml :

mail.userid :
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
Posted by Antti on Mar 26, 2010

Hi, its working fine!

FYI: If your are willing to send report to many email-addresses with one schedule, separate addresses with Comma (not semicolon like I tried…
Posted by Prashant Raju on Mar 26, 2010

@Antti,

Good point! Thanks for the input!

Prashant.
Posted by JoeDoe on Apr 8, 2010

Where can I edit the email body. Currently messages have the following body: This message was automatically generated by a subscription from the Pentaho BI server.
Posted by Prashant Raju on Apr 8, 2010

JoeDoe

With this method you can not specify an email body however you are able to specify a body if you set up your report to be emailed (or bursted) through Design Studio.

Prashant
Posted by James on May 22, 2010

Everything is working fine as described but the attached report on the email is always blank/empty. Any ideas?
Posted by Prashant Raju on May 22, 2010

@James

Is the report not empty when you preview it before you schedule it?

Prashant.
Posted by James on May 25, 2010

The report is not empty when I preview before I schedule it. In preview, it works fine, it shows data and the steel wheels logo.
Posted by James on May 25, 2010

Sorry, I must have done something incorrect. It works fine now. Thanks for your tutorial.
Posted by Andrés on May 26, 2010

Hi and thank you for this tutorial, very usefull.

I can do this in my server but I have an issue with the parameters because I need to execute the report daily with the current date as parameter. Do you know how to do this, how to set dinamyc this paramater?

Thanks in advance.
Posted by Prashant Raju on May 26, 2010

Andres,

Using this method you will need to set up a value in the parameters a value which will output the ‘current date’, preview the report with the ‘current date’ selected and schedule/email the report. The ‘current date’ value must be dynamic. Your other solution is to use xactions to deliver the report via email.

Prashant.
Posted by smartel on Jul 8, 2010

Hi Prashant,
Thank you for creating this website, it has helped me so much.

I have followed all of your steps when creating a new schedule in the admin counsel, but unlike you all my reports are showing up as being private.

“-Public Schedule
-Name
-Group
-Description
-Recurrence
-Recurrence pattern
-Range of recurrence”

I am assuming this this is the problem which is preventing me from seeing these schedules as being available in the Pentaho user console.

Thank you for your help. I would greatly appreciate any form of insight on this matter.
Posted by smartel on Jul 17, 2010

I was able to resolve this issue. Thanks.
Posted by ATKmain on Aug 17, 2010

1.Is it possible to schedule PAT Analysis report and send them by email? schedule menu is disable for it ( I sent others by email successfully)
2.Is it possible to send report link only?( instead of attaching them)
Posted by prashant tamanekar on Aug 28, 2010

hi prashant,

Thank you for creating this website, it has helped me so much.

I configured Seeting but i am not able to view Email to option

what i need to configure.

Thanks,
Prashant Tamanekar
Posted by Prashant Raju on Aug 30, 2010

@Prashant

Can you send emails using the sample xaction? If not you have not configured SMTP correctly.

Prashant.
Posted by santenkelapa on Sep 2, 2010

hi Mr.Prashant Raju,
your article always help me .. thank’s very much .. you are my big teacher
Posted by prashant tamanekar on Sep 8, 2010

hi prashant,

in SMTP configuartion is Wronge in Email address i dont put correct email address thats why its not working. I corrected That now its working Properly

Thaks For Your Support and Efforts you taking to Contribute Your Knowlege with us.
Posted by omar on Dec 9, 2010

hi prashant ,

I need a configuration for Lotus notes server SMTP ?
thanks ,
Posted by Asha on Dec 10, 2010

Hi,
Mail to single recipient working fine. Bt Hw to send to multiple recipient?I tried using , and : separator in Email To: Bt it doesnt work..I m new to pentaho reporting..Plz Help

Thanks in ADVANCE..
Posted by Nitesh Jain on Mar 17, 2011

Hi Prashant,

I wanted to know if we can burst a report to 1000′s of user.Using a comma separator for email id’s looks good for fewer email id’s but when it comes to 1000 or more email id’s then it will be cumbersome.If there is any other way to do it then kindly enlighten us..

thanks in advance
nitesh jain
Posted by Anup Cherian on Apr 19, 2011

Hi,

Sorry if I sound ignorant. Currently we are using PRD 3.6. Using the xaction, we are able to schedule and send out emails.

We are looking to upgrade to PRD 3.7. Can a PRPT file work w/ an xaction file? Either way, how can we do similar email scheduling here?

Analyse This