Steven Wright Blog

I have recently deployed a new reporting solution using SQL Server 2008 Reporting Services. I wanted to look into a solution to ensure that the first user, after reporting services had spun down its’ application pools, doesn't have to wait for them to be re-initiated. This so that I could avoid complaints about reporting services performance. I was looking for a simple solution to either stop reporting services clearing down the application pools or to find a way of speeding up the first request. I decided on an approach to speed up the first request made by a user. This was because I didn't want to make application settings changes in case other problems occurred. After searching for a method into speeding up reporting services application pools start-up I came across a useful PowerShell script. The script, which was for PowerShell V2, made use of the .Net class System.Net.WebRequest to make a request to the report manager. I made a copy of this script and applied some ...

In October 2009 I created 2 codeplex projects which I would to tell you about: SQL Server Reporting Services MSBuild Tasks (ssrsmsbuildtasks) A few years ago I created some tasks for MSBuild to help deploy reports for my project, details can be found here ,  since then my tasks have been re-used in a few other projects within my company.  However lately we have been doing a few projects which have made use of Reporting Services integrated mode with SharePoint, which meant that my tasks were unusable as they only work with a native mode report server. So I have been updating them to include support for an integrated mode report server.  Also before I added the integrated mode support I took some time to rewrite my original native mode tasks as well.  The re-writing of the tasks includes some improved understanding of MSBuild by making more use of Item Groups and metadata for setting of report server properties of the report items being deployed.  I plac...

I have been working with Microsoft SQL Server Reporting Services, since its first release, within many of my projects.  One of the biggest tasks, that I find with writing a report is the testing the reports and making sure the data that is displayed is correct.  My method of developing and testing reports is as follows: Write the queries, outside Reporting Services, for creating the report data set(s) with the parameters defined which will be configured within the report. Define the parameters that report data set would create and assign a valid value. Execute the queries. Inspect the results to make sure that they meet the expected results. Repeat with a new valid value or fix any issues. After one or two or even three passes that have worked then I take the queries and put them into report data sets. Then I define the report layout. Then I preview the report make sure it’s still correct Then I release to our te...

For the Scrum for Team System project I have been focusing on the reports; I came across an issue which was due to the way the Reporting Services 2008 "automatic upgrade" feature upgraded our reports. The Scrum for Team System template contains over 20 reports all written for Reporting Services 2005. This was done because Team Foundation Server 2008 only worked on SQL Server 2005 and Reporting Services 2005. Then in August of 2008 Team Foundation Server 2008 Services Pack 1 was released which added support for the Team Foundation Server 2008 is to run on SQL Server 2008 platform. With Reporting Services 2008 there is a new reporting definition language schema. However reporting services does have backward compatibility method as describe below: The report server will validate report definition file against the included reference to the RDL namespace which specifies the version of the report definition schema that is used. The report is automatically upgraded the first...

A couple of colleagues of mine were working on a complicated reporting services project; they had one report which had a parameter which allowed a user to select multiple values of numbers, the problem they were facing was how pass the selected values into the query. They were looking for a way within SQL Server to split a string by a delimiter. As they were using a stored procedure to access the data and multiple value parameters, regardless of parameter type, are passed in as a string of comma separated values. One thing which they weren’t aware of was that with a multiple value parameter, the behaviour can differ between how the data is being retrieved i.e.: stored procedure or embed sql statement. If the data set is using direct query to gather the data then the multi value parameter can be used in the query with a IN clause like so: Where column1 in (@pMultiValueParam) What happens is that before the query is sent to the SQL Server the query the report server substitutes...

As I have mention in another blog posts; I’ve been heavily involved in the Scrum for Team System process template since the start of version 2 and to coincide with the release of version 2.2; I would like bring your attention to a new tool, which I help to developed, that has been added to this release. I started creating this tool due to some feedback we got from our beta users for “ TaskBoard for Team system ” tool. The request was to add a demonstration mode into the application which would show things like swimming lane-view and the following reports: Sprint burndown chart; Sprint cumulative flow as these can be view within the application. The reason for the request was create the sense of: "Look guys, this is what we are working on, and this is how we are doing", throughout the day, not only during the scrum meetings. This also happen a lot within our projects which they do something similar as well. Another reason for the tool to separate from the Task Board applic...

I have been working on Scrum for Team System focusing on the reporting functionality of this tool.  One of the things I looked at was to get the reports delivered to me by e-mail.  I went looking to see if there was an interface via the Team Explorer interface, to which I found quickly there wasn’t.  However because TFS uses Reporting Services I knew that I could use Reporting Services subscriptions functionality.  Firstly, if not already configured, the Reporting Services server will need to will need to be set up to point to an SMTP server.  This can be done by using the Reporting Services configuration tool and updating the e-mail settings within this tool.   You can find this tool on your TFS server under the following: Start Menu > All Programs > Microsoft SQL Server 2005 > Configuration Tools To create the subscription you need to use the Report Manager  (to gain access to this you can right mouse click on the report folder within the t...

I am currently responsible for deploying our project into the test and production environments.   The project has a few reports which need to be installed on a few report servers.   The deployment tool that I am using for the rest of my project is MSBuild so I tried to see if there was a way of getting MSBuild to deploy the reports.   The only method that I found was to write an RS script file which deployed the reports and calls the Exec task to run the RS script.   So I started outright with RS script as I was not over joyed about writing VB.Net code as I am a C# coder. Then I remembered that MSBuild is customizable as it allows custom tasks to be written.   So I stopped writing my VB.Net code went back C# and created a few tasks that are commonly needed to deploy: AddReportUser, CreateReportFolder, CreateConnectionSoruces, DeployReports and SetReportsDataSource.   The task code was easy to write. I did have to change some of the auto generated code...

I had a few reports for an application that needed to be deployed to number of different report servers.   The report servers were Express edition of Reporting Services 2005 with the application database on the same server which helped with the connection limitation of Reporting Services 2005 Express. As the report servers were joined to a domain their computer names have to be unique, the only way application was going to get around this problem was to use host file redirect.   To help with deploying the report I was to going to try to use the host idea, so I added the server name to the localhost entry and tried to run the reports.   Unfortunately this would not work because it looks like the reporting services 2005 express checks the name of the computer, which it gets from the following path HKEY_LOCAL_MACHINE\SYSTEM\ControlSet001\Control\ComputerName\ActiveComputerName, against the datasource within the connection if they don’t match then raises an error.

We had a requirement to allow a front page, which was another local report, to be printed before the report within the report viewer was printed.  As both reports were local reports it was easy to create a print button which allowed us to meet the requirements, here is the code that we used: 1: if ( this .printFrontingSheetCheckbox.Checked) 2: { 3: LocalReport frontsheet = front.reportViewer1.LocalReport; 4: Export(frontsheet, 8.27f, 11.69f, 0f, 0f, 0f, 0f); 5: m_currentPageIndex = 0; 6: Print( false ); 7: 8: foreach (Stream s in m_streams) 9: { 10: s.Close(); 11: } 12: front = null ; 13: } 14:   15: LocalReport report = reportViewer.LocalReport; 16: Export(report, 11.69f, 8.27f, 0f, 0f, 0f, 0f); 17: m_currentPageIndex = 0; 18:   19: Print( true ); foreach (Stream s in m_streams) 20: { 21: s.Close(); 22: } 23:   24: private void Print( bool landscape) 25: { 26:...

I have found that the reporting services report viewer control might not use the correct paper size to render the report.  The reason for this can be found on the following link: click here . Quick summary of the solution is that if you wish the report viewer control to render A4 page size instead of Letter then within the report define the pages size of A4 in inches.

I have just managed to solve the problem of how to give an application the ability to print Avery labels with data from our database. I only had to support two label formats (L7161 and L7163) and the data requirement was static as well. So I looked at RDL / Reporting Services and the Report Viewer control. The way I wrote the report is as follows: Set the page size to A4 and all the margins to 0cm. Made sure that the report was not using a Header or Footer. Placed one list control per column of labels on a sheet Changed the size of the list control to be the same as a single label Then set an expression on the list visibility based on the result of the rownumber modulus by the number of columns (making sure each column looked for a unique result) Then added the textboxes for the data making sure there was a gap between the top and bottom of the list control. Also made sure that the text boxes didn’t push the list box size out. I did have trouble trying to line the labels up 10...

I have written some code, which will create a report and deploys it to a report server. However the connection string for the report has to use SQL Authentication. My code which builds the reports uses the same approach that Microsoft does to SQL Authentication within report, which is to not store the UserID and Password, so what would happen now when the report is displayed in the report viewer is that a prompt would appear asking for a username and password. This behavior had to be suppressed, because I did not wish the users to know or need to type in a userid or a password, so I tried to resolved this by looking for a way to get the connection string stored on the server when deploying the report, like you can when you use SQL Server Business Intelligence Development Studio, with no joy. I did find a solution with the Report Viewer control, there is method to send the data source credentials to the server without the user having to type them in. Below is an example: 1: rvRepor...