Installing SQL Server 2005 on Windows Vista or Windows 2008: IIS 7.0 problem

Recently I tried to install SQL Server 2005 on Windows Vista and I have received a warning message for IIS Feature requirement on the System Configuration Check page of the SQL Server 2005 Setup program. This means that you won’t be able to install SQL Server components, requiring IIS, such as Reporting Services. The problems occurs because IIS 7.0 is much more customizable and has much more components, so some of the components required for RS installation are missing from the default IIS 7.0 install. Here is the list of the IIS components required for Reporting Services:

For instructions on how to install missing IIS components in Windows Vista, go to: http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=957 (http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=957).

For instructions on how to install missing IIS components in a Server Core installation of Windows Server 2008, go to: http://www.iis.net/default.aspx?tabid=2&subtabid=25&i=956

The solution is also explained in Microsoft KB920201.

Fixing TFS reports after an upgrade/migration to TFS 2008

Microsoft has made quite a few changes to reports in TFS 2008. As you can see form the table below in TFS 2008 we do not only have 6 new reports, but some of the existing reports have been modified/removed (new reports are in red, removed reports are in orange).

This means that when you upgrade/migrate to Team Foundation Server 2008, your reports will no longer work as the schema has changed. To resolve the issue you need to download the new process template (http://msdn2.microsoft.com/en-us/teamsystem/aa718795.aspx) and add or update your reports on TFS server.

To add new report:

1. Go to your Reports server (http:// [SERVERNAME] /reports)
2. Click on Upload file
3. Browse to the report file (.rdl) you want to upload and click OK to upload
4. Click on newly uploaded report file and click on Properties
5. Set up data sources by clicking on Data Sources (Don't forget to click Apply button)
6. Depending on report, you might also have to set up default parameters by clicking on Parameters (Don't forget to click Apply button)
7. Click on View to make sure that report is displaying properly

To update existing report:

1. Go to your Reports server (http:// [SERVERNAME] /reports)
2. Click on report that you want to update and click on Properties
3. Click on Update
4. Browse to the report file (.rdl) you want to upload and click OK to upload
5. Make sure that data sources are configured properly by clicking on Data Sources (Don't forget to click Apply button)
6. Depending on report, you might also have to set up default parameters by clicking on Parameters (Don't forget to click Apply button)
7. Click on View to make sure that report is displaying properly

Note: You will have to update reports for each TFS project

Fixing MSSQL Reporting Services add-in after the migration of SharePoint site to a new server (WSS 3.0 or MOSS 2007)

If you're thinking about migrating WSS 3.0 or MOSS 2007 sites to a new server, you need to remember to reinstall any third-party webparts you have installed on the old Sharepoint server. So if your Sharepoint instance have Reporting Services webpart installed you will have to re-install it, but before you do that make sure you remove old webpart from the Web Part Gallery and remove (or comment out) any references to the old RS webpart from web.config file in the Sharepoint's virtual directory.

<SafeControl Assembly="Microsoft.ReportingServices.SharePoint.UI.ServerPages, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.ReportingServices.SharePoint.UI" TypeName="*" Safe="True" />
<SafeControl Assembly="Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.ReportingServices.SharePoint.UI.WebParts" TypeName="*" Safe="True" />
<SafeControl Assembly="RSWebParts, Version=8.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" Namespace="Microsoft.ReportingServices.SharePoint.UI.WebParts" TypeName="*" Safe="True" />

<add verb="*" path="Reserved.ReportViewerWebControl.axd" type="Microsoft.Reporting.WebForms.HttpHandler, Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a" />
<add verb="*" path="_vti_bin/ReportServer" type="Microsoft.ReportingServices.SharePoint.Soap.RSProxyHttpHandler, RSSharePointSoapProxy, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />
<add verb="*" path="Reserved.ReportViewerWebPart.axd" type="Microsoft.ReportingServices.SharePoint.UI.WebParts.WebPartHttpHandler, Microsoft.ReportingServices.SharePoint.UI.WebParts, Version=9.0.242.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91" />

<location path="_vti_bin/ReportServer/ReportServiceAuthentication.asmx">
<system.web>
<authorization>
<allow users="*" />
</authorization>
</system.web>
</location>

Note: If you don't remove the references to the old RS webparts, you won't be able to get reports to display properly no matter how many times you re-install the webpart.

Calling a Custom External Assembly from a Report

One of the most powerful features of Reporting Services is the ability to call code from within a report. Before we get to that, you may or may not know you can call the .net Framework from within the expression of an objects property on a report. However, there is one rule you must follow, the code must be written in VB.Net.

• For example: 
  =iif( me.Value < 0, “Red“, “Black“) placed in the color property of a textbox this expression will change the text colour based on the value returned.
  =Format(Fields!BirthDate.Value, "MMM dd,yyyy") placed in the value property this expression will format a date value, (ie Dec 10, 2006)=System.Web.HttpContext.GetGlobalResourceObject("AppResources", "ID14831").ToString() placed in the value property uses your existing resource file to translate a label

Although this is all great and very useful, if you need to do something a bit more complicated you can write your own code and call that from an expression in a report also. This can be done in two ways.

1. You can write code directly in the report using the report properties Code tab. The code tab contains a multi-line textbox where you can write code that can be called from the expressions of the report. From the menu select Report - Report Properties... on the Report properties dialog select the Code Tab. Here you can write code like in the example below. Once again it must be VB.Net.

To use this code in your report place the following into the Value proprty of the textbox where you want to display the Pay Frequency.  

=code.GetPayFrequencyString(Fields!PayFrequency.Value)

2. You may want to reuse the code you write, across many reports. if that is the case, first write your assembly in the .net language of your choice. Then make sure the assembly is in the correct folder so it can be found by the Report Server and the Report Designer. I have a Blog entry here to help you with that.

Now you need to create a reference to the Assembly from the report. There is a reference tab on the Report properties dialog. Make sure you put the namespace in front of the class name or it won't work.

Now you can call your Assembly from any expression by referencing the class and method in the expression. Like this:

=code.empReport.GetSickLeave(Fields!SickLeaveHours.Value)

SRS Filters

I have found a lot of students and clients are not getting the most out of filters in there reports. So I thought I would explain a bit about how useful they can be. There are two places you can put a filter in a report.

• Data Region (Table, Matrix, List, Graph, Grouping Level)

Applying a filter to a DataRegion means I can show different views of the same data. In other words I can create one Dataset and let it feed multiple regions in my report. Say for example I need a report for management that shows the breakdown of Salaried employees vs hourly. They want to see two pie charts one will show the number of salaried employees by department the other Hourly. Instead of creating two queries one that selects salaried employees and one that selects hourly. I can just select all the data into one Dataset and filter the separate Graphs.

 

When you set the filter don't forget you are applying an expression so the value must begin with an equal sign. This is a common source of frustration the first time people use filters.

I can reuse this data in different ways throughout the report. In a matrix to show the number of employees by region, in a table sorted by Hire Date. All from one dataset.

• DataSet

You can apply a filter to a Dataset, which at first seems odd. Doesn't the Dataset already have its parameters. Why would I filter the report again? When you create a Cached Instance or a Snapshot the report is stored with its data and the parameters cannot be changed. However filters can still be applied to the report. Filters will use the current report parameter values to filter the report instead of creating a new cached instance or snapshot. This can be useful to avoid creating multiple versions of the same report in the cache.

Removing unwanted space in a report

There is a little trick in Reporting services that is not easy to find.

I created a report for a client that included an external class which parsed a stream of XML and returned various data from the XML for display in the report. The nature of the data was that not everything is required for each row. For example, each XML document would contain a question and answer given by the end user. Some questions would contain a tip to help the user and others did not.

To make the report generic, I used a table with multiple details bands for each question. Each textbox in the table would go after a different piece on data in the XML. The output of the report looks something like this:

Q: Should golf be 18 holes?
A: No.

Q: How many holes should a round of golf be?
A: 12.
Tip: The answer is 12.

Q: Why 12?
A: It just makes sense.

As you can see, some rows contain a tip and some do not. When the report calls the parser it gets back a “NoData” message indicating that there is no tip for this question. The problem you will find is there will be a space where the empty tip textbox is. (see example)

Q: Should golf be 18 holes?
A: No.
   [extra space not required]

Q: How many holes should a round of golf be?
A: 12.
Tip: The answer is 12.

Q: Why 12?
A: It just makes sense.
   [extra space not required]

----------------------------------------------------------------------------------------

You might have tried the following.
Put an expression on the textbox's hidden property so that if the NoData message is returned the textbox is made invisible like this:

=iif(Me.Value = "NoData", true, false)

You will find the extra space is still there.

You may have tried to set the text box property CanShrink to True, but the space will still be there.

Here is the trick. Make the height of the textbox zero. Reporting Services will change that to some default like .03 but this is the only way to remove the space. Make sure you set Can Shrink and Can Grow to True.

Duplicated Parameters in SRS Dataset

It's time I logged this hear so I can remember what the problem is when I get it. :)

When using a Stored Procedure in a Dataset in Reporting Services, Have you ever seen double the parameters on the refresh dialog in the Data Tab? They are actually listed twice in the dialog like this.

OrderId
OrderItemId
ProductID
OrderId
OrderItemId
ProductID

This is caused by duplicate versions of the Stored Procedure. For example

dlloyd.apGetOrder
dbo.apGetOrder

If you remove one of the Stored Procedures the parameters will return to normal.

Reporting Services Special Directories

Where to put a report so that it shows up in the New Item Dialog as a template:

•   C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer\ProjectItems\ReportProject

Where to put a custom class so it can be found by Report Server

• C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\bin

Where to put a custom class so that it can be found by the designer

• C:\Program Files\Microsoft SQL Server\80\Tools\Report Designer

Parameter Collection MultiValue not implemented yet

The Parameter collection in Reporting Sevrices has a boolean property called MultiValue. I wanted to use this so we could tell the front end that the user should be able to return multiple values for this parameter. There is no where to set this value in the Report designer. So I added it to the RDL manually. But it always comes back false.

After searching the MS Reporting Service News Group  I found out this is not implemented yet. It is defined in the parameter class but not used. Even the help references it. MultiValue - Indicates whether the parameter can be a multi-value parameter. Boolean

Here is the response from MS in the news group:

The MultiValue element is part of the RDL specification published in 2003/10. It was not implemented in V1 of Reporting Services. It will be available in the next version. The MultiValue boolean flag determines if a report parameter is a multi value parameter.

Reporting Services - Report Parameter - Available Values

I would like to interrupt my discussion of how to create an inbox at this time to point out a potentially annoying feature/defect in the ValidValues collection in Reporting Services.

When you add parameters to the report you can add a list of Valid Values. I think it's called Available Values in the UI. This would typically be a list of codes and display values used in a drop down list. The entries can be queried or hard coded right in the report.

Since we are building a front end to RS and passing the parameters to JSP for rendering on a web page we have to try a few different things, to make sure we can generically render the controls for gathering parameters on the web site.

So I created a test report which retrieved the customers from Northwind. The customer code is a string. So to keep it simple I made that a parameter and I put three of the customer codes into the Valid Values section of the report Parameter. Both the Label and Value were the same. I know this seems a bit odd, but I have seen real applications where this is the case. In some industries the code is known by the end user. For example they don't care that YYZ is the code for Toronto airport. They know YYZ is and that is what they want to see in a selection list. (calm down it's just an example)

Anyway back to the problem. If you create a parameter of the type string and fill in the Valid Value list with the same data in the label and the value, you will notice that when you call GetReportparameters to populate the parameter collection the valid value labels will be null. Perhaps someone thought if the value and label are the same they don't need the label. Although this might be true why make us write special code to know that and ignore the label. Just pass through the label as is even if it is the same as the value.

I hope it's just a bug and not a design decision. By the way I installed Service Pack 1 and it's still the same.