Dan English's BI Blog

Welcome to my random thoughts in regards to Business Intelligence, databases, and other technologies

Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration

Posted by denglishbi on July 22, 2011

Update (7/22/2011): Added a note about an IE Security setting for SharePoint menus at the end of the post.  I will add a PDF of this posting either tonight or over the weekend if anyone is interested in that.

Update (7/24/2011): Added PDF download of the posting for printing, offline, and eReader availability at end of posting in the File Download section.

With the recent release of the SQL Server Denali CTP3 there have been a few changes in the installation process.  One of those is with how the SharePoint integration configuration works.  There was new post yesterday in regards to this process Getting up and running with SQL Server Denali for Business Intelligence (Crescent and PowerPivot in CTP3).  In this post is goes through the steps assuming that you have installed SharePoint 2010 first.  What if you are like myself and installed all of the SQL Server components (SQL Server ‘Denali’ CTP3 Install Experience), including Reporting Services, before SharePoint 2010?  Well the process is a little different because since you didn’t have SharePoint installed first the integration process will not be completed by just installing SharePoint 2010 next.  There are a few steps you can take and I am going to go over the PowerShell option that I took through with the help of some documentation I was provided from Microsoft with previous CTP testing.

So here are the steps that I went through to get this working (this was done on a Windows 2008 R2 VM):

  1. Installed all of the SQL Server Components (installed Multidimensional SSAS and just installed the SSRS and did not configure it) – SQL Server ‘Denali’ CTP3 Install Experience
    image
  2. Next I went through the installation process again and installed an additional SSAS named instance in the Tabular mode.
  3. Then I installed SharePoint 2010:  pre-reqs and then SharePoint (but did not run the configuration wizard – unchecked the box and closed the process down after the install).  I had to actually run the pre-req install twice and had to do a reboot prior to the SharePoint install as I recall.
    image
  4. Then I installed the two SharePoint 2010 SP1 files – one for Foundation and the other for Office Server (this is going to be required to get SharePoint to work with Denali for the database engine).  I believe a reboot was required after this as well. http://support.microsoft.com/kb/2510766
  5. I then did the SQL Server install again to install PowerPivot for SharePoint creating another named instance of SSAS for PowerPivot.
  6. The next step can be done a few different ways and I picked the new PowerPivot for SharePoint Configuration Tool (need to ensure your pass phrase meets the security requirements) – this option is available in the Tools page of SQL Installation or in the Configurations folder in your SQL Server install folder in the Start Menu. This process will configure the PowerPivot integration, activate it, and create a PowerPivot SharePoint Site Collection.
  7. I then installed the ADO.NET Data Services for .NET 3.5 SP1which is required if you want to use PowerPivot against an Atom Feed in SharePoint.  A reboot is required after this install.
  8. Setup the SSRS service and application (since I installed SQL and SSRS prior to SharePoint the Integration with the add-in did not complete – because no SharePoint).
    1. Run the Microsoft SharePoint 2010 Management Shell as Administrator
    2. Type the following PowerShell command> Install-SPRSService
    3. Type the following PowerShell command> Install-SPRSServiceProxy
    4. Now to start the service type the following PowerShell Command> get-spserviceinstance -all |where {$_.TypeName -like “SQL Server Reporting*”} | Start-SPServiceInstance
      image
    5. Verify that the service is started in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Services on Server and now look for the SQL Server Reporting Services Service to see that it has now been setup and is runningimage
    6. Now we are going to create a new SQL Reporting Services Application in SharePoint Central Administration –> Application Management –> Service Applications –> Manage Service Applications and create a new SQL Server Reporting Services Application
      image
    7. Specify a name for the Application, create a new application pool, and specify the database server name.  Down below you will also need to select the web application to associate this application with and I used the Windows Authentication for the database authentication
      image
    8. Now we should be all set and you should see the new application that you just created
      image
  9. Now you are off to the races and can create a Reports Library in SharePoint and add the content types including the new BISM Connection File for using to create Crescent Reports! You will need to install Silverlight to get the SharePoint Create window and this will be needed for Crescent as well since it is a Silverlight application.  If you do not see the Reports Library option you will want to make sure that the Enterprise Features are enabled not only at the Site Collection Level, but also at the Site level. I left out the Report Builder Model content type because from here on out it is all about the BI Semantic Model (BISM)
    image
    image
  10. Now you can create a new BISM Connection File and or Report Data Source to get started with Crescent (shown below in the respective order). After you create these you will not see them in the library.  In order to see them you can go to the Library tab in the Ribbon and select the ‘All reports and dashboards’ option for the view and then you will see the connection and data source files you have created (by default it simply displays just the reports so it is not cluttered)
    image
    image
    image
  11. Now you can launch Crescent from the context menu from the connection and data source files you have created.  In order to create these connection files and data sources I installed and deployed the Denali Samples from CodePlex.  There is one issue with the Tabular project that you need to be aware of when you want to deploy this that has been documented here – Problems with the SSAS Tabular Model Projects Denali CTP3 samples (according to the site the samples have been fixed, so if you haven’t downloaded them you should be okay).
    image
    image

