No underline shown on links, Action, Hyperlink, Drillthrough in Reporting Services R2 and Denali

If you’re using the ReportViewer 8.0 web control for Reporting Services 2005 in your web application and pointing at Report Server version R2 or Denali then you may see this bug – some font properties such as Color and TextDecoration do not render on an Action element (used for BookmarkLink, Hyperlink and Drillthrough). To get round this you can turn your property values to expressions e.g. change:

<TextDecoration>Underline</TextDecoration>

to:

<TextDecoration>="Underline"</TextDecoration>

Thanks to JhonnyGaleano for the tip-off on msdn forums

Here’s the full, fixed RDL for a Textbox with Action and Drillthrough:

<TextboxName=“section_0_FullName“>

<Style>

  <BorderColor>

   <Default>#2F5098</Default>

  </BorderColor>

  <BorderStyle>

   <Default>Solid</Default>

   <Right>None</Right>

  </BorderStyle>

  <BorderWidth>

   <Default>0.1mm</Default>

  </BorderWidth>

  <BackgroundColor>=IIF(RowNumber(Nothing) Mod 2, “#ffffff”, “#faf9f7″)

  </BackgroundColor>

  <FontStyle>=”Italic”</FontStyle>

  <FontFamily>=”Tahoma”</FontFamily>

  <FontSize>=”8pt”</FontSize>

  <FontWeight>=”Bold”</FontWeight>

  <Format />

  <TextDecoration>=”Underline”</TextDecoration>

  <TextAlign>Right</TextAlign>

  <Color>=”#00ff00″</Color>

  <PaddingLeft>1mm</PaddingLeft>

  <PaddingRight>1mm</PaddingRight>

  <PaddingTop>0.5mm</PaddingTop>

  <PaddingBottom>1mm</PaddingBottom>

</Style>

<Action>

<Drillthrough>

  <ReportName>/RSinteract Samples/Orders by Category</ReportName>

  <Parameters>

   <ParameterName=“FullName“>

    <Value>=Fields!FullName.Value</Value>

   </Parameter>

   </Parameters>

  </Drillthrough>

</Action>

<Value>=Fields!FullName.Value</Value>

<CanGrow>true</CanGrow>

<DataElementName>FullName</DataElementName>

</Textbox>

As this problem doesn’t affect reports viewed through Report Manager on Denali I’m guessing it only affects earlier versions of ReportViewer – if so, you may find it easier to upgrade your web application to use the latest ReportViewer control.

If you want help updating large numbers of existing reports then please get in touch below or through our contact form – we have plenty of code we could put together to create a mass report-update tool…

Could not load file or assembly Microsoft.ReportViewer.WebForms, Version=8.0.0.0

We’re starting to run RSinteract against SQL Server Denali and have hit an issue with the Report Viewer control, this solution could also apply to earlier versions of SQL Server…

When running RSinteract for the first time on a SQL Server 2008, 2008 R2 or Denali machine you may come across the following error:

Configuration Error

Description: An error occurred during the processing of a configuration file required to service this request. Please review the specific error details below and modify your configuration file appropriately.

Parser Error Message: Could not load file or assembly ‘Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ or one of its dependencies. The system cannot find the file specified.

Source Error:

Line 26: </buildProviders>

Line 27: <assemblies>

Line 28: <add assembly=”Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A” />

Line 29: <add assembly=”Microsoft.ReportViewer.Common, Version=8.0.0.0, Culture=neutral, PublicKeyToken=B03F5F7F11D50A3A” />

Line 30: <add assembly=”System.Core, Version=3.5.0.0, Culture=neutral, PublicKeyToken=B77A5C561934E089″ />

Source File: C:\Program Files\ICS\RSinteract\Website\web.config Line: 28

Assembly Load Trace: The following information can be helpful to determine why the assembly ‘Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a’ could not be loaded.

This is because RSinteract is trying to run the SQL 2005 Report Viewer Control. You can download it here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=4016

Passing parameters to a report

Lately we’re getting more and more interest from multitenancy SaaS providers and OEM partners who want to integrate RSinteract into their web applications. A really useful feature of RSinteract is the ability to link to a report from a 3^rd party application and pass filter values and UI parameters on the URL.

To do this with a report based on a SQL Server or Oracle data source:

• Create a report with filters, or use this sample report which comes with RSinteract: http://localhost/RSinteract/?/RSinteract+Samples%2fOrders+by+Category
• Open the report and hover over the parameter in the left hand sidebar
• Note the RDL field: Fullname
• Add the parameter name and value to the URL:
  • http://localhost/RSinteract/?/RSinteract+Samples%2fOrders+by+Category&Fullname=Pak,%20Jae
  • Separate the parameters with &
  • Replace any spaces with %20
  • Parameter names are case-sensitive
• For Between filters you actually have two parameters: add _0 and _1 to the RDL field:
  • …Orders+by+Category&OrderDate_0=1%20Jan%202003&OrderDate_1=31%20Dec%202003

For a report based on an Analysis Services OLAP data source:

• Create a report with filters, or use this sample report which comes with RSinteract: http://localhost/RSinteract/?/RSinteract+Samples%2fSales+by+Country+(OLAP)
• Open the report and hover over the parameter in the left hand sidebar
• Note the RDL field: Date_Fiscal
• Hold ctrl and double click on the empty bit of sidebar below the Filters panel, click OK to hide the popup message and you’ll see the Reporting Services toolbar at the top of your report
• Note the parameter value {[Date].[Fiscal].[Fiscal Year].&[2004]}
  • Remove the braces { }
  • Change ampersands to %26
  • Change [ to %5b
  • Change ] to %5d
  • Separate the parameters with &
  • Replace any spaces with %20
  • Parameter names are case-sensitive
• Put your new URL together by adding: &Date_Fiscal=%5bDate%5d.%5bFiscal%5d.%5bFiscal%20Year%5d.%26%5b2003%5d
• Try your new URL …Sales+by+Country+(OLAP)&Date_Fiscal=%5bDate%5d.%5bFiscal%5d.%5bFiscal%20Year%5d.%26%5b2003%5d

Notes:

• Any errors in the report frame are probably due to invalid parameters – if you’re not sure about your parameter values then paste them directly into the Reporting Services toolbar and hit the “View Report” button
• You don’t need to pass all of a report’s filter parameters on the URL, those not passed will use the normal report defaults.
• If your Filters tab has the ‘Always Ask’ option ticked you’ll need to add “&rsi:noq=1” to the URL to tell RSinteract not to show the question screen.
• The rsi:noq=1 option only works if all parameters in the report have a default value or a value is passed on the URL.
• Make sure you keep everything case sensitive.

Renaming SQL Server and Reporting Services

Renaming SQL Server & Reporting Services

If you change the name of a Windows Server which hosts a SQL Server instance then there a few steps you’ll need to take…

Rename SQL Server:

In SQL Server Management Studio check the name SQL has for itself:

print @@servername -- shows old name

If this isn’t your new server name then:

sp_dropserver @@servername -- drop old name
sp_addserver new_name, local -- add new machine name

Now restart SQL Server and:

print @@servername -- first time will give a transport error,
 the second should show the new name

Reporting Services 2005:

• Delete 2 IIS virtual directories for Reports and ReportServer
• Open Reporting Services Configuration Manager from start menu:
  • Create 2 new Virtual Directories for Report Server and Report Manager
  • Database setup – choose the new server
  • Web Service Identity – choose the RS app pool
  • Encryption Keys – delete or restore if you have a backup

The process for Reporting Services 2008 should be simpler as it doesn’t use IIS.

Enable remote errors in Reporting Services 2005 or 2008

Often when errors occur on the database or cube RSinteract and Reporting Services reports will fail with the following error: 

An error has occurred during report processing.
 Query execution failed for data set ‘dataset_0′.
  For more information about this error navigate to the report server on the local server machine, or enable remote errors

In order to see a better description of the error you can either RDP onto the server and run the report or…  

To enable remote errors in Reporting Services 2005 or 2008 run this statement in SQL Server Management Studio: 

Use ReportServer;
update ConfigurationInfo set Value = ‘True’ where Name = ‘EnableRemoteErrors’;

You then need to reset IIS

There are more options here: http://technet.microsoft.com/en-us/library/aa337165(SQL.90).aspx

And here: http://technet.microsoft.com/en-us/library/aa337165.aspx

 

So now if you re-run the report you can see it’s a data source error – a field name has been changed in the database:

An error has occurred during report processing.
 Query execution failed for data set ‘dataset_0′.
  Invalid column name ‘myNewField2′.

Further Information on Report Viewer Client Print Control bug

A while back I wrote a post about the “Unable to load client print control” error that Report Manager (and RSinteract) was throwing after Microsoft’s auto update KB956391.

I also asked a question over at StackOverflow which I ended up answering myself. Yesterday follow up information was added, which may be of use if you have trouble with the fix.

EY Kalman says:

I have had the same problem (on several different servers). Applying SP3 and Report Viewer SP1 has helped on some of the servers, allowing the client machines to connect and download the control with no problem. However, I have had one server that, even after applying the updates, when accessing the report viewer using a client machine, it was still giving me the error. On looking into the exact URL GET request that is being sent, I discovered that it is possible to force the client machine to connect directly to the Report Server to download the control.

The user would need to enter the following url:

http://reportservername/Reports/Reserved.ReportViewerWebControl.axd?ReportSession=51bjqv45xydgos2wghu5ceza&ControlID=7617dedbf0234f89b80cad8e64431014&
Culture=2057&UICulture=9&ReportStack=1&OpType=PrintHtml

This should then pop up the required download/install prompt.

dotnettrio added (which is probably more relevant to RSinteract):

Hi,

I got this working with out removing any patches. The above patch was not working too. Finally what I did was on the IIS server install the following patch and reset / restart the IIS server. This is not for report manager application. This is for any ASP.NET Web application developed in .net3.5 using VS2008 http://www.microsoft.com/downloads/details.aspx?familyid=6AE0AA19-3E6C-474C-9D57-05B2347456B1&displaylang=en

I hope this helps anyone else with this problem.

The XML page cannot be displayed

I’m just setting up RSinteract on a client’s 64 bit server which has Windows 2003 and SQL 2008. With most RSinteract installations IIS is installed as a prerequisite to Reporting Services but IIS isn’t needed for Reporting Services 2008. I guess this will become a common sticking point with SQL 2008 installations so here’s the solution…

The first problem I got was in the RSinteract installer (whoops, should have copied the error message) so we installed IIS and then RSinteract. When I hit the site I got this error:

The XML page cannot be displayed

Cannot view XML input using XSL style sheet. Please correct the error and then click the Refresh button, or try again later.

---

A name was started with an invalid character. Error processing resource ‘http://myserver/RSinteract’. Line 1, Positio…

<%@ Page EnableViewState="false" Language="c#" ValidateRequest="false" AutoEventWireup="True" Codebehind="Main.aspx.cs" I…

I guess this is because IIS isn’t configured for ASPNET by default so I ran:

%windir%\Microsoft.NET\Framework\v2.0.50727\aspnet_regiis.exe –i

Which gave me the following error:

The error indicates that IIS is in 64 bit mode, while this application is a 32 bit application and thus not compatible.

So I made a slight change to point to the 64 bit .NET folder:

%windir%\Microsoft.NET\Framework64\v2.0.50727\aspnet_regiis.exe -i

After an iisreset I was now getting a 404 error from aspx pages, the final step was to open IIS (type inetmgr at the command prompt) and go to the “Web Service Extensions” node and enable ASP.NET v2.0.50727. Another iisreset for good measure and we’re now up and running:)

Could not load file or assembly 'Microsoft.ReportingServices.Interfaces'

In the last week we’ve had three clients report a problem with our December release when running on Windows Server 2003 64 bit.

Server Error in ‘/RSinteract’ Application.

Could not load file or assembly ‘Microsoft.ReportingServices.Interfaces’ or one of its dependencies. An attempt was made to load a program with an incorrect format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.BadImageFormatException: Could not load file or assembly ‘Microsoft.ReportingServices.Interfaces’ or one of its dependencies. An attempt was made to load a program with an incorrect format.

It seems Microsoft.ReportingServices.Interfaces.dll version 9.0.1399.0 won’t load on a 64 bit server, the hotfix is to replace it with this version:
http://www.rsinteract.com/download_files/Microsoft.ReportingServices.Interfaces.zip
The default location is C:\Program Files (x86)\ICS\RSinteract\Website\bin\
IIS should be reset afterwards.

This issue will be resolved in the next big release in February 2009

The Double Hop Problem

Let me start by apologising for the lack of a post in the last week or so, I have been really busy with the new, improved version of RSinteract (sneak preview in the next couple of weeks, watch this space).

One of the issues we have experienced time and again with RSinteract is a scenario where the ReportServer/Database or Analysis services is on a different machine from RSinteract as demonstrated in the following diagram. The problem manifests itself as an authentication issue when Reporting Services tries to retreive information from the Database or Analysis server because Active Directory credentials expire after the first hop, in this case from RSinteract to Reporting Services.

Microsoft are calling this the “Double Hop” problem. The reason for Reporting Services’ and Analysis Services’ lack of integration out of the box is “in the interest of security”. Active Directory uses Kerberos under the hood which can be configured to pass credentials between web apps or not. To get this working with RSinteract or indeed any ASP.NET application you need to correctly configure Kerberos delegation.

I’m having trouble tracking down the steps for Reporting Services, the link I had has gone dead since I began writing this post

So here’s the steps to configure your Analysis Services (from Business Intelligence and Agile Development Blog):

1. Download and install the SetSPN utility (Either from the Windows Resource kit, or from http://www.microsoft.com/downloads/details.aspx?FamilyID=5fd831fd-ab77-46a3-9cfe-ff01d29e5c46&DisplayLang=en)
2. SETSPN is used to configure a “Service Principal Name”, which is required by Kerberos
3. Create a Service Principal Name (SPN) for the Analysis Services Instance (on the AS server)
4. (If AS is running as a domain account) setspn.exe –A MSOLAPSvc.3/
5. Configure Active Directory Settings
6. All users that are connecting to Analysis Services through the intermediate host need to be allowed to use Kerberos Delegation (“Account is sensitive and cannot be delegated” must be unchecked)
7. The “Account is trusted for delegation” setting must be checked for each user and each service account used in the process.
8. The “Trust computer for delegation” setting must be checked for each computer used in the process
9. Configure IIS Settings for the Virtual Directory hosting the application (or SharePoint site)
10. The Authentication must be set to “Integrated Windows Authentication” or “Basic Authentication”
11. The application protection level must be set to “High (Isolated)”
12. The impersonation level for COM+ must be set to “Delegate”
13. The COM+ application identity must be set to an account where “Account is trusted for delegation” is checked.
14. Connection strings must include SSPI=Kerberos
15. Connection strings CANNOT use IP Addresses

Here’s some more information pertaining to the bug:

• http://social.msdn.microsoft.com/forums/en-US/sqlreportingservices/thread/b8ba8546-76e8-46dc-a96e-bc61e847521f/
• http://www.sharepointblogs.com/kwanl/archive/2007/01/23/double-hop-asp-net-developers-beware.aspx

I’ll update this post as soon as I find the information for configuring SSRS.

Unable to load client print control

UPDATE: Further information on alternative fixes has come to my attention via StackOverflow.

This new error has popped up since Microsoft released the update KB956391.

The update is for a vulnerability in the GDI+ DLL which allowed a malicious user to execute code on the client machine (good old! – ed). The fix uses ActiveX Killbits which is a setting that Internet Explorer checks for each ActiveX control. If the Killbits are set, IE refuses to load the control.

The update set the Killbits for the version of the RS Client Print ActiveX control which is used by the version 8.0.0.0 of the ReportViewer which is referenced by RSinteract (and Report Manager). Thus the printing will not work in either.

To fix Report Manager you need to apply the ReportViewer SP1 patch and the Security Update for SQL Server SP2 (KB954606). Once this is working again you can fix RSinteract.

To fix RSinteract you need to apply another Security Update for SQL Server SP2 (KB954607).

Let us know if this fix doesn’t work for you.