Category: Reporting Services

Reporting Services Exception

If your Report Server 2008 instance is configured as SharePoint integrated and you get an exception like this one, keep on reading.

image

Additionally to the exception in the browser, the Eventlog revealed two more exception which are relevant to the SSRS exception. One was about the SSRS process, which quit unexpected, the other one from ASP.NET.

  1. “The SQL Server Reporting Services (MSSQLSERVER) service terminated unexpectedly.  It has done this 6 time(s).  The following corrective action will be taken in 60000 milliseconds: Restart the service.” Event ID 7031
  2. “.NET Runtime version 2.0.50727.4952 – Fatal Execution Engine Error (000007FEF523FA42) (80131506)”, Event ID 1023

My solution was to log on once to the SharePoint server as the account, which is used by the Reporting Services (remember: we’ve configured SSRS in SharePoint integrated mode).

Configuring SQL Server 2008 Reporting Services

Reporting Services Configuration Wizard

The SQL Server 2008 Reporting Services do not need an Internet Information Server anymore. You can host your Reports website from within Reporting Services only. If you have installed the IIS, you can still use Reporting Services, because it will register itself via HTTP.sys.

Because we configured the service account during the installation process, it is already configured.

Quote from the SQL Server 2008 Online Books:

Before you can use Report Manager or the Report Server Web service, you must configure at least one URL for each application. Configuring the URLs is mandatory if you installed Reporting Services in "files-only" mode (that is, by selecting the Install but do not configure the server option on the Report Server Installation Options page in the Installation Wizard). If you installed Reporting Services in the default configuration, URLs are already configured for each application. If you have a report server that is configured to use SharePoint Integrated mode and you update the Report Server Web Service URL by using the Reporting Services Configuration tool, you must also update the URL in SharePoint Central Administration.

Use the Reporting Services Configuration tool to configure the URLs. All parts of the URL are defined in this tool. Unlike earlier releases, Internet Information Services (IIS) Web sites no longer provide access to Reporting Services applications in SQL Server 2008.

If you want to run your Reporting Services Instance with a host header, you specify the IP where the host header will be resolved to. In my case the host header will be resolved to this IP by the DNS Server. You do not have to create an IIS Website with a host header anymore. All is done via DNS.

Next the wizard will create the necessary databases.

For a new instance, we create a new database. So click on the "Change Database" Button. A wizard will guide you through the creation process.

The Report Manager URL will adjust to the Webservice URL configured previously.

To be able to send emails, you have to configure the email settings.

Remote data sources which do not require credentials will be access with the execution account.

Before you leave the configuration wizard backup your encryption key! In case of a restore you will need this key if you attach the database to a new instance.

As you can see, the Reporting Services can be called through host header.

Tags:

Setting up Reporting Services with not default database name

Setting up the Reporting Services with a non default database name requires that you create a Script, and change the database names in this script, because otherwise the creation of the databases fails.

Before you can generate the Script, start up the Reporting Services Service in the first Dialog of the Wizard!

