What's underneath can bite hard

by Lisa Nicholls Sun, February 13 2011 18:32

Here's a cautionary reporting tale for you...

A couple of weeks ago a colleague asked for help with an RDL.  It was dog-slow, and there were about a hundred reasons for this, some fixable and some not.  But, in addition to the need for optimization, it would occasionally fail for reasons that had nothing to do with timeouts, and couldn't be reproduced in the Report Designer development environment.

Remember our plan to get to know more than what's on the surface, from my last post?

It took a while (embarrassing) but I finally took a look at the Reporting Server logs.  One attendance percentage calculation had a very rare divide-by-zero condition. Occasionally, a kid had not attended even one day in the period. 

Duh. Once we finished slapping our heads, it was an easy fix.

Could happen to anybody right? 

Sure, Anybody who thinks that Reporting is somehow immune to the requirements to follow sound development practices -- in this case, bounds checking. (FWIW, you'd be amazed at how many Anybodies think Reporting is somehow alien to "real" development best practices.  I've never quite understood this.)

Even so.  Anybody should have been able to diagnose this rather quickly from the log.

If by some chance you don't know where to look, you'll find them here, pretty much where Anybody would expect <sigh>:

... and the text files you'll find are not beautiful, but they're not mysterious, especially when the condition you're tracking is rare enough that you should be able to pinpoint the time matching your error pretty well.

I saw a license plate holder a couple of weeks ago that said something like 'tailgate me, and I'll divide you by zero". I think I want one of those.

Transformers: More than meets the eye

Continuing our focus on fundamentals here, a gentleman named Vince wrote recently to ask:

Hi,  I have been following your instruction in the post "Changing the Sheet names in SQL Server RS Excel: QnD XSLT". When I get to step 4. I won't be able to find the data output tab in SSRS 2008. I attached the screen capture. Can you let me know where should I put the xslt file name in SSRS 2008?

Vince, I hope you see the answer in this post, since I tried to reply to your email but apparently you didn't provide me with a valid email address (I tried several times): 

I've answered your question in this post, complete (I hope) with appropriate screen shots to remove any difficulty about where 2008 sticks the Transform information for an RDL, both in the dialogs and in the schema.  If you've read it and still don't see, please write again, and this time give me a real email address. 

Defensive driving

Even with the move up to BlogEngine 2.0, I'm not feeling comfortable about turning comments back on after the deluges of spam in November-December.  Sorry about that. 

But I'm still here, and you can still ask questions -- I'm always pleased to help.  The ground rules are:

  • Steps to repro, if possible with a generic RDL that uses AdventureWorks or something, are always nice
  • Make your question specific.  One of the reasons I ask for a generic RDL is that, in trying to isolate the problem to tell me about it, you'll discover the solution yourself.  When it's embedded in a large, complex RDL, it may be a side effect of something else in that report.
  • If you really can't isolate the problem in a generic RDL, I'll cope.  The interactions may be interesting, and I'll help you figure them out (I just want to know that you did your best on your own, first).  Give me your real RDL, in this case.  I won't need your data.
  • If I can help you, I will share the resolution with the folks who read this blog.  I won't share any private details about your business, but I do this gratis on the condition that more than one person gets the benefit.
  • And, finally (pace Vince)... I'm probably going to need to contact you to get this done, so gimme a real contact email, okay?

As they used to say on Hill Street Blues...

Let's be careful out there.

Tags:

Reporting | SQL Server | XML/XSLT