Thursday, October 4, 2012

Converting a SQL Analysis Report from Native Mode to SharePoint Integration Mode

This will cover creating a new data connection and report library, taking an existing report from Reporting Services – Native Mode that uses a Shared Connection, and migrating the existing report to the new SharePoint location

What you’ll need:
  • SharePoint Report Library – To hold your published reports
  • SharePoint Data Connection Library – To hold your shared data connections
  • A report you want to save
  • Your data source(s) – you can find these within the report references
  • SQL Server Report Builder – This is a local report building client available here:
Let’s start within SharePoint.  Enter the SharePoint site you want visitors to consume reports from.

Step 1: Setup your Report Library in SharePoint

If you don’t have a Report Library to save your reports to, these steps will help you set one up
  • On your SharePoint site, choose Site Actions and More Options
  • Filter by Library
  • Choose Report Library
  • Provide a name for your report library.  If you cant decide on one, just go with “Reports”
  • Click Create

Step 2: Setup your Data Connection Library in SharePoint

Similar to the steps above, we want to create another library on our site, only this time to hold our data connections that our reports use.  This way, we can share data connections with other reports.
  • On your SharePoint site, choose Site Actions and More Options
  • Filter by Library
  • Choose Data Connection Library
  • Provide a name for your report library, if you cant think of one, go with Report Data Connections
  • Select More Options
  • (optional) Under Navigation, choose No.  This will suppress the Data Connection Library from showing on the left navigation of your site.  While optional, typically there’s no reason for your users to need quick access to your data connections.
  • Click Create

Once you have your Data Connection Library created, we just want to do some housework while we can, to keep things neat and tidy.
  • Choose the Documents tab underneath Library Tools in the Ribbon
  • Choose New Folder
  • Create a folder called Data Sources. 
  • Repeat this process, and create a folder’s called
    • Datasets
    • ODCs
    • Report Parts
  • You may not need all of these folders to convert your report, but now you have the structure in this library to hold all data sources, data sets, office data connections (like connections from Excel) and Report Parts going forward

Lastly, we need to enable some file types in our connection library, to allow us to create new data files within SharePoint

  • While within your Data Connection Library, click Library Settings from the Library tab underneath Library Tools in the Ribbon
  • Choose Advanced Settings
  • For Content Types, choose Yes to allow management of content types
  • Click OK
  • Further down on the Library Settings, under Content Types choose Add from existing content types
  • From the Available Site Content Types, choose:
    • Report Builder Model
    • Report Builder Report
    • Report Data Source
  • Click Add
  • Click OK

Step 3: Get your Report

The above steps are a one-time deal, once you have your libraries created, you have a spot to always store this data.  Now, we can look at “converting” your report from Native Mode into SharePoint.

  • Go to your existing report server (typically http://<reportserver>/Reports)
  • Browse to the appropriate directory that contains your report.  Here, Ive navigated down one level to choose a Claims Paid report
  • Hover over the report, click on the arrow, and choose Download..
  • Once given the option, choose to Save your report.  Obviously, make note of where you saved this report.

Step 4: Gather your report properties to “convert” your report

Have you noticed I put the term convert in quotes a few times?  Its because were really not converting anything, more or less just saving it to a new location.  To perform the rest of the steps, you need to have installed SQL Server Report Builder, linked to at the top of this article

  • Browse to the location where you saved your report, and open the rdlc file. 
  • When prompted to connect to a report server, just hit Cancel
  • In the Report Data section on the left, expand the Data Sources section, to expose the data sources required for this report.
    • All were going to do is update the connection properties for this report.  We don’t want to delete it, because doing so would break all references to the Dataset’s associated with this connection
  • Take note of the name(s) of your Data Source(s).  Here, I only have 1 DataSource
  • Return to your Report Server
  • Browse to your Data Connections.
  • Find the Data Connection identified in the previous steps, hover over the Data Connection, and choose Manage
  • Within the Properties section, view\copy the Connection String used as a data source
  • Under the Connect Using section, take note of how\what credentials are used. 

Step 5: Create your Data Source in SharePoint

  • Now, return to your SharePoint Data Connection Library, and choose your Data Sources directory we created earlier
  • In the Ribbon, under Documents, choose the New Document Arrow, and choose Report Data Source

 We want to mimic the settings you found earlier on your Report Server
  • Provide a Name for your Data Source – preferably something that matches the name of the data source you’re re-creating
  • Specify your Data Source Type
  • Enter your Connection String
  • Specify your Credentials
  • Choose to Enable the data source

Basically, the settings for the new data source are basically the same as what was on your report server from Step 4
  • Click Test Connection to make sure your connection is properly setup
  • Click OK to save this data source to your report library

Step 6: Associate your new Data Source

Return to your report in SQL Server Report Builder, now we can associate our new data source with the rdl file.
  • Right Click your Data Source, and choose Data Source Properties
  • With the Use a shared connection or report model enabled, click the Browse… button
  • Within the Name textbox, enter the URL for the path to your Report Connection Library.  Here, I know that my report connection library is located at Data Connections

  • Next, choose Data Sources
  • Lastly, choose your rsds data source you created and click Open
  • Once complete, you have now associated your shared data source with the rdl file.  Click OK
  • Under Views in the Ribbon, choose Run to ensure that your report now works with your newly associated data connection

If the report is working properly, you should have the option to edit your parameters at the top of the report (if you have parameters).  Enter your data, and choose View Report

If there is an issue with connecting to your data source, you will be prompted with an error message.  Resolve any errors, and try previewing the report again.  If it’s an issue with the data source authenticating the user at run time, and you think you resolved the issue, you may have to Refresh the report, by choosing the refresh option.

Step 7: “Convert” (Save) Your Report

All the hard work is done, all we need to do now is just save our report to SharePoint

  • Still within SQL Server Report Builder, click the report button in the upper left hand corner
  • Choose Save As
  • In the Name field, enter the URL to the Report Library you created in Step 1.  Here, I know that my report library is located at Library
  • In the Name Field, enter the name of your Report. 
    • You can keep it consistent with your existing reports if you wish, and re-enter the same name as the report you are converting.  So, here I chose to enter Claims Paid.
  • Click Save
  • Return to SharePoint, and enter your Report Library
  • Choose your report
  • Validate  that is runs within SharePoint

No comments:

Post a Comment