SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers

SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 1 on 33
SQL Server Reporting Services 2008 in SharePoint
Integrated Mode
Distributed Server Deployment with Three computers
An example of deployment Topology for SQL Server Reporting Services 2008 in
SharePoint Integrated Mode:
Distributed Server Deployment with Three computers.
Updated Tuesday, April 28, 2009
Summary
Introduction ........................................................................................................................................................ 2
1 - Prerequisites ................................................................................................................................................. 3
01 - Windows Server Domain and Active Directory ............................................................................ 3
02 - Service Accounts ..................................................................................................................... 3
03 - Available Server....................................................................................................................... 3
2 - Installation Overview .................................................................................................................................... 3
01 - Topology description ................................................................................................................ 3
- Computers: .................................................................................................................................... 4
- Service Accounts: ........................................................................................................................... 5
02 - Recommended installation sequence ......................................................................................... 7
3 - Installation ................................................................................................................................................... 10
3.1 - Step 1: Installation of SQL Server 2008 on SQLSERVER2008 .................................................. 10
01 - Start SQL Server 2008 installation .......................................................................................... 10
02 - Selecting Installation features .................................................................................................. 10
03 - Server Configuration ................................................................................................................ 11
04 - Not to forget ............................................................................................................................. 12
3.2 - Step 2: Installation of SQL Server Reporting Services 2008 on SSRS2008SPIM ........................ 12
01 - Start SQL Server 2008 installation .......................................................................................... 12
02 - Selecting Installation features .................................................................................................. 12
03 - Server Configuration ................................................................................................................ 13
04 - Reporting Services Configuration Page .................................................................................. 13
05 - Check Installation .................................................................................................................... 13
3.3 - Step 3: Installation of SharePoint 2007 on SHAREPOINT2007.................................................. 14
01 - Create SharePoint databases and grant permissions to SPS_SETUP ................................... 14
02 - Start SharePoint installation on SHAREPOINT2007 ............................................................... 17
03 - Running SharePoint Product and Technologies Configuration Wizard ................................... 17
03 - Creating a Web Application on port 81 and a TestReportingServices Site Collection. ........... 18
3.4 - Step 4: Installation of SharePoint 2007 on SSRS2008SPIM ...................................................... 18
01 - Start SharePoint installation on SSRS2008SPIM .................................................................... 20
02 - Check SharePoint installation on SSRS2008SPIM ................................................................. 23
3.5 - Step 5: Configuration and test of the Report Server on SSRS2008SPIM .................................... 24
01 - Preparing SSRS_SERVICE account to configure Reporting Services.................................... 24
02 - Creating report Server databases ........................................................................................... 24
03 - Provisioning Report Server Web Service .......................................................................................... 26
3.6 - Step 6: Installation of Add In for SharePoint, Configuration and Check. ...................................... 28
01 - Installation of SQL Server Reporting Services 2008 Add In for SharePoint ............................ 28
02 - Configuration of reporting services in SharePoint Central Administration ............................... 28
3.7 - Step 7: BIDS installation, Report creation, deployment and test ................................................. 29
01 - BIDS installation ...................................................................................................................... 29
02 - Report Creation ....................................................................................................................... 29
03 - Report Deployment .................................................................................................................. 30
04 - Report Data Source Configuration .......................................................................................... 30
05 - Report Displaying .................................................................................................................... 32
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 2 on 33
Introduction
This tutorial includes the key steps for deploying and configuring:
 SQL Server 2008 Database Engine
 SQL Server Reporting Services 2008
 SharePoint 2007 (Windows SharePoint Services 3.0)
in an environment composed of three computers
This tutorial is completing these previous ones
Integrating Reporting Services 2008 with SharePoint 2007 Step 1 - SQL Server 2008 Installation
Integrating Reporting Services 2008 with SharePoint 2007 Step 2 - SharePoint Installation
Integrating Reporting Services 2008 with SharePoint 2007 Step 3 - Configuring Reporting Services for
SharePoint 3.0 Integration - Part 1
Integrating Reporting Services 2008 with SharePoint 2007 Step 4 - Configuring Reporting Services for
SharePoint 3.0 Integration - Part 2
Installing SQL Server 2008 samples: Adventure works (Integrating Reporting Services 2008 with
SharePoint 2007 Step 5)
Integrating Reporting Services 2008 with SharePoint 2007 Step 6 - Report Creation and Deployment
that was showing how to install SQL Server Reporting Services 2008 in SharePoint integrated mode
with SharePoint 2007 but on a single computer. In this tutorial, we are going to build a real Production
Environment.
Of course it would be better before doing this installation to have done at least once the installation on
a single machine with one service account: local administrator account.
01- Tutorial goal
In this installation tutorial, I will show a complete example of installation with the required service
accounts, in order to allow an Infrastructure Administrator to install SQL Server Reporting Services
2008 in SharePoint Integrated mode with SharePoint 2007 on three different computers for a real
production environment compliant with the principle of least privilege administration.
What I have noticed performing several interventions for different clients, is that the knowledge
required installing even Microsoft Office SharePoint Server 2007 in a QA, staging, or Production
Environment is increasing more and more because the involved products (SharePoint, SQL Server,
IIS, etc.) are becoming more and more complex.
For that reason, it is difficult for people working in Infrastructure and Network Area to have the entire
required acknowledgement, and very often they need the support of SharePoint developers that are
used to install SharePoint on their own development environment. However, on the opposite,
SharePoint developers are not deploying very often the products on several machines using the
several service accounts required by the least-privilege administration policy that is recommended
when you configure a Production Environment. They are often, not either aware of the configuration
key elements that are needed by SharePoint, SQL Server, IIS, etc. to work on a multiple server
environment. If you add SQL Server Reporting Services 2008 to SharePoint 2007, the number of
operations to perform and choices to take, much more increases. So there is a knowledge gap to fill...
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 3 on 33
1 - Prerequisites
01 - Windows Server Domain and Active Directory
The machines ready for installation have joined a Domain. An Active Directory is available in this
Domain
The name of the Domain used in this tutorial is "CORPORATE".
02 - Service Accounts
As this tutorial is rather made for people working in Infrastructure Area, I do not show how to create a
domain account in Active Directory, and I put in the prerequisites the presence in the Active Directory
of the following Service Accounts in order to be able to build an environment compliant with the
principle of least privilege administration (Plan for administrative and service accounts (Office
SharePoint Server)):
 SQL_SERVICE
 SPS_SETUP
 SPS_FARM
 SSRS_SERVICE
 SPS_AppPoolWebApp81
I will explain these accounts properties and roles in the next section.
03 - Available Server
Of course you have three available Servers running Windows 2003 Server OS ready for the
installation. The machine names I use in this post are the followings:
 SQL2008
 SSRS2008SPIM (for SQL Server Reporting Services 2008 in SharePoint Integrated Mode)
 SHAREPOINT2007
I will indicate these servers role in the next section (that is to mean if they need to be Application
Server or not, thus if IIS is needed to be installed or not).
2 - Installation Overview
01 - Topology description
This is a part of the MSDN article (Deployment Topologies for Reporting Services in SharePoint
Integrated Mode)
[...]
Three-Computer Deployment
The following illustration shows components for a three-computer deployment
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 4 on 33
The first computer hosts an instance of a SharePoint product or technology. The second computer
hosts SQL Server Reporting Services. The third computer hosts an instance of the Database Engine.
In the illustration, notice that the Database Engine does not require a local installation of a report
server or an instance of a SharePoint product or technology.
To use a remote database server with a deployment of a SharePoint product or technology, choose
the Advanced installation option when installing an instance of a SharePoint product or technology, or
use Central Administration to modify the database server setting. To select a remote database server
for Reporting Services, use the Reporting Services Configuration tool. For more information about
edition and connection requirements for a report server database, see Creating a Report Server
Database and How to: Create a Report Server Database for SharePoint Integrated Mode (Reporting
Services Configuration).
[...]
- Computers:
Machine 1 Name: SQL2008
Operating System: Windows 2003 Server
Server Role none (no more need of IIS for SQL Server 2008)
Deployed products:
SQL Server 2008 (Database Engine, Full Text, Basic Management Tools, Management
Tools Complete)
Topology: one instance (no named)
Machine 2 Name:
SSRS2008SPIM (for SQL Server Reporting Services 2008 SharePoint Integrated
Mode)
Operating System: Windows 2003 Server
Server Roles:
Application Server (no more need of IIS for Report server 2008 since it has its own IIS,
but we need IIS for SharePoint)
Deployed products: SQL Server 2008 Reporting Services, Windows SharePoint Services 3.0
Topology:
SQL Server 2008 Reporting Services: one instance, simple (no Scale Out)
Windows SharePoint Services 3.0: Minimal Installation
Machine 3 Name: SHAREPOINT2007
Operating System: Windows 2003 Server
Server Roles: Application Server (we need IIS for SharePoint)
Deployed products: Windows SharePoint Services 3.0
Topology: simple Farm
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 5 on 33
- Service Accounts:
Account Name and
Password:
SQL_SERVICE Passw0rd
Account Description:
SQL Server prompts for this account during SQL Server Setup. This account is used as
the service account for the following SQL Server services:
 SQL Server Database Engine
 SQL Server Agent
we will use it also for:
 SQL Full-Text Daemon Launcher
Domain vs. Local Domain Account (can be a local account)
Permissions no special permissions required
Referenced
documentation:
Technet: Plan for administrative and service accounts
Account Name SPS_SETUP
Account Description:
SharePoint SetUp user Account on SHAREPOINT2007 Server
SharePoint SetUp user Account on SSRS2008SPIM Server
The service account that is used to run:
 Setup of SharePoint on each server computer
 The SharePoint Products and Technologies Configuration Wizard
 The Psconfig command-line tool
 The Stsadm command-line tool
 Setup of Reporting Services on Report Server computer
 Reporting Services Configuration Manager on Report Server computer
 Credentials for “Enter Credentials” dialog box in “Grant Database Access Page”
during Reporting Services Configuration in SharePoint Central Administration.
(this account might be used in operations of recovery, when you need to
reinitialize Reporting Services Account for example if you tried to change this
account or its password has expired)
cf. : Configuring the Report Server Service Account
Domain vs. Local Domain Account (cannot be a local account)
Permissions
Administrator of SHAREPOINT2007
Administrator of SSRS2008SPIM
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 6 on 33
SQL Server login on the computer running SQL Server.
Member of the following SQL Server security roles:
 securityadmin fixed server role
 dbcreator fixed server role
 dbowner of SharePoint_Config database
 dbowner of SharePoint_AdminContent database
Referenced
documentation:
Technet: Plan for administrative and service accounts
Account Name SPS_Farm
Account Description:
SharePoint Server Farm Account
This account is also referred to as the database access account.
This account is:
 The application pool identity for the SharePoint Central Administration Web
site.
 The process account for the Windows SharePoint Services Timer service.
Domain vs. Local Domain Account (cannot be a local account)
Permissions
no special permissions required
Its permission for SharePoint databases will be granted automatically during
installation process
Referenced
documentation:
Technet: Plan for administrative and service accounts
Account Name : SSRS_SERVICE
Account Description:
SQL Server prompts for this account during SQL Server Reporting Services 2008 Setup.
This account is used as the service account for the following SQL Server services:
 SQL Report Server on SSRS2008SPIM
Domain vs. Local
Domain Account (cannot be a local account in the current topology: Distributed Server
Deployment with Three computers)
Permissions
The SharePoint Database Access will be granted during Report Server Configuration in
SharePoint Central Administration on SHAREPOINT2007
Referenced msdn: How to: Configure a Service Account for Reporting Services
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 7 on 33
documentation:
Account Name
SPS_AppPoolWebApp81
Account Description:
Application pool identity
This account will be used when creating the fist Web Application and its Application
Pool.
Domain vs. Local
Domain Account (cannot be a local account in the current topology: Distributed Server
Deployment with Three computers)
Permissions
No manual configuration is necessary.
 Use a separate domain user account for each application pool.
 This account should not be a member of the Administrators group on any
computer in the server farm
The following are automatically configured:
 Membership in the db_owner role for content databases and search
databases associated with the Web application.
 Access to read from the configuration and the SharePoint_AdminContent
databases.
 Additional permissions for this account to front-end Web servers and
application servers are automatically granted.
Referenced
documentation:
msdn: How to: Configure a Service Account for Reporting Services
02 - Recommended installation sequence
The installation performed in this tutorial includes the following steps:
 Step 1: Installation of SQL Server 2008 on SQLSERVER2008
 Step 2: Installation of SQL Server Reporting Services 2008 on SSRS2008SPIM
 Step 3: Installation of SharePoint 2007 on SHAREPOINT2007
 Step 4: Installation of SharePoint 2007 on SSRS2008SPIM
 Step 5: Configuration and test of the Report Server on SSRS2008SPIM
 Step 6: Installation of SQL Server Reporting Services 2008 Add In for SharePoint on
SHAREPOINT2007, Configuration and Check.
 Step 7: BIDS installation, Report creation, deployment and test.
WARNING:
when you are installing SQL Server Reporting Services 2008 in SharePoint Integrated Mode, and want
to keep being compliant with the least-privilege administration principle, this principle and its limited
permissions become a problem because both, the configuration of report server and the installation of
SQL Server Reporting Services Add in for SharePoint fail due to the limited access of all these
accounts. But with a workaround you can install the products combination properly and restore the
permissions keeping compliant with the least privilege administration. Read carefully the following:
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 8 on 33
this is a part of the Technet article Plan for administrative and service accounts (Windows SharePoint
Services)
[...]
Least-privilege administration requirements when using domain user accounts
Least privilege administration is a recommended security practice in which each service or user is
provided with only the minimum privileges needed to accomplish the tasks they are authorized to
perform. This means that each service is granted access to only the resources that are necessary to
its purpose. The minimum requirements to achieve this design goal include the following:
- Separate accounts are used for different services and processes.
- No executing service or process account is running with local administrator permissions.
By using separate service accounts for each service and limiting the permissions assigned to each
account, you reduce the opportunity for a malicious user or process to compromise your environment.
Least privilege administration with domain user accounts is the recommended configuration for most
environments.
[...]
Regarding Windows SharePoint Services 3.0, let us examine the minimal services and processes
needed and we will understand least privilege basic administration for SharePoint.
 Setting up SharePoint on each server computer: task requiring local administrator permissions
 Running the SharePoint Products and Technologies Configuration Wizard: task requiring local
