Friday, November 19, 2010

SCOM: Deliver Reports to Sharepoint

Today I focussed on delivering SCOM reports to a Sharepoint document library with the File Share and e-mail delivery methods.

Most teams and departments have used file shares and network mappings to store their documents. These days more and more companies are using portal products for that purpose, like Microsoft Sharepoint.

In this blog I'll show you how you can deliver a report to a Sharepoint 2007 library. Although this is not difficult to setup, there are some things you need to know to get it working. In this setup SQL SRS 2005 and Sharepoint 2007 are used.

Note: SQL Reporting Services 2008 supports publishing reports directly in Sharepoint libraries, but SCOM 2007 does not support that. Atleast as far as I know. :)

Operations Manager supports two kinds of delivery, file and e-mail.
Both flavours can work, but it fully depends on how your Sharepoint implementation is setup.
There's a third but that's the Null Delivery Provider to cache reports.


E-mail delivery
For Operations Manager Reporting to deliver e-mail to a library the following has to be in place and configured.

Configure SQL Reporting Services

  • Use the Reporting Services Configuration Manager
  • Make sure the the Windows Service identity is configured with a domain user account and not with a local account (Data Warehouse Reader account).
  • Configure the E-mail Setttings
  • Make sure your SMTP server allows you to send e-mail

Configure you Sharepoint document library

  • Make sure you have administrative rights on the document library
  • Configure incoming e-mail settings on the library in which you want to deliver your reports
  • Unfortunately you cannot change the name of the file SQL Reporting Services sends along with the e-mail message.
  • As i want to archive all reports, I don't want them to be overwritten. In this case Sharepoint will create unique numbers as a postfix in the file name.
  • If you don't want everyone to be able to send documents in to your library restrict access to specific users. In this case configure the SCOM Reporting Windows Service account to be allowed to send e-mail (Data Warehouse Reader account).
  • Group items on the e-mail subject. This is the only configurable item when you schedule a SCOM report.

Schedule your reports

  • Choose a naming convention for delivering your reports.
  • Use that in naming your e-mail subjects, so they are neatly grouped in your Sharepoint library.
  • Choose the right document format, e.g. PDF.

And the deliver!


Windows File Share (Sharepoint Library UNC path)

The other option is deliver the reports in to a file share. This is rather easy to setup when you're using ordinary File Shares, but as I sead earlier, more and more companies are using centralised documentation solutions like Sharepoint.
What I like about this delivery method is the possibility to give your reports a more understandable name. And more, SQL Reporting Services auto increments the new files which is more readable than the generated unique numbers when using incoming email on the Sharepoint library.

What you need:
  • A domain account for authentication on the Sharepoint library. You could use "RDA" in the account name, as for Report Delivery Account.
  • Configure the account to have enought privileges to add new items to that library
  • Write down the Network UNC path of that library

Configure your Report Server

  • On Windows Server 2003: Make sure the Web Client service is running and is started automatically. You need this to connect to a Sharepoint library with UNC.
  • On Windows Server 2008: There's no Web Client in Windows Server 2008. For the UNC path to work you need to install a feature called, Desktop Experience. You can add this feature with the Server Manager > Features > Add Feature > Desktop Experience. Notice that you'll need to reboot the Reporting Server after the installation.
  • Test the UNC path of the library from your Report Server

Schedule your report

  • Select the File Delivery Method
  • Choose a naming convention for delivering your reports.
  • Use that in naming your report files (remember that file names of new items are autoincremented)
  • Fill-in the UNC path of the library that you wrote down earlier
  • Use the domain account with the necessary privileges (a.k.a. Report Delivery Account)

And then you can deliver your report.

5 reacties:

The Guy From IT said...

Thank you! The Server 2008 Desktop Experience bit was my problem.

Unknown said...

Don't you need SharePoint Enterprise for UNC support?

Andy in San Diego and Elsewhere said...

Uh, yeah, that Desktop Experience thing bit me in the arse, too. That should be in bold, italics, 72pt type (and flashing...does the FLASH tag still work in HTML?)

Michiel Wouters said...

@Jonathan,

As far as I know, you don't explicitly need SharePoint Enterprise, even WSS supports UNC. I'm not a Sharepoint guru though.

sethsorensteward said...

Any idea why Dundas Gauges or graphs don't show up when scheduling the report to run as a Word or Excel file? I see the error "The Dundas Chart for Reporting Services report item is unavailable." I've changed the web.config file on my SSRS box according to http://support.microsoft.com/kb/977574 with no luck.

Post a Comment