So there you go, you are off to the races.  You know have SSRS in the new SharePoint Integrated Mode setup running as a SharePoint Application (no longer running as its own Windows Service, completely setup and managed by SharePoint now) and you are creating awesome reports with Crescent.

Enjoy!

NOTE: If you are having an issue in say Step 9 and you are not getting menu options in SharePoint you need to go into your IE Security Settings and enable the Active Scripting.  Once you do that you should be able to use the menu options in SharePoint.

image

File Download:

image

About these ads

12 Responses to “Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration”

  1. [...] SharePoint 可參照 Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration This entry was written by byronhu, posted on 2011 年 07 月 15 日 at 01:44:46, filed under SQL [...]

  2. Alan said

    Great write-up Dan. I am finally getting around to working with CTP3 and like you did all my SQL installations before my SharePoint install. You probably saved me a few hours of troubleshooting and web searching. Thanks.

  3. [...] Posts Using SharePoint List data for Reporting Services (SSRS) parameter sourceConfiguring SQL Server Denali Reporting Services SharePoint 2010 IntegrationFree Online PerformancePoint Services 2010 Training VideosUsing Reporting Services Report (SSRS) [...]

  4. nedeljko said

    Thanks,
    Great article.

  5. Berkay said

    Awesome.

    Thanks

  6. Tofig said

    Hi denglishbi,

    I followed your intructions, however I am missing the “BISM Connection File” content type.
    Do you have any suggestion on what I am missing?
    Thanks in advance

    • denglishbi said

      Is it possible that you do not have the PowerPivot integration setup? If so, you will still be able to setup a Report Server Data Source (rsds) connection file and you can use that to connect to your data model. You will just have to enter in the connection string as shown in my example.

      • Tofig said

        Yes indeed, I did not installed PowerPivot yet.
        I though BISM is also a Report Server feature…

        So I will actually have BISM only after installing PowerPivot or otherwise use Report Data Source and Report Builder Report, correct?

      • denglishbi said

        You will only get the BISM Connection File content type if you have PowerPivot setup. You can use the Report Server Data Source option instead and when you create the connection you will use the data source type of Microsoft Business Intelligence Semantic Model as your option and then you will have to provide the Connect String just like in my example provided above. When and if you do add the PowerPivot integration into SharePoint you will then be provided with the alternate content type. With the rsds connections you have to use the context menu to launch Power View versus simply clicking on the BISM connection file link.

  7. Jason said

    Reblogged this on Shared Pointing and commented:
    Great guide pointing out that if you should happen to install SQL Server Reporting Services 2012 before installing SharePoint, you must then let SharePoint 2010 know it’s there and install the service. Thanks Dan!

  8. […] Configuring SQL Server Denali Reporting Services SharePoint 2010 Integration  […]

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
Follow

Get every new post delivered to your Inbox.

Join 1,754 other followers

%d bloggers like this: