Developing SQL Server Reporting Services Reports in Teams

By: Rob Fisch   |   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).

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.
  • Part 1 - Security
  • Part 2 - How to Show and Hide Report Objects
  • Part 3 - Building a Dashboard
• Take a look at these other Reporting Services tips.

About the author

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

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips