SSRS: Scheduling A Report With 2008R2 (In SharePoint Integrated Mode)

In an ideal world you'd go to a report, enter your parameters, run the report, and a few seconds later you'd have all your data in the format you need. Unfortunately whilst going to the report and entering the parameters are (usually!) easy, the "few seconds" can quickly stretch to "a few minutes" and beyond before you get your data - particularly with some of the more complex GL-based reports.

As most reporting is clyclical (month end, year end, financial year, monthly sales, weekly orders, etc) it's possible to predict the reports you'll need for a particular period and it's possible, using Reporting Services and SharePoint to scheduled the reports to run when you know you'll need them.

The first step is to go to your report;

Simple SSRS Reporting Showing GL Segments

At the top-left of the web page is a "Actions" drop down. Select this and choose the "Subscribe" option. If you don't have a subscribe option then you probably need to contact whoever administers your server to see why not; there really isn't any reason why users who run reports shouldn't be able to schedule them to run out-of-office hours.

When you select "Subscribe" you'll be directed to the following web page to select the options for the report;

SSRS Subscription Properties

This page allows you to choose how you want to receive the report. By email is probably the simplest way (and is the default). The available options are;

• Email (default): You can choose to have a report sent to any email address (for example a distribution list) but you should already remember that only the person who sets up the Subscription will be able to edit it and if they leave and their Active Directory (AD) account is disabled the subscription will automatically stop
• Windows File Share: Select this to choose to have the report sent to a Windows File Share. You can specify your username and password to grant access. Useful under certain circumstances, but I'd recommend switching to a ...
• SharePoint Document Library: We like this one. Again it requires some setting up by your system administrator but once it's there then you get all the nice features of SharePoint (sharing, versioning, security, etc) without the permission problems of Windows File Shares. This is our preferred option for in-house reporting
• Null Delivery Provider: Sometimes it's necessary just to run the report without worrying about the results (triggering caching automatically for example). If that's the case then this is the provider to select.

Once you've picked the "Delivery Extension" (how you want the report delivered) then the next steps are to expand on that by selecting the following;

• Output Format/ Report Contents/ Render Format (Depends on Delivery Extension selected): Here you can select the format you want the report to be saved in. You can (in Sharepoint 2007 with SQL SSRS 2008R2) only select a single format which means if you want the report in multiple formats (for example PDF for archiving, and Excel for working with) you need to create multiple schedules (if you'd like to show Microsoft you'd like to see this changed you can vote for the item on Connect - see here)
• Delivery Event: You can select either; a) When a report snapshot is created, b) On a shared schedule, or c) on a Custom Schedule. You need to decide when you want the report and whether it's important or not if it runs at the *exact* time specified or within a few minutes/ hours. If it absolutely has to run at a specific time then select Custom Schedule 

"Delivery Event" Subscription Options*

• Parameters: Here you select the parameters for your report. Here you'll definitely need the help of the report writers who need to have written the report with scheduling in mind in order for it to work. For example if you have a report which has a Start and End Date then the default values need to be default to, for example, the start and end of last month so that when it's scheduled to run on the 1st of every month the user can just select "defaults" rather than having to hard-code values. With a bit of planning this can work really well (NOTE: The list of parameters includes *all* parameters for the report including ones developers have marked as Hidden - I've raised this as a bug with Microsoft click here)

Now that you've selected all the options click "OK" (at the top) to create the subscription. It won't run immediately but when the Delivery Event comes around it will try, remember to check back for errors!

*- you'll notice, if you squint really hard at the "Shared Schedule" option that we (OK, I) made a mistake when setting these up. The point of shared schedules should be to increase the flexibility of system administrators to juggle the jobs around but make sure they are still available when needed. "Monthly (1st) @ 7am)" isn't exactly flexible, what I should have created is "Monthly (1st) before 8:30" so I'd be able to move the reports around as and when.

Read More

Generating a "My Reports" In SQL Server Reporting Services (SharePoint Integrated Mode)

The idea behind this report is that most, if not all, of our users will need to keep running the same reports over and over again every month and so rather than coming up with some fantastic way of categorising the reports why not just write a report that looks at the logs and see which reports the user has run and how often and provide the user with this list to pick the report from?

Here's the screen capture of the final report;

My Reports - Sample Output

As you can see we took the opportunity to provide a method for users to provide suggests to correct the (laughably inaccurate) descriptions we hold of the reports into something a lot more meaningful for what the report does for them.

Now the next thing to say is that Microsoft does not support direct access to tables in either SharePoint or Reporting Services EXCEPT for SSRS access to the ExecutionLog* views. It also helps if, like us, you have consolidated your SQL Servers so that same server hosts both SharePoint's WSS_Content_ database AND the ReportServer database for SSRS.

Here is the SQL;

SELECT A."Report Name",
       AUD.ntext2          "Description",
       AUD.nvarchar12      "Status",
       AUD.tp_Version      "Version",
       A."Execution Count"
  FROM (SELECT C.NAME "Report Name", COUNT(*) "Execution Count"
          FROM ReportServer.dbo.Catalog C
          JOIN ReportServer.dbo.ExecutionLogStorage ELS
            ON C.ItemID = ELS.ReportID
         WHERE 1 = 1
           AND ELS.UserName = @Username
           AND ELS.Format = 'RPL'
           AND ELS.TimeStart > '01-JAN-2012'
           AND C.Type = 2 -- Report
           AND UPPER(C.Path) LIKE UPPER('%Reports LIVE%')
         GROUP BY NAME
        HAVING COUNT(*) >= @Min_Execution_Count
        ) A
  JOIN WSS_Content_Reporting.dbo.AllUserData AUD
    ON A."Report Name" = AUD.tp_LeafName
   AND AUD.tp_DirName = 'Reports LIVE'
   AND AUD.nvarchar3 = 'rdl'
   AND AUD.tp_IsCurrentVersion = 'True'
 ORDER BY A."Execution Count" DESC

As you can see this SQL joins the data in both the Catalog and ExecutionLogStorage tables in SSRS and the AllUserData table in SharePoint. The Execution details are restricted to just reports (ELS.Format  = 'RPL' and C.Type = 2) and, as our live reports are in a single Document Library in SharePoint, we have also restricted it to where the reports path (C.Path) contains the words "Reports LIVE" (the name of the directory).

Additional the details from SharePoint (AUD.ntext2, AUD.nvarchar12, etc) will vary depending on your configuration. If you don't have additional details stored in SharePoint (like descriptions) you can just drop that part of the SQL.

Finally we have two parameters @Username and @Min_Execution_Count. We use @Username as the user whose report execution history we're interested in and @Min_Execution_Count as a way of restricting the report to only display reports that have been executed over a certain number of times.

We order the results by the Execution Count in descending order so the most frequently run report is at the top.

Now that we've got the dataset set-up (I've called it "My reports By Frequency Used.rsd" and it makes use of a dataset in the "Data Sets" document library) the report which accesses it is available here - please note that in order to use it you need to save it as a normal text file. I have made the following replacements;