administrator permissions
 Executing a Psconfig command-line: task requiring local administrator permissions
 Executing an Stsadm command-line: task requiring local administrator permissions
 Running the process of application pool identity for the SharePoint Central Administration Web
site: process that does not require local administrator permissions
 Running the Windows SharePoint Services Timer service: service that does not require local
administrator permissions
Looking at the previous enumeration, if we assign the four first tasks to an account that has the local
administrator permission, and the two last to an account that has not the administrator permissions,
we are compliant with the least privilege administration principle.
Thus, the least privilege administration principle for SharePoint is based on two main user accounts:
 Setup Account
 Farm Account
The Setup Account is Administrator of all the servers where SharePoint is deployed. It is used also to
run the SharePoint Products and Technologies Configuration Wizard, and to execute Stsadm and
Psconfig command. This account is also one of the Farm administrators, and has access to the entire
operations provided by the SharePoint Central Administration.
The Farm Account appears as System Account when you are signed in with it on a SharePoint site,
but is not Administrator of any SharePoint server, thus you cannot perform a Psconfig or Stsadm
command using it. You cannot run the SharePoint Products and Technologies Configuration Wizard
with it.
Furthermore, in SharePoint Central Administration, the Farm account cannot access to the entire
administration tasks, for example, when you are connected to SharePoint Central Administration with
this account, in the Tab Operations, you cannot see the following links:
 Services on Server
 Incoming e-mail settings
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 9 on 33
And in Application Management Tab
 Remove SharePoint from IIS Web Site
The Application Pool Identity Account is an account used during the creation of the first Web
Application. The least privilege administration requirements for this account are:
 Use a separate domain user account for each application pool.
 This account should not be a member of the Administrators group on any computer in the
