GPO (4/11/2012)Fantastic work Jessica. Can anybody suggest some sites that demonstrate clever uses of complex custom functions in SSRS?

Um, my blog (http://spacefold.com/lisa) has, at current count, 63 entries in the SQL Server category and 78 in the Reporting category. Some Reporting entries are obviously not SSRS, but of those that are, most use custom code of one type or another, and a lot of them use complex custom functions, sometimes in bizarre ways.

I generally write my blog posts in response to a reader's question. If you have a specific type of code you're looking for, I'll check and see if there is an entry that pertains to that area... if not, I might write a post that shows it . My past and present work has afforded me with a *lot* of real-life opportunities to come up with examples, both common and uncommon.

Here's an example of using custom code to solve a common real-life problem: http://spacefold.com/lisa/post/2007/12/23/A-Pedestrian-Piece-of-Code-Goes-Walkabout.aspx

Here's an example of custom code doing something quite a bit more unusual, but still a generalized problem-to-solve, it seems to have helped a number of people:http://spacefold.com/lisa/post/2008/06/22/Walkthrough-A-bar-chart-trick-examined-and-a-charting-alternative-offered.aspx

Here's an example of custom code actually writing the SQL statement that creates a dataset (and some explanation of why I occasionally do it) :http://spacefold.com/lisa/post/2007/11/01/Writing-Dynamic-SQL-in-and-for-RDLs.aspx

... there are many, many other examples (some bizarre and specific, but many generally useful).

I've been thinking of writing a walkthrough on how I use custom code to localize reports. Would this be of any interest to anybody?

Rod at work (4/11/2012)Am I right in thinking that this article only pertains to SSRS 2008 and above?

Rod, I'm sure the author will answer, but I did not notice anything that you couldn't do in SSRS 2005 - I won't answer for SSRS 2000, which was pretty primitive.

I *will* say that, when you move between SSRS versions, you need to check certain aspects of custom code, and even complex expressions, very carefully. This is not because the ability to *use* the expressions and code has changed, but because the order of evaluation of items may, in certain cases, have changed.

This problem of a different order of evaluation also exists within a single version of SSRS when you look at the results of your report using different renderers (export to Excel or PDF, versus HTML, etc).

However,I don't mean to warn you off from using this stuff -- you will rarely encounter a problem of this nature unless your code *expects* a certain order of evaluation. Most code, like the code in the article, doesn't have side effects, and affects a single report item atomistically, and is not affected by this. The only exceptions you'll see more generally will be code that is in page headers and footers, since (in my experience) these are evaluated and handled at different times by the different renderers.

Interesting info however at my work I discourage use of report custom code. Generally the logic to turn a textbox red or green is better contained in the underlying SQL which of course is where all the other logic sits. Keeping it all in the one place is easier to write and quicker for other dev's to pick up. For example I'l derive a column named ThresholdExceeded and simply stick a 1 in there for use with a very simple IIF statement against the color proterty in the report. Recently I had to chart the middle value for the month only which could have been the 14th,15th or even the 21st. Far better to T-SQL it rather than just through VB hoops.

I think that there is nothing wrong with using custom code within reports as long as it is documented somewhere for others to pick up. Also with a bit of experience on working with reports the developer does start to get a feel for where to look to resolve issues.

For some reports we have there are way too many different cosmetic tweaks that to cover them all in the SQL would be just as much a headache. Though I guess this is just my opinion.

If there is no documentation for the report (or series of reports) it does slow down any future developments. I have just been amending a report done by someone else, without any documentation, and it did cause me to bang my head on the desk a couple of times. Still this just gives me the excuse to take the proverbial out of the original developer (I can alo get on my high horse because of course I always document it all - not!!)