#18 SQL Reporting Viewer on page passing credentials

This code presents a report viewer on an asp.net page and allows you to call a class that encapsulates the needed calls to get & pass credentials, report path, report name and parms to the SQL Reporting Server. The more static parameters like report server and authenticating ID & password are read in from the web.config file.

asp.net page code holding ReportViewer control:

<%@ Register Assembly=”Microsoft.ReportViewer.WebForms, Version=9.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a”
Namespace=”Microsoft.Reporting.WebForms” TagPrefix=”rsweb” %>

<rsweb:ReportViewer ID=”ReportViewer1″ BackColor=”#efefef” runat=”server” Width=”100%” Height=”800px”>
</rsweb:ReportViewer>

asp.net.cs code

protected void launchReport()
{
SRS _srs = new SRS();
List<ReportParameter> paramList = new List<ReportParameter>();
paramList.Add(new ReportParameter("Username", "***", false));
paramList.Add(new ReportParameter("ClientID", "****", false));
_srs.launchReportInViewer(ref ReportViewer1, "ReportFolder", "ReportName", paramList);

}

SRS class used to wrap the calls to the report server. This will require you add a reference to Microsoft.ReportViewer.WebForms.dll in the GAC or add into your /Bin dir. (check C:\Program Files\Microsoft Visual Studio 8\ReportViewer to locate the dlls if not installed in the GAC)

using System;
using System.Configuration;
using Microsoft.Reporting.WebForms;
using System.Collections.Generic;

/// <summary>
/// Summary description for SQL Reporting Viewer class
/// </summary>
public class SRS
{
public SRS ()
{

}

public void launchReportInViewer(ref ReportViewer RV, string sFolder, string sReportName, List<ReportParameter> paramList)
{
RV.ServerReport.ReportServerCredentials   = new ReportViewerCredentials();
RV.ServerReport.ReportServerUrl           = new Uri(ConfigurationSettings.AppSettings["ReportServer"].ToString());
RV.ServerReport.ReportPath                = @"/" + sFolder + @"/" + sReportName;
RV.ServerReport.SetParameters(paramList);
RV.ShowRefreshButton                      = true;
RV.ShowPageNavigationControls             = true;
RV.ShowToolBar                            = true;
RV.Visible                                = true;
RV.ProcessingMode                         = ProcessingMode.Remote;
}

}

Credit due for ReportServerCredentials class beloworiginal link This class was converted to C# and slightly modified. Please note the GetFormsCredentials method has to be implemented to meet the interface definition but is stubbed out.

ReportViewerCredentials class:
using Microsoft.Reporting.WebForms;
using System.Net.Security;
using System.Net;
using System.Configuration;

public class ReportViewerCredentials : IReportServerCredentials
{
private string _userName;
private string _password;
private string _domain;

public ReportViewerCredentials()
{
_userName   = ConfigurationSettings.AppSettings["ReportViewerUser"].ToString();
_password   = ConfigurationSettings.AppSettings["ReportViewerPassword"].ToString();
_domain     = ConfigurationSettings.AppSettings["ReportViewerDomain"].ToString();
}

public ReportViewerCredentials(string userName, string password, string domain)
{
_userName = userName;
_password = password;
_domain = domain;
}

public System.Security.Principal.WindowsIdentity ImpersonationUser
{
get { return null; }
}

public System.Net.ICredentials NetworkCredentials
{
get { return new NetworkCredential(_userName, _password, _domain); }
}

/// <summary>
/// Not Used
/// </summary>
public bool GetFormsCredentials(out System.Net.Cookie authCookie, out string userName, out string password, out string authority)
{
userName = _userName;
password = _password;
authority = _domain;
authCookie = null;
return false;
}

}

web.config entries:

<appSettings>
<add key="ReportViewerUser" value="***"/>
<add key="ReportViewerPassword" value="***"/>
<add key="ReportViewerDomain" value="***"/>
<add key="ReportServer"  value="***"/>
</appSettings>

#17 Thinking of converting to VS 2008 ?

### Update ###

The SQL 2008 Business Intelligence Development Studio (VS subset) that ships with SQL Server 2008 imports the 2005 VS  rdl files without any problems. I have not tested this extensively but all looks same-same.

D Gorman 1/14/09

####

(see update above) Be aware that your VS 2005 Sql Reporting Projects don’t convert.  I just ran a conversion (after backing up the code and solution files) and it just drops the SRS (.rptproj) project. I tried opening a .rdl file and it does not handle it correctly.  I will have to look at what is next in regards to this but for now you have to keep VS2005 around for SRS reporting and/or figure out VS 2008 new process for handling BI (BIDS).  Post a comment if you know a better way.

After searching around I found these posts:

•http://geekswithblogs.net/juang/archive/2007/12/12/visual-studio-2008-will-not-support-sql-server-2005-reporting.aspx