server farm.
The Report Server Account that is required for SSRS installation is a fourth account that must have
least privilege, it is the Report Server account, here called SSRS_SERVICE. For this account, very
simple, it has no permissions at all. However, it might be useful to use it during installation process,
you will see why...
The Installation Sequence overview with the workarounds
01 - Use the SharePoint Setup Account to install Reporting Services on the Report Server computer (it
has local administrator permissions for this computer because it will be used to install SharePoint on
this computer later).
02 - Use the SharePoint Setup Account to install SharePoint on the SharePoint Server.
03 - Use SharePoint Setup Account to create the Web Application but when prompted, reference the
Application Pool Identity Account
The additional operation will be to grant the dbo or db_owner permissions for the new created content
database to the SPS_SETUP account as specified in Technet documentation previously mentioned:
After each database has been created, change the database owner (dbo or db_owner) to the Setup
User account.
04 - Configuring Reporting Services on the Report Server.
Here again there is additional operation to perform
Let us examine the permissions required by the account configuring SQL Server Reporting Services
2008.
1. Local administrator of the Report Server computer
If the account you are logged on with is not local administrator of the computer, you cannot open
Reporting Services Configuration Manager.
2. high privileges on the Database Engine
During the Report Server Databases creation, the configuration process is not only creating the
ReportServer and the ReportServerTemp Databases, but also configuring the Databases (rights
generation, connection rights application), and the account used by the process needs high
privileges on the Database Engine. For example, there is a request on msdb database :
USE msdb
if not exists (select * from sysusers where issqlrole = 1 and name = 'RSExecRole')
BEGIN
EXEC sp_addrole 'RSExecRole'
END
The consequence is that you cannot configure Reporting Services with SPS_SETUP account,
since it has unfortunately not enough permission on the database engine.
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 10 on 33
You have two solutions:
You grant temporary the database engine sysadmin permission to the Setup account.
You add temporary the SQL Database Engine administrator account to the administrators group of
Report Server computer, and you use this account to configure reporting Services.
(I am quite sure that the second solution will be never used).
05 - Workaround for Add In installation:
When you are installing Reporting Services Add In for SharePoint on the SharePoint computer, you
have temporary to grant the database engine sysadmin permission to the Setup account AND to add
temporary the Farm account to the machine Administrators, otherwise the Reporting Services Add In
installation fail over.
06 - Use the SharePoint Setup or Farm Account to configure Reporting Services on the SharePoint
Server.
Installation Trick:
In the current installation tutorial I have taken advantage of this workaround to perform Reporting
Services Configuration with the Reporting Services Account (here called SSRS_SERVICE). Doing that
I am able to check that the Report Server Web Service works well before installing Reporting Services
Add In for SharePoint.
In order to be allowed to do that, I had temporary to grant the database engine sysadmin permission
to the SSRS_SERVICE account and to temporary add this account to the Report Server computer
Administrators. (only computer Administrators can configure Reporting Services)
But you can skip this and configure Reporting Services with the SharePoint Setup account. In that
case, you will have to wait the end of the installation to be sure that the Report Server Web Service
works properly.
I will remind these operations in time using the red color.
3 - Installation
3.1 - Step 1: Installation of SQL Server 2008 on SQLSERVER2008
01 - Start SQL Server 2008 installation
Logon to SQL2008 with a domain user account that is administrator of the computer.
Launch SQL Server 2008 installation.
Follow all steps of the previous post Integrating reporting Services 2008 with SharePoint 2007 Step 1 -
SQL Server 2008 installation from step 1 to step 9.
02 - Selecting Installation features
On Step 10 - Selecting Installation features, select:
 Database Engine Services
 Full-Text Search (it is for SQL 2008 Samples: Adventure Works).
 Management Tools - Basic (SQL Server Management Studio).
 Management Tools - Complete (SQL Server Management Studio complement included those
for Reporting Services).
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 11 on 33
Follow the procedure until step 12
03 - Server Configuration
Use the same account for all services: SQL_SERVICE.
Set the SQL Server Agent account to be started “Automatically”, because it will be used for Standard
or Data Driven Subscriptions in Integrated mode and thus, has to be enabled and always started.
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 12 on 33
04 - Not to forget
Do not forget the Collation settings otherwise you will have an exception during SharePoint
deployment.
This is the Installation Complete Screen Shot.
and the screen shot of the SQL Server Management Studio open.
Do not forget to configure connections protocol with SQL Server Network Configuration Area
3.2 - Step 2: Installation of SQL Server Reporting Services 2008 on SSRS2008SPIM
01 - Start SQL Server 2008 installation
Logon to SSRS2008SPIM with a domain user account that is administrator of the computer.
Launch SQL Server 2008 installation. Follow all steps of the previous post Integrating reporting
Services 2008 with SharePoint 2007 Step 1 - SQL Server 2008 installation from step 1 to step 9.
02 - Selecting Installation features
On Step 10 - Selecting Installation features, select:
 Reporting Services
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 13 on 33
Follow the procedure until step 12
03 - Server Configuration
Use the SSRS 2008 service account: SSRS_SERVICE
04 - Reporting Services Configuration Page
We have to select the last option because SharePoint is not installed, neither on SHAREPOINT2007
computer nor on SSRS2008SPIM one.
05 - Check Installation
This is the Installation Complete Screen Shot.
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 14 on 33
The screen shot of the Reporting Services Service.
The screen shots of the welcome Pages of the Report Server Configuration Tool
3.3 - Step 3: Installation of SharePoint 2007 on SHAREPOINT2007
01 - Create SharePoint databases and grant permissions to SPS_SETUP domain account
Logon to SQL2008 with SQL_SERVICE domain service account.
Open SQL Server management Studio
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 15 on 33
Create databases as in the previous post: Integrating Reporting Services 2008 with SharePoint 2007 -
SharePoint Installation
In Management Studio right click Login node click "New Login"
On the "Login-New" Pop Up type "CORPORATE\SPS_SETUP" as new login name.
You can check the new Login creation in SQL Server 2008 Management Studio
Right click the new login and click "Properties"
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 16 on 33
Login-properties Dialog is opening.
Double click Server Roles, and in the opening Dialog, check "SecurityAdmin" and "DbCreator
double Click User mappings, and in the opening Dialog, locate the two SharePoint databases
SharePoint_Config and SharePoint_AdminContent.
For each of these databases, grant SPS_SETUP account the following permission:
"DbOwner".
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 17 on 33
02 - Start SharePoint installation on SHAREPOINT2007
Logon to SHAREPOINT2007 with the SPS_SETUP domain user account.
Launch SHAREPOINT 2007 installation as in the previous post.
check box unchecked).
Open a command prompt and type the following line:
psconfig -cmd configdb -create -server SQL2008 -database SharePoint_Config -user
CORPORATE\SPS_FARM -password Passw0rd -admincontentdatabase SharePoint_AdminContent
execute the command.
You will obtain this window after performing the command.
03 - Running SharePoint Product and Technologies Configuration Wizard
Run the SharePoint Product and Technologies Configuration Wizard as in the previous post, but with
the multiple Server Environment parameters.
You can check the parameters on the following screen shots.
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 18 on 33
At the end of the process as usual, you will obtain the displaying of the Central Administration Home
page
03 - Creating a Web Application on port 81 and a TestReportingServices Site Collection.
In order to have a Site Collection with a Root Web Site that can display SQL Server reporting services
2008 Reports, DataSources and so on in a SharePoint 2007 document library,
Perform the steps 2 to 4 of the previous post:
Integrating Reporting Services 2008 with SharePoint 2007 step 4 - Configuring reporting services for SharePoint
3.0 Integration
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 19 on 33
For step 5 because we are in a multiple server environement and want to be compliant with the least
privilege administration principle, do the following:
In the "Create New Web Application" page, Application Pool section, select "Create new application
pool" option, type the name of your new Application Pool, select "Cofigurable" and type the name and
the password of the Application Pool Identity domain account.
Then, perform your Site Collection creation and check that the Site Collection is properly created.
Now, we have to grant the dbo permission to the SPS_SETUP account for the brand new created
WSS_Content_WEBAPP81 content database. If we do not do that, we will not be able to perform
operations using stsadm command for the new created Web Application, its Site Collections, its Web
Sites, etc., because we will get an "Access Denied" exception.
Logon to the SQL Server computer (SQL2008).
Open the SQL Management Studio.
Locate the login for SPS_SETUP account.
Right Click the Login and select properties.
In the opening dialog click "User Mapping".
Locate the WSS_Content_WebApp81 database.
Check the check box for the database.
In "Database role member ship for: WSS_Content_WEBAPP81" pane check the check box for
db_owner.
Click OK.
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 20 on 33
3.4 - Step 4: Installation of SharePoint 2007 on SSRS2008SPIM
01 - Start SharePoint installation on SSRS2008SPIM
Logon to SSRS2008SPIM with the SPS_SETUP domain user account (that has Administrator
permissions on this server too).
First of all start by stopping the IIS default web site in order to avoid conflicts with the web service of
Report server that also uses port 80.
Launch SHAREPOINT 2007 installation as in the previous post: Integrating Reporting Services 2008
with SharePoint 2007 - SharePoint Installation
Follows the steps 4 to 9. (Until closing the first installation wizard with the check box unchecked).
When Setup finishes, a dialog box prompt you to complete the configuration of your server. Be sure
that the "Run the SharePoint Products and technologies Configuration Wizard now" check box IS
selected. (It is the opposite of what we have done for installation of SharePoint 2007 on
SHAREPOINT2007 server).
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 21 on 33
Click "Next". On the "Connect to a Server farm" dialog select "Yes I want to connect to an existing
Farm".
Click "Next". On the "Specify Configuration Database Settings" type "SQL2008" for the databases
server name, then click "Retrieve Database Names" button.
The wizard retrieves not only the SharePoint Configuration database, but also the Farm Service
Account...
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 22 on 33
...so you just have the password to type
Click "next" to launch configuration and wait until the "Configuration Successful" dialog.
As usual, when you close this Dialog, SharePoint Central Administration Web Site Home Page
Appears.
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 23 on 33
02 - Check SharePoint installation on SSRS2008SPIM
Take advantage of this page to check that SSRS2008SPIM server has now joined the SharePoint
Farm.
Click Operation Tab, then servers in farm link. You can see the presence of the computer as a new
Server of the SharePoint Farm.
There is another thing you can notice since SSRS2008SPIM is a Web Front End. Open IIS and Notice
that a "SharePoint - 81" Web Application in now available. Right click the Web Application node in IIS
and click "Browse".
The testReportingServices Site Collection Root Web Site will be served by the SSRS2008SPIM
computer as it is become a new Web Front End of a SharePoint Farm.
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 24 on 33
3.5 - Step 5: Configuration and test of the Report Server on SSRS2008SPIM
I could keep using SharePoint Setup account (SPS_SETUP) to configure report server, but configuring
Report Server with Reporting Services account will allow me to check the Report Server Web Service
before having installed Reporting Services Add In for SharePoint on the SharePoint computer, and
before having configured SharePoint for Reporting Services.
(If you prefer to keep using SharePoint Setup account (SPS_SETUP) to configure report server do not
forget to Logon to SQL2008 and grant the sysadmin permissions to SPS_SETUP since it has not
enough permissions to configure the Report Server Databases.)
01 - Preparing SSRS_SERVICE account to configure Reporting Services
Add SSRS_SERVICE to the Administrators Group of the Report Server computer (SSRS2008SPIM).
To use Report Server Configuration Manager you must be logged as an Administrator.
Logoff from SSRS2008SPIM and re-logon with SSRS-SERVICE.
Logon to SQL2008 and grant temporary the sysadmin permissions to SSRS_SERVICE
02 - Creating report Server databases
Open Report Server Configuration Manager and connect to the default instance of the current server
On the left menu click database, then, on opening dialog, click change database to open the change
database dialog.
Select "create a new report Server Database", then click "Next".
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 25 on 33
On database server dialog notice that the SQL 2008 Server Computer has been retrieved, and that
the account is SSRS_SERVICE.
Do not forget to test database connection.
On the next dialog select SharePoint Integrated mode.
On the next dialog select windows credential and type again SSRS_SERVICE
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 26 on 33
Check the summary and launch database creation
03 - Provisioning Report Server Web Service
On the left menu of the report server Configuration Manager, this time click Web Service URL
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 27 on 33
Click Apply to provisioning the Web Service.
Click the URL to check the Web service. Notice that no mention of SharePoint is visible for the
moment.
Logon to SQL2008 and remove the sysadmin permissions to SSRS_SERVICE
Click again the URL to check the Web service. Notice that the connection requires now the
configuration in SharePoint. But we will not be able to perform it before having installed SSRS Add In
for SharePoint on SHAREPOINT2007 computer.
Remove SSRS_SERVICE from the Administrators Group of the Report Server computer
(SSRS2008SPIM)
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 28 on 33
3.6 - Step 6: Installation of SQL Server Reporting Services 2008 Add In for SharePoint on
SHAREPOINT2007, Configuration and Check.
01 - Installation of SQL Server Reporting Services 2008 Add In for SharePoint on SHAREPOINT2007
Logon to SQL2008 and grant the sysadmin permissions to SPS_SETUP
Logon to SHAREPOINT2007 and grant the Administrator permissions to SPS_FARM
Then, perform SQL Server Reporting services 2008 Add In for SharePoint 2007 installation as shown
in my previous post:
Integrating Reporting Services 2008 with SharePoint 2008 Step 4 - Configuring Reporting Services for
SharePoint 3.0 Integration - Part 2: Operations performed in SharePoint Central Administration.
Step 1 to 8. When it is done, go to SharePoint central Administration, Application Management Tab,
and locate Reporting Services section.
02 - Configuration of reporting services in SharePoint Central Administration
Open "Manage Integration Setting" Page and type the report Server URL.
For the "Authentication Mode" Section select "Trusted Account" that is the right mode for the present
configuration in multiple servers with NTLM.
For more information there is a good post of Reza Alirezaei:
SSRS 2008 integrated mode: security
Open the Grant Database Access Page, and type the Report Server Computer name.
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 29 on 33
Click OK.
In the Enter Credentials dialog box, specify the User Name and Password to connect to the report
server to retrieve the service account information. Be sure to enter credentials for an account that is a
member of the local Administrators group on the report server computer. In our case, SPS_SETUP
has the required permissions to perform this task.
When the page is refreshed, you can open a browser window and type the url of the report Server
Web Service: http://ssrs2008spim/reportserver
Notice that you are now connected to the SharePoint Sites and Libraries. Your Installation is done!
Don't forget to:
Logon to SQL2008 and remove the sysadmin permissions to SPS_SETUP
Logon to SHAREPOINT2007 and remove SPS_FARM from the Administrators group.
3.7 - Step 7: BIDS installation, Report creation, deployment and test
01 - BIDS installation
I have chosen to install BIDS on SSRS2008SPIM computer to be as close as possible to a real
Production Environment where BIDS will not be installed on the SharePoint machine.
Doing that, I will be sure to succeed in deploying report remotely using the current environment.
Logon to SSRS2008SPIM with SPS_SETUP account. Run the SQL Server installation package, and
select "Add a feature to the current installation". Select Visual Studio and wait until installation process
is complete.
02 - Report Creation
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 30 on 33
Open BIDS.
Create a report (if needed install Adventure Works as a sample Database).
You can do my previous tutorial:
Integrating reporting Services 2008 with SharePoint 2007 Step 6 - Report Creation and Deployment
For the project properties, type the following:
 TargetDataSourceFolder: http://SHAREPOINT2007:81/shared documents
 TargetReportFolder: http://SHAREPOINT2007:81/shared documents
 TargetServerURL: http://SHAREPOINT2007:81/