• xxxxx - This is the servername
• yyyyyy - This is the email address that the people clicking on "[Suggest Update]" will have their messages sent to

Read More

SharePoint 2007: Implementing Simple Tagging

This blog post just covers a very simple "tagging" example that I've been using in our SharePoint 2007 Reporting Services server to allow the sharing of reports between different areas. For example in Finance we have a simple report that gets GL Journal Entry lines for a specific Account Code combination that is useful to many of the teams in Finance so they all want access to it. Using tags each team can have it's own view of reports that include the "shared" report.

Let's start with looking at how our current environment is configured. Basically we have a single Document Library (Reports LIVE) which contains all our reports and we've added various additional fields to the SharePoint library such that when you edit the document it looks like this;

Report Properties (SharePoint 2007)

As you can see we had a slight disagreement on how we should work. I championed the "tag everything, categorise nothing" position and sadly had to compromise on "Categorise on Business Process, and Tag Teams Within The Process" - of course we quickly had to turn the categories into a multi-select but otherwise it seems to work just fine (of course I still think I was right - but doesn't every software engineer?!).


The field I'm going to be storing tags in is the "Keyword(s)" field, as you can see it's not mandatory (as sometimes a Process is so distinct that after you've defined the process category then tagging is just irrelevant) and for the report specified above it's actually blank - we're going to change that.


I've picked an Expense report (from Oracle Internet Expenses) deliberately as I know that it is used by two teams; expenses, and accounts payable.  At the moment all the teams in Finance (General Ledger, Purchasing, Inventory, Audit, etc) all have the report visible in their lists.


The first step is to enter some keywords. I'm using ";" as a separator so I've entering "expenses;payables" (note the lack of a space);

Adding Keywords To The Report

Now that the changes have been saved go to the drop down at the top right of the document library and choose "Create View". I'm going to create a view called "Finance: Payables" that will contain any report that has been tagged with "payables";

New View for Finance (Accounts Payable)

Select the columns you'd like to display and then scroll down to the Filter section and choose to show the items when column "Keyword(s)" contains "payables";

Building a View Filter

Now save the new view and you should see the report you've tagged. To properly implement this you then need to create a new view to pick up the reports tagged with "expenses" - then you'll see the same report appearing in both views but not, if you continue the example further and create views for other groups, in those views.

Of course you're totally reliant on everyone spelling (and typing!) everything correctly - something that's far from guaranteed - but it does give you a way of dynamically categorising your items and sharing them between different groups. Something using folders won't let you do.

Read More

SSRS: Changing US-format Date/Time Pickers (SharePoint Integrated Mode)

If you have, like us, configured your servers and clients for a non-US locale and then been surprised to see that when running SQL Server Reporting Services (SSRS) all the date/time pickers seem still, somehow, to be stuck in US Date/Time format the fix is actually incredibly easy.

Basically the issue is that SharePoint uses neither the Locale of the Server or the Locale of the User but instead uses the SharePoint culture to determine the format that the Date/Time pickers will use.

In order to fix the issue you need to be able to administer the site in order to update the Regional Settings.

Go to your SharePoint website;

"Site Actions" on a SharePoint site

Using the "Site Actions" drop down select "Site Settings";

Site Settings in SharePoint

Under the "Site Administration" section select "Regional settings";

Site Settings > Regional Settings in SharePoint

Change the locale (which should be saying "English (United States)") to the correct locale.

Date/Time pickers will now be in UK-format (or whatever format you have selected);

SSRS Date/Time Pickers Showing UK-format

NOTE: It's worth noting that if you set your users settings to a different region the settings on the Site seem to take precedence to your personal settings.

Read More