Report Customization - Level 8 in the Stairway to Reporting Services

Question

Report Customization - Level 8 in the Stairway to Reporting Services

Jessica M. Moss

SSC Eights!

Points: 986
More actions
April 11, 2012 at 12:01 am

#272116

Comments posted to this topic are about the item Report Customization - Level 8 in the Stairway to Reporting Services

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 1

Fantastic work Jessica. Can anybody suggest some sites that demonstrate clever uses of complex custom functions in SSRS?

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

Nice article. Good read, thanks.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

CGSJohnson SSCertifiable Points: 7208 More actions · Answer 3

I have really enjoyed this series. Thanks for sharing the knowledge!

- Chris

Rod at work SSC-Dedicated Points: 34007 More actions · Answer 4

Am I right in thinking that this article only pertains to SSRS 2008 and above?

Kindest Regards, Rod Connect with me on LinkedIn.

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 5

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?

>L<

Lisa Slater Nicholls Hall of Fame Points: 3455 More actions · Answer 6

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.

>L<

Rod at work SSC-Dedicated Points: 34007 More actions · Answer 7

That's good information to know, Lisa, thanks.

Kindest Regards, Rod Connect with me on LinkedIn.

colin.hodges SSC Journeyman Points: 76 More actions · Answer 8

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.

Skanker Hall of Fame Points: 3059 More actions · Answer 9

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

GPO SSCarpal Tunnel Points: 4574 More actions · Answer 10

In some cases we've even gone a layer of abstraction further than putting the formatting logic in the sql, and put it in an underlying metadata table. For example where your KPI gauges all have different threshold percentage rates for poor/fair/good performance. For KPI1 the colour change from poor to fair might be at 50%. For KPI2 it might be at 60% and so on. You don't want all that logic tied up in a big ugly CASE WHEN THEN statement in SQL, or worse, a switch function in an SSRS expression, or even worse, several nested Iif()s. If it's in a table, there's no need to change any SQL and no need to redeploy the report (testing first of course). Just change a value in the table and you're done.

...One of the symptoms of an approaching nervous breakdown is the belief that ones work is terribly important.... Bertrand Russell

dkjnsdf SSC Rookie Points: 32 More actions · Answer 11

n2012 comes, in order to thank everyone, characteristic, novel style, varieties, low price and good quality, and the low sale price. Thank everyone

==== ( http://www.fullmalls.com ) =====

$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

$30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

$15 Ed Hardy ,LV ,Gucci Bikini

$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

$10 Gucci,Ed Hardy sunglasses

$9 New Era caps.

give you the unexpected harvest

==== ( http://www.fullmalls.com ) =====

sdkjfhiweiu SSC Rookie Points: 29 More actions · Answer 12

n2012 comes, in order to thank everyone, characteristic, novel style, varieties, low price and good quality, and the low sale price. Thank everyone

==== ( http://www.fullmalls.com ) =====

$33 True Religion jeans, Ed Hardy jeans,LV,Coogi jeans,Affliction jeans

$30 Air Jordan shoes,Shox shoes,Gucci,LV shoes

50%Discount winter fashion :Sandle,t-shirt,caps,jerseys,handbag and brand watches!!!

$15 Ed Hardy ,LV ,Gucci Bikini

$15 Polo, Ed Hardy, Gucci, LV, Lacoste T-shirts

$25 Coach,Gucci,LV,Prada,Juicy,Chanel handbag,

$10 Gucci,Ed Hardy sunglasses

$9 New Era caps.

give you the unexpected harvest

==== ( http://www.fullmalls.com ) =====