03 - Report Deployment
Perform deployment with BIDS.
If you try to see the deployed report now, you will have a credential exception. You will be able to see
the type of this exception only on the report Server machine: SSRS2008SPIM.
The error type will only be displayed on the Report Server Web Service when you are logged on to
SSRS2008SPIM. And Reporting Services errors can be examined. This is the path of the Log Files of
SQL Server 2008 Reporting Services:
c:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\LogFiles
04 - Report Data Source Configuration
To solve this error logon to SHAREPOINT2007.
Go to the Shared Documents documents library of the TestReportingServices Web Site, and locate
the file Report1.rdl (that has not its extension visible).
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 31 on 33
Expand the Report contextual menu and select "Manage Data Sources
The Manage Data Sources Page is displaying
Click the Datasource1 link to open the edit the data source connection information.
Select "Stored Credentials"
Use an account that have read permissions on the databse used for this report. For example in my
case, I used the SSRS service account credentials, that will be quite never done in real life
Check the check box: "Use as Windows Credentials".
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 32 on 33
05 - Report Displaying
You can now display your report in:
 RSViewerPage.aspx
 Report Viewer Web Part
SSRS 2008 SharePoint 2007, Distributed Server Deployment with Three Computers – Marc Charmois Page 33 on 33
 Report Server Web Service from the SHAREPOINT2007 computer
 Report Server Web Service from the SSRS2008SPIM computer
End of the Document

SQL Server 2008 R2 Reporting Services Lookup Functions

Problem

I noticed a new Lookup function in SQL Server 2008 R2 Reporting Services. This sounds like something that I've needed for quite some time. Can you provide an example of how I can use this?

Solution

There are three lookup functions in SQL Server 2008 R2 Reporting Services: Lookup, LookupSet and MultiLookup. In this tip I will review each function and provide a simple report to show how it is used.

Lookup Function

I will use the following tables from the AdventureWorksDW2008R2 database in my sample report:

Assume that my report contains a Dataset for each table; e.g. SalesQuotaDataset and EmployeeDataset. I want to list the rows in the SalesQuotaDatset on the report and include the employee's name from the EmployeeDataset. You might be thinking why not just have a single dataset and get the employee name by a join. You can do that. However, the Lookup is the way to go, if what you have are two datasets and you cannot change the queries.

The Lookup function has the following parameters:

Lookup(source_expression, destination_expression, result_expression, dataset)

• source_expression - specifies the value in the current scope that you want to use as the lookup; e.g. EmployeeKey in the SalesQuotaDataset
• destination_expression - specifies the value that you want to match in the destination dataset; e.g. EmployeeKey in the EmployeeDataset
• result_expression - is what you want the lookup function to return; e.g. EmployeeName from the EmployeeDataset.
• dataset - is the name of the destination dataset; e.g. EmployeeDataset

The actual Lookup expression in my report is:

=Lookup(Fields!EmployeeKey.Value, Fields!EmployeeKey.Value, Fields!EmployeeName.Value, "EmployeeDataset")

The following is a portion of the output of my report:

The EmployeeName column has the Lookup expression; the query for the EmployeeDataset concatenates the Last Name and First Name.

---

LookupSet Function

I will use the following tables from the AdventureWorksDW2008R2 database in my sample report:

Assume that my report contains a Dataset for each table; e.g.ProductCategory and ProductSubcategory. I want to print each Product Category and include the list of its Product Subcategories, all on the same output line.

The LookupSet function has the following parameters:

LookupSet(source_expression, destination_expression, result_expression, dataset)

The parameters are the same as Lookup function described above. However, the output of the LookupSet function (i.e. the expression_result) is the list of matching values in the destination dataset. In this case I want to get the list of EnglishProductSubcategoryNames for a given ProductCategoryKey in the ProductSubcategory dataset. In order to print a list of items inside of a single cell in a report, I will use the Join function which will transform the list of items into a single string and put in a comma to delimit each item. The following expression performs the LookupSet function and applies the Join:

=Join(LookupSet(Fields!ProductCategoryKey.Value,Fields!ProductCategoryKey.Value,Fields!EnglishProductSubcategoryName.Value,"ProductSubcategory"), ",")

The sample output from my report is shown below:

I used a PlaceHolder instead of a TextBox for the Product Subcategory Names. This opens up the possibility of rendering HTML instead of just plain text.

---

MultiLookup Function

I will use the following tables from the AdventureWorksDW2008R2 database in my sample report:

I want to create a report where the user can select one or more Sales Reasons from DimSalesReason then see which sales orders had any items that are tagged with any of these sales reasons.

The MultiLookup function has the following parameters:

Multilookup(source_expression, destination_expression, result_expression, dataset)

The parameters are the same as the previous lookup functions except that the first parameter is actually a list of values. The report will have a Sales Reason parameter that allows multiple selections; the selected Sales Reasons will be printed in the report header. The report will list each sales order that contains any item that matches one or more of the selected Sales Reasons. My sample report is shown below:

There are two lookup expressions used in this report; I'm using the following MultiLookup to retrieve the Sales Reason Name for each Sales Reason selected in the multi-select report parameter:

=Join(MultiLookup(Parameters!SalesReason.Value, Fields!SalesReasonKey.Value, Fields!SalesReasonName.Value, "SalesReason"), "
")

Note that since the Sales Reason parameter allows multiple selections, Parameters!SalesReason.Value is a list containing every value selected by the user. The above expression is rendered in a PlaceHolder that has the markup type set to HTML so I can show each Sales Reason on a separate line.

The second lookup expression is a LookupSet that retrieves the list of Sales Reasons for all items on the order:

=Join(LookupSet(Fields!SalesOrderNumber.Value, Fields!SalesOrderNumber.Value, Fields!SalesReasonName.Value, "SalesOrderReasonBridge"), ", ")

The SalesOrderReasonBridge dataset is using the following query which gets every Sales Order that has any items tagged with any of the Sales Reasons selected for the report and also grabs the Sales Reason Name:

select distinct f.SalesOrderNumber,f.SalesReasonKey,r.SalesReasonName  from dbo.FactInternetSalesReason f  join dbo.DimSalesReason r on r.SalesReasonKey = f.SalesReasonKey  where f.SalesReasonKey IN (@SalesReason)  order by SalesOrderNumber  

Difference B/w ReportServerDB and ReportServerTempDB In SSRS

Difference between SQL Server 2008 and SQL Server 2012

Difference between SQL Server 2008 and SQL Server 2012

Difference between SQL Server 2008 and SQL Server 2012

S.No	SQL Server 2008	SQL Server 2012
1	Maximum number of  concurrent connections:  The Maximum number of concurrent connections to SQL Server 2008 is 32767.	Maximum number of  concurrent connections:   SQL server 2012 has unlimited concurrent connections.
2	Precision used for spatial calculations: The SQL Server 2008 uses 27 bit bit precision for spatial calculations.	Precision used for spatial calculations:  The SQL Server 2012 uses 48 bit precision for spatial calculations
3	TRY_CONVERT() and FORMAT() functions:   TRY_CONVERT() and FORMAT() functions are not available in SQL Server 2008	TRY_CONVERT() and FORMAT() functions:    TRY_CONVERT() and FORMAT() functions are newly included in SQL Server 2012
4	ORDER BY Clause with  OFFSET / FETCH options:  ORDER BY Clause does not have OFFSET / FETCH options as in SQL Server 2012	ORDER BY Clause with OFFSET / FETCH options:  ORDER BY Clause now have OFFSET / FETCH options to use paging to show required rows per page in applications and allow the user to scroll through each page of results rather than download the entire set In the sample query below, SQL Server would return 10 records beginning with record 11. The OFFSET command provides a starting point for the SELECT statement in terms of paging, and the FETCH command provides how many records to return at a time. SELECT BusinessEntityID, FirstName, LastName FROM Person.Person ORDER BY BusinessEntityID OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;
5	Code Name:  SQL Server 2008 is code named as Katmai.	Code Name:  SQL Server 2012 is code named as Denali
	In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition.	In SQL Server 2012,support for server auditing is expanded to include all editions of SQL Server.
7	Sequence Object:  Sequence is not available in SQL Server 2008	Sequence Object:  Sequence is included in SQL Server 2012.Sequence is a user defined object that generates a sequence of a number. Here is an example using Sequence. /****** Create Sequence Object ******/ CREATE SEQUENCE MySequence START WITH 1 INCREMENT BY 1; /****** Create Temp Table ******/ DECLARE @Person TABLE ( ID int NOT NULL PRIMARY KEY, FullName nvarchar(100) NOT NULL ); /****** Insert Some Data ******/ INSERT @Person (ID, FullName) VALUES (NEXT VALUE FOR MySequence, 'Umar Ali'), (NEXT VALUE FOR MySequence, 'John Peter'), (NEXT VALUE FOR MySequence, 'Mohamed Iqbal'); /****** Show the Data ******/ SELECT * FROM @Person; The results would look like this: ID FullName 1 Umar Ali 2 John Peter 3 Mohamed Iqbal
8	Full Text Search Capability:  The Full Text Search in SQL Server 2008 does not allow us to search and index data stored in extended properties or metadata.	Full Text Search Capability:  The Full Text Search in SQL Server 2012 has been enhanced by allowing us to search and index data stored in extended properties or metadata. Consider a PDF document that has "properties" filled in like Name, Type, Folder path, Size, Date Created, etc. In the newest release of SQL Server, this data could be indexes and searched along with the data in the document itself. The data does have to be exposed to work, but it's possible now.
9	BISM Model:  Analysis Services in SQL Server does not have BI Semantic Model (BISM) concept.	BISM Model:   Analysis Services will include a new BI Semantic Model (BISM). BISM is a 3-layer model that includes: Data Model Business Logic Data Access BISM will enhance Microsoft's front end analysis experiencing including Excel, Reporting Services and SharePoint Insights. Microsoft has said that BISM is not a replacement for the current BI Models but more of an alternative model. In simple terms, BISM is a relation model that includes BI artifact such as KPIs and hierarchies.

Moving the Report Server Databases to Another Computer

Moving the Report Server Databases to Another Computer

You can move the report server databases that are used in a current installation to a SQL Server Database Engine instance that is on a different computer. Both the reportserver and reportservertempdb databases must be moved or copied together. A Reporting Services installation requires both databases; the reportservertempdb database must be related by name to the primary reportserver database you are moving.

Moving a database does not effect scheduled operations that are currently defined for report server items.

Schedules will be recreated the first time that you restart the Report Server service.

SQL Server Agent jobs that are used to trigger a schedule will be recreated on the new database instance. You do not have to move the jobs to the new computer, but you might want to delete jobs on the computer that will no longer be used.

Subscriptions, cached reports, and snapshots are preserved in the moved database. If a snapshot is not picking up refreshed data after the database is moved, clear the snapshot options in Report Manager, click Apply to save your changes, re-create the schedule, and click Apply again to save your changes.

Temporary report and user session data that is stored in reportservertempdb are persisted when you move that database.

SQL Server provides several approaches for moving databases, including backup and restore, attach and detach, and copy. Not all approaches are appropriate for relocating an existing database to a new server instance. The approach that you should use to move the report server database will vary depending on your system availability requirements. The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, but you must run Transact-SQL commands to perform the operations. Copying the database is not recommended (specifically, by using the Copy Database Wizard); it does not preserve permission settings in the database.

Important 
The steps provided in this topic are recommended when relocating the report server database is the only change you are making to the existing installation. Migrating an entire Reporting Services installation (that is, moving the database and changing the identity of the Report Server Windows service that uses the database) requires connection reconfiguration and an encryption key reset. For more information about migrating a Reporting Services installation, see Migration (Reporting Services).


Detaching and Attaching the Report Server Databases
--------------------------------------------------------------------------------

If you can take the report server offline, you can detach the databases to move them to the SQL Server instance you want to use. This approach preserves permissions in the databases. If you are using a SQL Server 2008 database, you must move it to another SQL Server 2008 instance. After you move the databases, you must reconfigure the report server connection to the report server database. If you are running a scale-out deployment, you must reconfigure the report server database connection for each report server in the deployment.

Use the following steps to move the databases:

1.Backup the encryption keys for the report server database you want to move. You can use the Reporting Services Configuration tool backup the keys.

2.Stop the Report Server service. You can use the Reporting Services Configuration tool to stop the service.

3.Start SQL Server Management Studio and open a connection to the SQL Server instance that hosts the report server databases.

4.Right-click the report server database, point to Tasks, and click Detach. Repeat this step for the report server temporary database.

5.Copy or move the .mdf and .ldf files to the Data folder of the SQL Server instance you want to use. Because you are moving two databases, make sure that you move or copy all four files.

6.In Management Studio, open a connection to the new SQL Server instance that will host the report server databases.

7.Right-click the Databases node, and then click Attach.

8.Click Add to select the report server database .mdf and .ldf files that you want to attach. Repeat this step for the report server temporary database.

9.After the databases are attached, verify that the RSExecRole is a database role in the report server database and temporary database. RSExecRole must have select, insert, update, delete, and reference permissions on the report server database tables, and execute permissions on the stored procedures. For more information, see How to: Create the RSExecRole.

10.Start the Reporting Services Configuration tool and open a connection to the report server.

11.On the Database page, select the new SQL Server instance, and then click Connect.

12.Select the report server database that you just moved, and then click Apply.

13.On the Encryption Keys page, click Restore. Specify the file that contains the backup copy of the keys and the password to unlock the file.

14.Restart the Report Server service.

Backing Up and Restoring the Report Server Databases
--------------------------------------------------------------------------------

If you cannot take the report server offline, you can use backup and restore to relocate the report server databases. You should use the ‘Copy_Only’ option. Beginning in SQL Server 2008, SQL Server Management Studio supports copy-only backups. After you restore the databases, you must configure the report server to use the database on the new server instance. For more information, see the instructions at the end of this topic.

Using BACKUP and COPY_Only to Backup the Report Server Databases
When backing up the databases, set the COPY_OnlyL option and backup type to Full.

Note 
Be sure to back up both the ReportServer and ReportServerTempDB databases as well as their associated log files.


For more information on using SQL Server Management Studio to backup a database, see How to: Back Up a Database (SQL Server Management Studio)

Using RESTORE and MOVE to Relocate the Report Server Databases
When restoring the databases, you can Use the RESTORE WITH NORECOVERY argument to perform the initial restore; this keeps the database in a restoring state, giving you time to review log backups to determine which one to restore. You would then repeat the RESTORE operation but use the RESTORE WITH RECOVERY argument.

Note 
Be sure to restore both the ReportServer and ReportServerTempDB databases as well as their associated log files.


For more information on using SQL Server Management Studio to restore a database, see How to: Restore a Database Backup (SQL Server Management Studio)

How to Configure the Report Server Database Connection
1.Start the Reporting Services Configuration tool and open a connection to the report server.

2.On the Database page, click Change Database. Click Next.

3.Click Choose an existing report server database. Click Next.

4.Select the SQL Server that now hosts the report server database and click Test Connection. Click Next.

5.In Database Name, select the report server database that you want to use. Click Next.

6.In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next.

7.Click Next and then Finish.

Note 
A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool. If you use alternate approaches (specifically, if you use the rsconfig.exe command prompt utility) to configure the connection, the report server will not be in a working state. You might have to write WMI code to make the report server available. For more information, see Reporting Services WMI Provider.

Custom Assemblies in SQL SERVER Reporting Services(SSRS)

Custom Assemblies in SQL SERVER Reporting Services(SSRS)

Create custom assembly


1) Open Visual Studio 2008 and create a new Project.

2) The New Project window will open; Select the Visual C# node; from the right pane select Class Library. Complete the following:

In the Name field enter "rsCustomAssembly"

In the Location field enter "C:\Visual Studio 2008\Projects"

In the Solution field enter "rsCustomAssembly"

Click OK when you are done

3) Delete the default class.cs file created. When prompted click OK.

4) Add a new class by right clicking on the solution name and selecting Add >> Class...

5) Name the new class Utilities.

6) Enter the code below into the class file and save it.

using System;

using System.Collections.Generic;

using System.Linq;

using System.Text;



namespace rsCustomAssembly