In the "Database Setup" Dialog of the Reporting Services Configuration Wizard hit the "Script" Button. (My Database Name is "ReportServer2005).

After the Script has been generated, open it with SQL Server Management Studio (or you favorit Editor), and replace [ReportServerTempDB] with your Database name ([ReportServer2005TempDB]). In my case this was in the lines 14036 and 14047.

INSERT INTO [ReportServerTempdb].[dbo].[ChunkData] (
needs to be changed to
INSERT INTO [ReportServer2005Tempdb].[dbo].[ChunkData] (

and

FROM [ReportServerTempdb].[dbo].[ChunkData]

to

FROM [ReportServer2005Tempdb].[dbo].[ChunkData]

After that, you can execute the script. It will generate the databases, and set them up for usage with Reporting Services.

HowTo use the resource files from SharePoint

How about using the available resources from SharePoint to translate some basic words and sentences? Well, it is quite easy to use the available resources. You need the Microsoft.SharePoint.Intl.dll and some lines of code to use the already translated resources:

But how do you know how the resource name for your translated text might be? Your friend and helper is the Reflector. Open up the ‘Microsoft.SharePoint.Intl.dll’ from the ‘C:\WINDOWS\assembly\GAC_MSIL\Microsoft.SharePoint.intl\12.0.0.0__71e9bce111e9429__71e9bce111e9429c’ folder (navigate to that path with cmd.exe and copy the dll to some safe place like c:\temp).

A big thanks goes to Janne Mattila for the information in the comment for this blog entry.

Custom assemblies for the Reporting Services 2005

Even if the Reporting Services are very powerfull, you might get to the point where you have to extend the building functionality. So why not write your own custom assembly with some additional code?

Create your assembly like this:

   1:  using System;
2: using System.Security.Permissions;
3: using Microsoft.SharePoint;
4:  
5: public class MyNamespace
6: {
7: public class ReportingExtension
8: {
9: public ReportingExtension()
10: {
11: }
12:  
13: public static string HelloWorld()
14: {
15: return "Hello World.";
16: }
17:  
18: [EnvironmentPermission(SecurityAction.Assert, Unrestricted = true)]
19: [Microsoft.SharePoint.Security.SharePointPermission(SecurityAction.Assert, Unrestricted = true)]
20: public static string getSomeListItem(string url, int itemID)
21: {
22: string returnvalue = string.Empty;
23: try
24: {
25: using (SPSite site = new SPSite(url))
26: using (SPWeb web = site.OpenWeb())
27: {
28: // do something
29: returnvalue = "";
30: }
31: }
32: catch (Exception ex)
33: {
34: return ex.ToString();
35: }
36:  
37: return returnvalue;
38: }
39: }
40: }

 

Put this line into your AssemblyInfo.cs:
[assembly: AllowPartiallyTrustedCallers]

and sign your assembly with a strong name (see here for details).

Next you have to reference your assembly in your Reporting project in the Report Properties:

You can then use your code like this:

=MyNamespace.ReportingExtension.getSomeListItem("http://site/web", 5)

Before you can use your custom assembly, you have to deploy it to the following paths:

  • Report Designer C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer
  • Reporting Server C:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting Services\ReportServer\bin

And register it in the rssrvpolicy.config file:

<CodeGroup
class="UnionCodeGroup"
version="1"
PermissionSetName="FullTrust"
Name="MyNamespace.ReportingExtension"
Description="Code for SSRS">
<IMembershipCondition
class="UrlMembershipCondition"
version="1"
Url="c:\Program Files\Microsoft SQL Server\MSSQL.1\Reporting
Services\ReportServer\bin\MyNamespace.ReportingExtension.dll"
/>

 

Now you are good to go J

Reporting Services and XML Datasource

Querying a XML Datasource

Query:

<Query>

    <SoapAction>http://ns.tld/MethodName</SoapAction>

    <Method
Namespace="http://ns.tld/"
Name="

MethodName" />

    <ElementPath
IgnoreNamespaces="True">*</ElementPath>

</Query>

Passing Parameters

You only have to define your parameters in the parameter tab of your dataset. They are passed automatically to the Webservice Method. If you want to define your parameter yourself and not use a Reporting Services Parameter, just type

<Query>

    <SoapAction>http://ns.tld/MethodName</SoapAction>

    <Method
Namespace="http://ns.tld/"
Name=" MethodName">

        <Parameter
Name="ParamName">

            <DefaultValue>ParamValue</DefaultValue>

        </Parameter>

    </Method>

    <ElementPath
IgnoreNamespaces="True">*</ElementPath>

</Query>

Reporting on SharePoint Lists

You can do Reporting on SharePoint Lists with SQL Server Reporting Services 2005 quite easy.

  1. Configure the Datasource with integrated Security
  2. Define a query like

    <Query>
    <SoapAction>http://schemas.microsoft.com/sharepoint/soap/GetListItems</SoapAction>
    <Method Namespace="http://schemas.microsoft.com/sharepoint/soap/" Name="GetListItems">
    <Parameters>
    <Parameter Name="listName">
    <DefaultValue>SharePoint List Name</DefaultValue>
    </Parameter>
    </Parameters>
    </Method>
    <ElementPath IgnoreNamespaces="True">*</ElementPath>
    </Query>

  3. Hit the "Field Refresh" Button to get the Field from your SharePoint List
  4. Drag your Fields in the Report
  5. Use the Code to eliminate "ID;#" and "<div>","</div>" from your Fields

    Use "=Code.CuttOff(Fields!ows_LinkTitle.Value)" in your Report to display your Fields