SRS, Microsoft SharePoint, and Dashboards

This post summarizes the process of creating a SharePoint portal using SRS reports, highlighting several issues that we have experienced, and how we solved them. This is a work in progress�if you see any errors, or would like to add any details, please drop a note in the comments or email me. Special thanks to Dena Harrell, Chris Rogers, and Chris Vanzant for your contributions.

To start, you will need to have Windows Sharepoint Services (WSS) 3.0 or Microsoft Office SharePoint Server (MOSS) 2007 installed. WSS 3.0 or SharePoint 2007 is required because it has added functionality that will properly display subreports launched from a hyperlink from a dashboard report. Since SharePoint web parts don't have a "back" button, we want any linked reports to pop up in a separate window or tab when launched so they don't mess up the layout of the dashboard.

Microsoft has an excellent comparison of SharePoint versions that can help you decide which flavor of SharePoint is the best fit for your needs.

Once you have SharePoint installed, here is the process for setting up your portal:

Step 1: Download and install the SRS 2005 web part

Update--do not download the report viewer from Microsoft.  This is a different web part for 3.0 that integrates SRS with Sharepoint; however, it will change your SRS database to sharepoint integrated mode, which is not supported by CRM

Copy the RSWebParts.cab to a folder on the SharePoint server. The .cab is installed with Reporting Services. By default, it is located in the C:\Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint folder. You can copy it to any folder on the SharePoint server, and then delete it later after you install the Web Parts. use the command prompt from the folder where STSADM.exe is located (By default c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN):

STSADM.EXE -o addwppack -filename "C:\ Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab" -globalinstall

By executing this from the command prompt, it usually sets the security permissions for you, without having to mess with the web.config files.

See the full instructions for installing the web part here.

One step that is not very clear in the instructions is that after you install the web part, you may still need to manually add the web part to a web part gallery before you will be able to add it to your portal.

Trouble spot: This is where there can be a potential for problems. If the web parts upload successfully, you are done with this step; however, if there are any error messages, or if the web parts are "grayed out," you need to do a few more things. We have found this to especially be a problem if a prior version of Sharepoint is installed, since the web parts are by default installed to the root of the main SharePoint site. Here is how to resolve this issue:




   





1. 
   
   Update the Sharepoint 3.0 web.config with the following setting:
         <SafeControl Assembly="RSWebParts, Version=8.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.ReportingServices.SharePoint.UI.WebParts" TypeName="*" Safe="True" />
   
   

   This modification will tell WSS 3.0 that the web part is safe, so it will allow it.



2. 
   
   Configure Sharepoint to work with Kerberos security 
   
   
   
   
   1. go to the Sharepoint 3.0 Central Administration.
   
   
   2. Then go to  Application Management > Authentication Providers > Edit Authentication
   
   
   3. Change it to Negotiate (Kerberos) if not already selected.
   
   

   See this document for details on configuring Sharepoint to work with Kerberos security




Step 2: Creating a new site





1. In Sharepoint, click"create"


2.  Under "sites" select "site or workspace"


3. Give your portal a title, description, and set up the URL


4. Select "Blank Site" template.


5. For permissions, select "use the same permissions as parent site"


6. In the "navigation" section, select yes if you want your new portal to be listed on the navigation bars of your main SharePoint site.


7. In "Navigation Inheritance," I recommend that you select "no." Otherwise, your new portal will have a link to all of your other SharePoint sites on the top nav bar.  



Step 3: Clean up your site




SharePoint will automatically add several placeholders and sections to the page. We don't want to display these items because screen real estate is at a premium since we are going to be integrating this site into Microsoft Dynamics CRM, and will have to have enough room for the CRM nav bar or the Outlook bar.




IMPORTANT�do not delete the HTML code that contains these items from your site�this will cause your pages to have errors, since SharePoint looks for these placeholders.





1. To edit the page, download the page in SharePoint Designer.


2. In Sharepoint designer, click file, open, then enter the URL of the SharePoint portal that you want to edit. SD will prompt you for your login credentials.


3. When the page is opened in SD, click View/Page/Split.


4. There are two main pages that you will want to edit. Default.masterpage, which contains the style and theme layout of the page, and default.aspx, which is your content portal page. You may need to hunt through the folders on the site to find the default.masterpage.


5. Select the area on your page that you want to edit. The code window will highlight the code for that part of the page.



There are two ways to hide an item:





1. Right click on the item, select properties, and change the isVisible property to "False"


2. Locate the TD tag for the item, and add style="display:none" to the tag



Example:  line 259 of default.masterpage is the table of links on the left side of the page.  It reads  <table class=ms-nav width=100% height=100% cellpadding=0 cellspacing=0>.  After changing this line to  <table class=ms-nav width=100% height=100% cellpadding=0 cellspacing=0 style="display:none">, the entire left side bar is gone.  Save the page when you are finished.




   




Step 4: Change the Column Width




The blank page template defaults to the left column at 70% width and the right column at 30% width.  We want our page to have uniform column width.  Let's change it.





1. In SharePoint Designer, open default.aspx.


2. Select the left column.  In the code (around line 62 in the blank page template), change the width from 70% to 50%.


3. Select the right column.  In the code (around line 67 in the blank page template), change the width from 30% to 50%.


4. Save the page when finished



Step 5: Save your site as a template




Back on your now bare SharePoint site, click on "Site Settings" on the right side of the screen.  Under "Look and Feel" column, select save as template.  By saving this site as a template, you will be able to select your naked page and quickly set up dashboard pages.




Step 6: Create a new site using your new dashboard template




Follow the instructions for step 2, substituting your new site template for the "blank page" template.




Step 7: Add Report Viewer Web Parts to the page





1. On the upper right side of the new page, click site actions button and select edit page to enter editing mode.


2. Click the "add a web part" button on either the left or right column


3. Select the "report viewer" web part from the gallery options.  If you don't see the report viewer web part, refer to step 1.


4. On the newly inserted report viewer web part, click the edit button and select "modify shared web part."  This will open the report viewer configuration window on the right side of the screen.


5. In the configuration window, enter the url for your web server.  Example: http://servername/reports


6. In the report path enter the folder and file name of the report. Example: foldername/filename (note--do not inclue the .rdl file extension).


7. For toolbar size, select "None," as this will eat up screen real estate.


8. Under the appearance settings, I recommend leaving the title as "report viewer."  We have found that the report viewer is prone to changing the title of reports back to "report viewer, so as a general rule we put the name of the chart at the top of the report and turning the title of the report viewer off.  To hide the title of the web part, change the chrome settings to "none."


9. Height and Width--for best results, size your SRS charts for the dashboard at a maximum of 4"X4".  Then select a fixed height and width on the report viewer appearance settings, and size it to a height of 4 inches and a width of 4 inches.


10. Hit "apply" at the bottom of the web part configuration window to apply your changes to the web part.  To add additional web parts, click another "add a web part" button.


11. When you are finished editing your page, click the "exit edit mode" on the right side of the screen.



Note--For best results, when you design your chart in SRS, put the legend on the bottom of the chart, since the width of the dashboard viewers will be small.




   




 

Comments

[Joel Lindstrom]

One small correction to step 1. Instead of installing the RSWebparts via the web part gallary, use the command prompt from the folder where STSADM.exe is located (By default c:\Program Files\Common Files\Microsoft Shared\web server extensions\12\BIN):

STSADM.EXE -o addwppack -filename "C:\ Program Files\Microsoft SQL Server\90\Tools\Reporting Services\SharePoint\RSWebParts.cab" -globalinstall

By executing this from the command prompt, it usually sets the security permissions for you, without having to mess with the web.config files.

[Joel Lindstrom]

share point,

The best way to get your content on the any of the Microsoft Community portals/msdn sites is to get involved in the community--forums, newsgroups, and provide original quality content on the blog. If you do, you can participate in the MVP program, which will give you an opportunity to get your blog featured.