{

public class Utilities

{

///

/// Sample concatenate string method; not intended for production use.

/// Note: the string object already provides a concatenate method - string.concat(object o)

///

///

///

///

public static string Concat(string str1, string str2)

{

return str str1+ " " + str2;

}



public static string HelloReportingServices()

{

return "Reporting Services";

}

}

}

Your screen should look similar



7) From the top menubar you will find Build; Click this and select rsCustomAssembly from the list. The bottom status bar should presenet Build succeeded. If not, check your code again to make sure it follows the description above.

Copy the assembly to the Visual Studio Private assemblies and Report Server bin

You must copy you assembly (.dll) to the report designer and report server folders before you can use the assembly in Reporting Services. You can find your assembly by locating the bin folder in your project. For example: C:\Visual Studio 2008\Projects\rsCustomAssembly\rsCustomAssembly\bin\Debug

Note: the location is dependent on your install

For Report Designer: C:\Program Files\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies

For Report Server: C:\Program Files\Microsoft SQL Server\MSRS10.MSSQLSERVER\Reporting Services\ReportServer\bin

You have completed with this step.

Create a new Report

This step we will perform the following:

Create a new report

Reference the custom assembly

Add a new textbox to the report designer with a custom expression

1) Open BIDS and create a new project, like you did in 1) under "Create custom assembly".

2) Under the Project Type pane, select Business Intelligence Projects; from within the Templates pane, select Report Server Project and complete the following:

For the Name field enter SampleReport

For the Location field enter C:\Visual Studio 2008\Projects

For the Solution field enter SampleReport

Click OK

3) From the Solution Explorer, right click on the Reports folder and select Add >> New Item

The Add New Item window will open



4) From within the Templates pane, select Report and leave the default name of Report1.rdl

5) Click Add

Reference the Assembly

1) From the top menu bar select Report >> Report Properties

Note: if you don't see the Report menu item, try placing your cursor anywhere in the design view; you should then see the menu item.

2) The Report Properties window opens. From within the left pane, select References.

3) Click Add under "Add or remove assemblies".

4) Click the ellipsis right of the text box. The Add Reference window will open.

5) Select the Browse tab and navigate to the Report Server bin location, where you copied your .dll, as described earlier in this article under Copy the assembly to the Visual Studio Private assemblies and Report Server bin

you should see a similar value in the reference text box

6) Click OK to save and close the Report Properties window.

Add Expression and run report

1) Drag and drop a Text Box from the Toolbox pane on the left of your design pane. Strech the text box so you have some room for text.

2) Right click the text box you just added and select Expression

3) For the expression value enter :

=rsCustomAssembly.Utilities.Concat("Reporting", "Services")

4) Click OK to save and close the Expression window.

5) To the right of the Design tab, click Preview. You should see the following:

Save your work. You have completed referencing a custom assembly in SQL Server Reporting



SQL SERVER Reporting Services: Create and Call a Custom Assembly



namespace MyAssembly

{

public class Colors

{

public string PercentageToColor(double percentage)

{

string returnValue = string.Empty;



if (percentage < 20)

{

returnValue = "red";

}

else if (percentage < 80)

{

returnValue = "blue";

}

else

{

returnValue = "green";

}



return returnValue;

}

}

}

Once you’ve compiled your class you need to copy the resulting assembly to the directory in which it is accessible from within your report:

· To use it in the report designer, you need to copy it to: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies

· To make it available for reports that have been deployed to the report server, you need to copy it to: C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin



Before you can access your custom assembly you have to reference the assembly: Open the report properties (Menu: Report – Report Properties) and select the References tab.

Browse to your assembly and define a Class Name and an Instance Name. (The Class Name and the Instance Name or only for non-static methods). Make sure to prefix your class name with the assembly name.

Now you can call the methods in your assembly from your report, using an expression:

· To call a static method: =..

· To call an instance method: =Code..

So in our example this would be: =Code.TestColor.PercentageToColor(Fields!Percentage.Value)

That's it! You've just created and called a custom assembly from your report...

In one of my future articles I will explain how to pass parameters to the class constructor and I will also tell something about SSRS and .Net Permissions.

Source: ssrstips

Report Functions

Using Report Functions in Expressions (Reporting Services)

SQL Server 2005

11 out of 38 rated this helpful - Rate this topic

Updated: 15 September 2007

You can use built-in functions within expressions to manipulate the data within report items, properties, and other areas in the report. Built-in functions are used to aggregate data in datasets, data regions, and groups, and return other data.

Aggregate Functions

---

The following table describes the aggregate functions that are supported by Reporting Services. You can use aggregate functions in expressions for any report item. All data used for an aggregate calculation must be the same data type. To convert data that has multiple numeric data types to the same data type, use conversion functions like CInt, CDbl or CDec. For more information, see Type Conversion Functions.

CInt Example

The following example uses the CInt function to convert a value to Integer.

VB

Dim aDbl As Double 
Dim anInt As Integer
aDbl = 2345.5678
' The following line of code sets anInt to 2346.
anInt = CInt(aDbl)

Function	Description
Aggregate	Returns a custom aggregate of the specified expression, as defined by the data provider.
Avg	Returns the average of all non-null values from the specified expression.
Count	Returns a count of the non-null values from the specified expression.
CountDistinct	Returns a count of all non-null distinct values from the specified expression.
CountRows	Returns a count of rows within the specified scope.
First	Returns the first value from the specified expression.
Last	Returns the last value from the specified expression.
Max	Returns the maximum value from all non-null values of the specified expression.
Min	Returns the minimum value from all non-null values of the specified expression.
RowNumber	Returns a running count of all rows in the specified scope.
RunningValue	Uses a specified function to return a running aggregate of the specified expression.
StDev	Returns the standard deviation of all non-null values of the specified expression.
StDevP	Returns the population standard deviation of all non-null values of the specified expression.
Sum	Returns a sum of the values of the specified expression.
Var	Returns the variance of all non-null values of the specified expression.
VarP	Returns the population variance of all non-null values of the specified expression.

Scope

Each aggregate function uses the Scope parameter, which defines the scope in which the aggregate function is performed. A valid scope is the name of a grouping, dataset, or data region. Only groupings or data regions that directly or indirectly contain the expression can be used as a scope. For expressions within data regions, Scope is optional for all aggregate functions. If you omit the Scope parameter, the scope of the aggregate is the innermost data region or grouping to which the report item belongs. Specifying a scope of Nothing sets the scope to the outermost data region to which the report item belongs.

For expressions outside of data regions, Scope refers to a dataset. If a report contains more than one dataset, Scope is required. If a report contains only one dataset and Scope is omitted, the scope is set to the dataset. You cannot specify the Nothing keyword for report items outside of a data region.

You cannot use the Scope parameter in page headers or footers.

Other Functions

---

Reporting Services provides the following additional aggregate functions that you can use within expressions. For a complete list of the types of functions available when expressions are evaluated, see Creating Expressions in Reporting Services.

Function	Description
InScope	Indicates whether the current instance of an item is within the specified scope.
Level	Returns the current level of depth in a recursive hierarchy.
Previous	Returns the previous instance from the specified scope.