Chris May

We ran into an interesting problem today.

A matrix report in SSRS (sqlserver reporting services) 2008 would work just fine when previewing it in VS, and would work fine when viewed directly on the reporting server.  But, if you view it through a asp.net reportviewer control, it would just show the header, a big blank space, and then the footer.

This only happens if you have Vertical text for the row headers.  Remove that and everything is OK.

I began editing the generated CSS/HTML and found that the cells had a number of styles applied, but specifically the one that seemed to break everything was:

WIDTH:100%;

Remove that and the page rendered as expected.

We tried changing a number of parameters to get it to remove the width style but no luck.

We have something that generates images with GDI to do it now, but it's not ideal.

UPDATE 2:

Either this never worked in the first place as I wanted, or MS has changed how they are doing things in SSRS 2008.

I now don't see any of the errors in the tables I had been looking in, and in fact, the errors don't show up in any of their logs either.  This is crap.

If you are like me, you don't like the fact that the first time you realize that a data driven subscriptions in SQL Server Reporting Services has been failing is when someone comes up to you and says "Hey, I did XYZ and I never saw the server kick off an email." 

Really?  Let me check.... oh, looks like it has been broken for god knows how long.  What the heck?

Well, I wrote a script to notify you when there are errors on the report server.

First, you need to setup Database Mail.  Expand Management, and pick Database Mail.  For my script I used the name "Email Profile" for the "Email Profile" get it?

Then create a trigger on the ExecutionLogStorage table like this:

USE [ReportServer]
GO
/****** Object:  Trigger [dbo].[ExecutionLogStorage_Insert]    Script Date: 05/13/2009 10:20:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[ExecutionLogStorage_Insert]
ON [dbo].[ExecutionLogStorage]
AFTER INSERT
AS 
BEGIN
    DECLARE @sStatus AS NVARCHAR(32)
    SET @sStatus = (SELECT Status FROM inserted)
    
    IF(@sStatus <> 'rsSuccess')
    BEGIN
        DECLARE @sReportName AS NVARCHAR(425)
        DECLARE @sPath AS NVARCHAR(425)
        DECLARE @ReportId AS UNIQUEIDENTIFIER 
        DECLARE @sUserName AS NVARCHAR(260)
        DECLARE @sParameters AS NVARCHAR(4000)
        DECLARE @dTimeEnd AS DATETIME 
        DECLARE @iTimeProcessing as int
        
        
        SELECT 
          @sReportName = Name,
          @sPath =PATH,
          @ReportId = ReportId,
          @sUserName = username,
          @sParameters = (select CAST(ISNULL(ELS.Parameters,'') AS NVARCHAR(4000)) from ExecutionLogStorage ELS Where LogEntryId = Inserted.LogEntryId),
          @dTimeEnd = TimeEnd,
          @iTimeProcessing = ISNULL(TimeProcessing,0)
        FROM inserted INNER JOIN [Catalog] 
        ON inserted.ReportId = CATALOG.[ItemID] 
        
        DECLARE @sBody VARCHAR(8000)
        SET @sBody = 'Error with SQL Report ' 
                    + CAST(@sReportName AS VARCHAR(100))
                    + ' at path ' 
                    + CAST (@sPath AS VARCHAR(250))
                    + ' with ReportId ' 
                    + CAST(@ReportId AS VARCHAR(100)) 
                    + ' and username ' 
                    + CAST(@sUserName AS VARCHAR(100))
                    + ' Parameters ' 
                    + CAST(@sParameters  AS VARCHAR(5000))
                    + ' End time ' 
                    + CAST(@dTimeEnd AS VARCHAR(50))
                    + ' Status ' 
                    + CAST(@sStatus AS VARCHAR(50))
                    + ' Time Processing '
                    + CAST(@iTimeProcessing AS VARCHAR(50));
        
        DECLARE @sSubject VARCHAR(500)
        SET @sSubject = 'Error With Report ' + @sReportName
        
        EXEC msdb.dbo.sp_send_dbmail 
         @profile_name = 'Email Profile', 
         @recipients = 'email@address.com', 
         @body =  @sBody  ,
         @subject = @sSubject;

    END
    
END

Presto!

UPDATE: Added logic to get the status and parameters.

Ever get this error?

More than one data set, data region, or grouping in the report has 
the name 'SOMENAME'.  Data set, data region, and grouping names 
must be unique within a report. 

Basically what happened was, somehow VS lost track of an existing datasource you were using, why I don't know. When you went to add a new field or change something it added a 2nd copy of the same datasource, and now you have 2 datasources with same name. You can't do that. To fix this, when you are editing the report, choose Report --> Data Sources from the menu and remove one of the duplicate datasource names.

In a related error, you may notice that you can't use some fields that are part of your dataset.  For example, in my app, I had a field called "CreatedDate".  This was a public property of my object, and it showed up in my data source that I was using to bind to the report, but the report refused to acknowledge it.

So in this case you need to delete the original datasource using the same steps as above, and do something to cause it to add a new copy of the correct datasource (like drag a field onto the report).

Recently I have been making some changes to our SQL Server Reporting Services machines.  I am finding that many of my changes are not taking effect on the server.

The deployment goes ok.  The reports show that they have been updated, but some things seem to not transfer.

For example, I changed the data source on some reports and redeployed them, but this new setting did not make it to the server.  If I delete the report and THEN deploy it, it works fine.

Another issue was with some reports where I was trying to change some of the parameters to take a default null value.  I made the changes in my reports and deployed.  The reports now ALLOWED a null value, but they were not setup for the null to be the default value. 

Once again deleting the report and deploying fixed the problem, but this is stupid.

Has anyone else had this problem, or have know of the reason why it doesn't work right?

UPDATE:

I got a response from someone on this.  Apprently Microsoft felt that certain changers like changing report parameter details, could cause existing report parameter settings to be overwritten (well duh) and you as a developer probably didn't realize what you were doing (hmmm yes I did) and so they don't update everyone on the server when you redeploy a report.

Brilliant!  No warning, no message, just some things are not updated.

Has anyone been getting these errors when serving up SQL Reporting Services Reports?

A connection that was expected to be kept alive was closed by the server

All of a sudden we have been getting a lot of these, and I am not sure at the moment what is causing them.

We have 2 webservers that are hosting the reportviewer control, which loads up reports from a single reporting server.

This guy suggested that this problem was related to some 10 minute timeout, but I haven't been able to replicate his 10 minute problem.

Some other people are talking about overrideing the webservice proxy classes to set KeepAlive = false, but we have no webservice proxy to override as we are using the reportviewer.

Update:

This guy is talking about changing the SSRS session timeout.

Could "rc:Toolbar=false" be the problem?

Update 2:
Looks like this was all caused by the introduction of an IDP by our network guys.

Are you getting one of these errors?

Could not load file or assembly Microsoft.ReportViewer.WebForms

Could not load file or assembly Microsoft.ReportViewer.Common

Could not load file or assembly Microsoft.ReportViewer.ProcessingObjectModel

The problem is that some of these assemblies are supposed to be in the GAC, or at least when you create a basic application using .net, the application is expecting to find them.

You can deal with this in one of 2 ways.  You can find and download the ReportViewer.exe from Microsoft on the clients.  Or, you can copy the missing DLLs out of the GAC on your computer, and add them into your project.

Because I didn't want to have to download extra software to my clients, I decided to take the second option.

To do this, follow the instructions here:

http://www.chrismay.org/2006/08/09/Accessing+DLLs+Stored+In+The+GAC.aspx

To pull the dlls out of the GAC (you can just copy them).

Then add them into your project at the root level (not as references, but just like you were adding another file).  Then, in visual studio, highlight them and select "Copy always" as the option for the "Copy to Output Directory" field.

This will make sure that the DLLs are copied into the BIN directory for deployment.

We recently ran into a problem with SSRS where our email subscriptions were processing, but no emails were going out.

SSRS doesn't really give you any info in the web interface to give you any clue that something isn't work right, let alone help you figure out what the problem is.

I was able to solve the problem by looking at the various log files that SSRS creates:  ReportServerWebApp__X, ReportServerService__X, and ReportServer__X.

In these config files I was able to find some errors coming back from our mail server. 

This lead me to discover that the problem was with how the email addresses were formatted. 

The issue was that we recently changed which mail server we used for sending out these emails, and we have moved from an Exchange 2003 server to an Exchange 2007 server.

Exchange 2003 would accept domain accounts as destinations for emails, so you could send an email to "cmay" and it would be delivered.  It seems that Exchange 2007 requires that you provide the full email address: cmay@company.com. 

After changing some of the SPs that served up the data, all worked once again.

What happens if you lose the RDL source file for your SQL Reporting report?  Or more accurately, what if your coworkers never check them into source control?

Well, you can get them out of the web interface of SSRS.

To do this:

1. Login into the /Reports of SSRS
2. Navigate to the report you want
3. Click the Properties tab
4. Then under Report Definition, click "Edit"
5. Save the RDL file to your local machine.

When working with a SSRS report, the report framework will already "Denull" everything.  But in some cases, for example when you are working with numbers, you might want to display a value in lue of the "" that is displayed for nulls.

Now you would THINK, that they would just use the same ISNULL syntax that everyone knows from sql programming.

HAHAHHAHA... of course they didn't do that.

Instead you have to use a combo of IIF and IsNothing like so:

=IIF(IsNothing(Fields!Quantity.Value),0,Fields!Quantity.Value)

It isn't THAT big of a pain, but I wish MS would sometimes just realize how much easier it would be for them to just create an IsNull function in their own codebase than make their users write this out.

Oh well.  I guess when it comes to problems with SSRS, this is waaaaaaayyyyy down my list.

Speaking of "my problems with SSRS"... when are you guys going to support TBLR text??  Drives me nuts.  Everyone does "vertical text" in TBLR format, for pretty much every application, but now in SSRS you are forced to use TBRL.  When you give these reports to engineers that is the very first thing they say "You need to turn this text around the other way".... yes I know, I got my degree in Civil Engineering with a focus on structure design, so when you come from a background of seeing all vertical text (like on any plans) as ALWAYS, by requirement, aligned in a TBLR manner, and then you are forced to cock your head the other way to read it, it is really a pain.

But I guess I would still like to see MS fix their *terrible* PDF rendering first.  I just LOVE watching my reports take 4x as long as in CR, and end up 100x bigger in file size than they are after being saved in acrobat (15 MB for a 1 page PDF, open in Acrobat, save the file again, down to 150KB, with no visible change in appearance or quality).

Here is an interesting article on codeproject about creating "drill through" reports.

I was having problems with getting RS to work correctly on a windows 2000 server.  I would browse to the page, and it would prompt me for my credentials.  I would use the Admin loign, and it wouldn't take, finally giving me an error like this:

Server Error in '/Reports' Application.
--------------------------------------------------------------------------------

Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\bin" is denied.
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.UnauthorizedAccessException: Access to the path "C:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportManager\bin" is denied.

ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}\ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.

I found someone who figured out the impersonation problem:

The problem happens when installing RS on Windows 2000 Server that is a domain controller.  RS doesn't do the setup correctly.  The basic steps to fix the problem are

1. Grant impersonate privilege to IWAM_<machine> account (see knowledge base article 824308).  Go to domain controller security policy  in administrative tools. Click security settings -> Click local policies -> click user right assignment.  In the right pane, double click impersonate a client after authentication.  In security policy setting window, click define these policy settings.  Click add -> click browse.  In select users or groups window, select IWAM account name and click add.  Then, click Ok -> Click OK -> Click OK.  At command prompt, type the following command: secedit /refreshpolicy machine_policy /enforce.  Then, type iisreset.

2.  Remove IWAM_<machine> account from guest group.  Go to active directory users and computers in administrative tools.  Open users folder.  In right pane, double click IWAM_<machine>.  Select member of tab.  Remove guest.  Click OK.

3.  Reboot.

4.  Run rsactivate.  From command prompt, change directory to C:\Program Files\Microsoft SQL Server\80\Tools\Binn.  The run following command: RSActivate.exe -c "c:\Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer\RSReportServer.config" -t.

See the following link for more details http://msdn.microsoft.com/library/default.asp?url=/library/en-us/RSINSTALL/htm/gs_installingrs_v1_8k82.asp .

For now, this seems to have fixed this problem.

Well, maybe it would be BT-LR, but either way, it seems reporting services gives you 2 ways to align your text.

LR-TB, and TB-RL (Left to right, top to bottom, and top to bottom, right to left).

The problem with this is of course, that no one writes (english) top to bottom, right to left.   When you write text in a vertical fashion, it is always bottom to top, left to right.

In other words, you tilt your head to the left to read it.

I found this post on the topic where it sounds like someone might have come up with a creative way to rotate the TB-RL text 180 degress.

I haven't tried, it, but it seems like the only hope out there.

SQL Server Reporting Services 2005 allows you to connect with a web based ReportViewer control.

This control needs to provide credentials for the reporting server to authenticate the user requesting the report.  By default, the account running the asp.net worker process is used, but you can use the code in this forum posting to create you own custom credentials.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=409854&SiteID=1

When you try to assign the web reportviewer control to a height of 100%, it displays as a very short (height wise) format.  For me, adding the style attributes to the form did the trick.

The solution to this problem was found on this page: http://msdn2.microsoft.com/en-us/library/ms252090.aspx.

Considerations for XHTML and ReportViewer Web Server Control

When using SQL Reporting services, you can format things such as dates and currency.

However, depending on the situation you might want to:

1. Show a report with culture X on a computer running culture Y.
2. Show a report using the same culture settings as the local computer.

To do (1), all you have to do is set the "Language" parameter of the local report in design time.

To get (2) to work, you need to set the Language parameter of the report as the expression "=User.Language".  This will set the report culture as the culture that the hosting program is running under.

If you have a program that you need to switch between different cultures, you can do so with the following line of code (which changes the culture to "English-Ireland")

System.Threading.Thread.CurrentThread.CurrentCulture = New Globalization.CultureInfo("en-IE", False)

More information can be found at: http://msdn2.microsoft.com/en-us/library/ms156493.aspx

There are not many good examples of how to do stuff like this.  So the article below was all the more helpful when I was building a somewhat complex report.

http://www.codeproject.com/dotnet/AdoNetForOopPart2.asp?df=100&forumid=258341&select=1482558&msg=1482558