Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Developing SQL Server Reporting Services Reports in Teams


By:   |   Updated: 2007-11-08   |   Comments   |   Related: > Reporting Services Best Practices

Problem
You know the old expression, "Be careful what you wish for, because it might come true!" That goes the minute your Reporting Services team grows from one person to any size larger. How do you manage the code? What if you are working on a multi-developer team project, all on the same reporting solution? If you are in a relatively small reporting team, perhaps it is not worth the overhead of a software-based version control system. So what do you do?

Solution
First, I'll say that, there is no one-size-fits-all solution. But if your team is relatively small, you can manage without a software solution for version control, so long as you have an adequate manual system in place. There needs to be person that plays the role of primary Portal Manager/Librarian. Ideally, there will also be a backup person for this role as well. The typical activities of the Portal Manager are similar whether you are using Native Mode (Report Manager) or Sharepoint Integrated Mode. These include the follow:

  • Designing the structure of folders, sub-folders, Sharepoint sites, & document libraries. This will be based on the security and functional needs of the Portal (see Part 1 of this series).
  • Publish production ready reports to the final production target locations.
  • Maintain (and protect) the "Official Code" library of files.
  • Advise report developers on portal capabilities and limitations. You will find that unless you have actually worked with the portal, there are a lot of misconceptions about it, even from the most technically oriented.
  • Ensure that adequate versions of code are saved according the version control requirements of your organization. (i.e. What are the legal requirements? Departmental requirements? If there are none, then you should organize a team discussion to determine what is at least reasonable.
  • Code backups: You may not be responsible for them, but you are responsible to ensure that someone is, and that someone is doing them!
  • Provide links to and from other (non BI) portals as needed.
  • Plan, plan, plan, baby! - You have got to think ahead. Image what your portal might look like in three months, six months, one year or more...then plan accordingly. You may have to enlarge the scope of the folder/site/library hierarchy. How tall or flat will it be.


Report Publishing Workflow

Below is the model I use that shows how to get code from the developer to a production site in a team environment.

You can see from the diagram, that this will work with any (reasonable) number of report developers and with a Portal Librarian acting as Manager of the "Official Code". Here's how it works.

Preparation - The Developer and Production code library must have the same data source (rds) files. As the librarian you will create and distribute those in advance before the developer begins the solution.

  1. The developer works within the Visual Studio report designer environment with RDL (and RDS) files in the designer "Solution". This exists as a set of files on a file system (RDL Code Library). When it's time to see the results on a web server in a secure, testing environment, the report developer publishes to their development folder or library on the Reporting Services web server. This can be a development server or a secure location on the production server. When the developer is happy with the results, they notify the Portal Librarian to publish their report to the Production site.
  2. The Portal Librarian should always download the report from the Developer's Published Site on the web server, not the file system. It could be that the develop wants to work some more with the original code on the file system. The file could be open, or in some partially edited state. For this reason, the developer communicates the exact location of the published code to the librarian. The Librarian adds the rdl file from the web server to the "Official" Production Code RDL Library (file system). This would be a separate Reporting Designer Solution apart from the developer's version. It should be kept safe and backed up and considered the "Official" source code.
  3. The Portal Librarian then publishes the "Official" source code to the Production Library where users can access the report.

 



How to download (.rdl) report files from the web server. (See step 2 in the workflow above).

From the Report Designer...
  • If there was a previously existing version of the report (which is likely), using Report Designer in the "Official Code" version, you must rename the report (or move it to another location). I like to append a prefix to the name like~backup20071101-ReportName.rdl.  This becomes my working version control system. The "~" character makes all the backup files sort together, and the reverse date code sorts them in sequence.
Native Mode

From the SQL Server Manager Console...
  • From the Object Explorer, click Connect.

  • Choose the target Reporting Server

  • Drill down to the needed report in the Developer's published site.

  • Right-Click the report and select Edit Report.

  • Save this to the Report Solution of the "Official Code" Solution (file system).
Sharepoint Integrated Mode

From the Sharepoint Document Library...

  • Locate the Developer's Published Site location.

  • Click on the report dropdown, then select Send To, then Download a Copy.


  • Save this to the Report Solution of the "Official Code" Solution (file system).
From the Report Designer...
  • Within the Report Designer of the "Office Code", right-click on Reports in the Solution Explorer and select Add/Existing Item. Then browser for the new report.
  • Click Add.
  • Publish the report to the Production site.



A word of advice on Sharepoint site hierarchies

In Sharepoint, I used to think a tall hierarchy was good. Not any more! My experience is that hierarchies change over time. Dependencies that you setup in a tall structure cannot be undone easily or perhaps at all. If it is questionable, err on the side of building sub-sites flat rather than in hierarchies.



Next Steps

  • Plan your Portal carefully.
  • Prepare enough testing environments so you are fluent with publishing to various locations.
  • Save versions as needed.
  • Communicate with team members and business owners about the Portal possibilities.
  • Review the previous Parts to this series on Portal Management.
  • Take a look at these other Reporting Services tips.

 



Last Updated: 2007-11-08


get scripts

next tip button



About the author
MSSQLTips author Rob Fisch Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools