Tuesday 9 September 2014

Scheduled email reports in SpagoBI 4.2



A common requirement when creating reports is that as well as having them run "on demand" that they be scheduled to be sent automatically via email.

SpagoBI allows this functionality, here is how I did it on SpagoBI 4.2.

Step 1: Configuring the email settings.

Click on the "Resources" icon and select the "Configuration management" option.



This will take you to the configuration page.  As there are a lot of options here, we'll change things to only see the ones relating to email.  Change the "Select Category" pull-down to "MAIL".



You'll now need point SpagoBI to an external mail system that will send the emails on your behalf.  The following settings are the ones that you'll need to look at for scheduling (you may want to duplicate this for the User and KPI email settings too).



In my case I only needed to set the smtphost, smtpport and from settings to point at my company's proxy server and provide a dummy return email address.

Step 2: Scheduling a report

The reports scheduling module is hidden away under the "Repository Management" section.





Select the "Scheduler" option.

From here you can add new scheduled reports or view / amend existing ones.

To add a new report click on the "Add" button



Now you need to name the schedule item. 



Click on the "+" button to add a report (multiple reports can be added to a single scheduled item).

Browse to the document(s) that you'd like to send by email, and make sure they are selected, then click the "Save" icon.



 Now add the parameter values for the report, and click "Save" once you have finished.



You will be returned to the main schedule screen, with your report listed in the left-hand column.  Select your report, then click on the "+" sign in the "Schedulations" section.




You can now specify the time that the report will be scheduled for.



Select the start date / time, end date / time (if required) and the report frequency.

Now we need to specify that we are sending the report via email.

 

Select the "Send mail" option.  This will display the email options.



Fill in the details to match your requirements (at the very least the "Mail to" and "Mail Subject" fields), before clicking the "Save" icon.

You can test your scheduled item by clicking its "Execute now" button.


Step 3: Charts, graphics and PDFs

The problem with this is that by default the report will be attached as a .HTML document, and any graphics or charts that may be in your report will be missing (HTML being the default format used in SpagoBI).

To change it, you'll need to add an extra parameter onto your SpagoBI document, with the "URL Name" set to outputType.



You will also need to create an "Analytical driver" for this.

 

You will also need a LOV with the return value of "html."



No, that isn't a typing error.  If you set the LOV to return "pdf" then it will return as a PDF even when trying to view it via the web site.  We will override this value when we schedule the report.

Now, edit your scheduled item (see Step 2 above).  The "outputType" parameter should now be listed.  Set its value to "pdf."



Save the item, and that's it.  The next time you run that scheduled item it will attach a PDF to the email.  Not only that, but the PDF will also contain any graphics or charts that you included.

8 comments:

Mushtaq. said...

Thanks for the excellent post.

I have been looking for some information regarding report scheduling are that is hard to find.

Would you kindly be able to help me in scheduling a report which takes two parameters (start_date end_date).

mushtaq_h_k@hotmail.com

Anugraha Jain said...

Hi,

We have developed a new gen developer friendly BI framework with some extremely unique features. Would like to give you early access & love to hear your opinion. Please do let me know of how to reach out to you. Would be launching product in 3 weeks from now.

Regards,
Anugraha

Unknown said...

Hi,

i tried the same steps to send a chart report to email. i am getting report attachment in the mail but when i open the report the chart is not displaying and empty report is displaying. as suggested i have fallowed the same steps, even changed to pdf and saved but no result, could you please help me on this.

thank you,
sravan kumar.

Unknown said...

Please let me know the steps to be followed for Version 5.0 and 5.1 as I am not able to configure the mail settings. Please help me here as it is very urgent....

Anonymous said...

I followed the steps properly but cudnot send an email ....after the scheduled time i get a dialogue box saying ...could not send response to client

Anonymous said...

I followed the steps properly but cudnot send an email ....after the scheduled time i get a dialogue box saying ...could not send response to client

siladitya said...

Is anyone ever used the other Save Options on Scheduler like 'Save as snapshot'/'Send to Distribution Lists'/'Send to Java class'. I'm looking for a solution where user should get a alert with a email link of the scheduled report with parameter list else a downloadable pre-executed report (using save as document).

DanO said...

Wow, it looks like some people still find their way to this post.

I stopped using SpagoBI a couple of years ago - mainly as I changed jobs, and our team had already standardised on using Microsoft Reporting Services, so sadly my adventures with SpagoBI have come to an end - for now at least.