• http://forums.microsoft.com/msdn/ShowPost.aspx?PostID=3336504&SiteID=1

Apart from this the other features in VS2008 are very nice & the ability to switch frameworks (sans the usual 1.x)  works flawlessly. I moved a web project from 2.0  to 3.5 and down to 3.0 without a problem.

#16 Flipping a SQL report from/to Prod. & Test systems

To flip an existing report definition (stored in rds/rdl files) via Visual Studio:

1. Create an alternate (shared) data source (.rds) for the other system.

2. Take the existing report and copy it off  and rename it (control-C & control-V/ rename should do that)

3. Open this new report definition (step #2) & create a new dataset (part of the .rdl) using this new data source (step #1)

4. Copy the old query or stored procedure reference into this new dataset.

5. Clean up any direct dataset references in the cell expressions.

#12 Abbreviating Memo/Text data with an ellipsis

You can do this on the raw SQL query for a GridView (asp.net) or the data tab on SQL Reporting Services (RDL definition)’

Examples:

Select   CASE WHEN (DATALENGTH(Activity.Notes) > 250) THEN  SUBSTRING( dbo.ScrapeText(Activity.Notes),1,250) + ' ...' ELSE LTRIM( dbo.ScrapeText(Activity.Notes)) END as ActivityNotes
from Activity

  
 The example above incorporates the UDF ScrapeText to clean up the HTML tags (see Post #7 )

You can also do this in the table cell on SQL Reporting:

=iif((Len(Fields!ActivityNotes.Value) > 250), Left(Fields!ActivityNotes.Value, 250) & " ...", Fields!ActivityNotes.Value )

The result of all of this sets a maximum length on a table cell which can provide a more uniform table look when you get unusually large data.  You can also wrap that ellipsis with a view more link (anchor tag) to show more detail (i.e.) <a href=’http://server/viewMore.aspx?id=xxxx’ target=’_blank’> … (more)</a>  To do this you will want to use the SQL Reporting cell edits to install the query string variable (id).

#09 Simple Alternate Background Row Color

Overwrite the row background color property with this expression.

=iif(RowNumber(Nothing) Mod 2, “WhiteSmoke”, “White”)

For old timers – this creates the Green Bar shading effect on a SQL Reporting  Services Report when applied to a detail row on a table.

I have not tried it but you can probably use the hex color values like #990000 instead of the key words (ie Maroon)

Credit Due:  Professional SQL Server 2005 Reporting Services by Turley, Bryant …

#07 SQL UDF to Clean Up HTML tags

When using SQL Reporting to report on text fields that are formatted with html tags this user defined function UDF (a modified copy of a post by William McEvoy) is helpful. It does not clean up all but does a decent job on most common tags. You can add offending tags and code to the replace special character’s list (ie.   below)

Create FUNCTION  [dbo].[ScrapeText]
(
  @string varchar(8000)
)
returns varchar(8000)

AS
BEGIN
---------------------------------------------------------------------------------------------------
-- Title:        ScrapeText
--
-- Date Created: April 4, 2006
--
-- Author:       William McEvoy
--
-- Description:  This function will attempt to remove rtf formatting from a string. This is
--               accomplished by concetenating all text contained between greater than and less
--               than signs within the formatted text.
--
-- Example:      <P>This text will be parsed and returned but not the P's</P>
---------------------------------------------------------------------------------------------------
-- Date Revised:  5/3/08
-- Author:       D Gorman
-- Reason:      Minor HTML tags & codes added to replace
---------------------------------------------------------------------------------------------------

declare @text  varchar(8000),
        @PenDown char(1),
        @char  char(1),
        @len   int,
        @count int

select  @count = 0,
        @len   = 0,
        @text  = ''

-- Add tokens
select @string = '>' + @string + '<'

-- Replace Special Characters
select @string = replace(@string,'&nbsp;',' ')
select @string = replace(@string,'<P>',char(13))
select @string = replace(@string,'</P>',char(13))
select @string = replace(@string,'&amp;',char(38))
select @string = replace(@string,'&copy;',char(169))

-- Parse out the formatting codes
select @len = len(@string)
while (@count <= @len)
begin
  select @char = substring(@string,@count,1)

  if (@char = '>')
     select @PenDown = 'Y'
  else
  if (@char = '<')
    select @PenDown = 'N'
  else
  if (@PenDown = 'Y')
    select @text = @text + @char

  select @count = @count + 1
end

RETURN @text
END

You would use this ‘upstream’ on the query/view or stored procedure used by the SQL Reporting report dataset definition.  In the report def you’d just reference the NewName field reference.

Select  dbo.ScrapeText(FieldX)  as NewName From ProjectCustomTabFields Where WBS1=’xxxxxx’