Category Archives: Reporting

SSRS report fails with vertical text

Posted on by

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.

Getting error reports when a SQL Reporting Subscription Fails

Posted on by

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.

 

SQL Reports RDLC Errors

Posted on by

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).

Why Do SSRS Deployments Not Update Everything?

Posted on by

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.

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

Posted on by

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.

 

Could not load file or assembly Microsoft.ReportViewer.XXX

Posted on by

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.

 

SQL Server Reporting Services Email Subscriptions Not Going Out

Posted on by

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.

 

Getting the RDL files out of Sql Server Reporting Services (SSRS)

Posted on by

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.

 

Dealing with null data in SSRS

Posted on by

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).