Monday, July 20, 2009

SQL Server Reporting Services Chart – Custom Y Axis Label

July 20, 2009 Posted by Jason Irwin , , , No comments

Of all the tools in my developer toolbelt none frustrates me the way SSRS frustrates me. Don’t get me wrong – it can be extremely powerful and given that it is packaged with SQL licensing becomes a little easier. But I have NEVER come across a tool that took so many dirty hacks, neat tricks or insider gotchas to get the job done. And I fear for the day that these combinations of hacks, tricks and gotchas need to be maintained but nobody remembers why things were written the way they were written….

Recently I worked on a report (actually, only part of a report) that required what seemed like a simple chart. The goal was to show some information over time – X-axis the labels should contain the date, on the Y-axis we should have three intervals with the labels bad, good and great respectively. ‘Bad’ corresponds to a 0 value, '’Good’ to a 50 value and ‘Great’ a 100 value. Easy, right??? Wrong!!!

What I soon learned is that there was no way to achieve this goal using any of the Axis, Chart or Series properties in SSRS. Luckily a fix was found, and I'll let you decide if it is a hack, trick or gotcha…

An example:

For this example I’ve created a simple table called yearly_profit with a date (year) and int (profit) field and populated it with 4 records. Please note, this is an extremely simple example used to illustrate a solution rather than a real-life report.

image

The report itself is written in SSRS 2008 and uses the following query to pull the data from my yearly_profit table.

  1: select year, profit from yearly_profit


On the report itself I have a single (Error Bar) chart with the profit as it’s value axis and the year as it’s category axis. I modify the min/max and interval values to 0, 100 and 50 respectively and get a graph similar to that below.



image



Now it should be a simple matter of renaming the 0, 50, 100 intervals to bad, good and better, right? Wrong! This is where the trouble is. There is no pre-defined field used to perform this task and the .NET string formatters that allow you to format the text are true to their word – they allow you to format – not manipulate!



Desperation



It is strange to be so close to a goal without being able to reach it. After pushing the UI further than I knew how to push it, and attempting to manipulate the RDL code itself in hope of finding somewhere where a string replace could be used I resorted to Google – only to find that no solutions were openly available.



I like to give credit where it is due and all credit goes to the wonderful  http://www.stackoverflow.com.  



After posting a bounty I finally found a fix that worked for me:



http://stackoverflow.com/questions/942086/reporting-services-chart-custom-axis-label



The Solution



In order to use text data instead of the numeric data I had to modify my min and max interval values for the Y-axis to –50 and 50 respectively. They looked a little something like this:



image



Then, I had to normalize my data to reflect this change. This was simply a matter of subtracting 50 from each of my Center, Lower Error and Upper Error values.



The values became the following expressions:



Center Value: =Sum(Fields!profit.Value) - 50



Lower Error: =Sum(Fields!profit.Value) - 10 - 50



Upper Error: =Sum(Fields!profit.Value) -50 + 10



Please note: the lower and upper error values are used because this is an Error Bar chart and do not appear in most chart types. the –10 and +10 in corresponding expressions is arbitrary for illustrative purposes.





Normalizing the data left me with a similar chart to before, but the Y-axis values now range between –50 and 50 instead of 0 and 100.



image





Finally, we are able to use these values to format the interval labels with an Excel format code (pos;neg;zero). In our case we use the expression ="Great;Bad;Good"



image



image





The Result



The result is quite simple considering the required work and the below screenshot shows the chart with the newly added text labels.



image



Luckily for me, i only needed 3 intervals. If  anyone out there has an easier way to do this I would LOVE to hear about it!

Sunday, July 19, 2009

uncaught exception ReferenceError: AjaxControlToolkit is not defined

I was working on an ASP.NET side project and, upon deployment, was unable to perform a number of functions that worked just fine in my dev environment – all were GUI related and all seemed to point to issues with the Ajax Control Toolkit controls used within the site. Loading up the chrome (my  browser of choice) javascript debugger I noticed the following error message:

uncaught exception ReferenceError: AjaxControlToolkit is not defined

I am packaging the application using a Web Deployment Project and apparently the issue is related to the Ajax Toolkit’s Scriptmanager having difficulty in resolving assemblies – this makes sense because the Web Deployment Project is packaging the assemblies differently than they would look in the standard BIN folder. The workaround in my case was to update my code (a single line) to use the ASP.NET’s built in Scriptmanager instead of using the Ajax version. I had no defined reason to use the Ajax version instead of the ASP.NET version, so this doesn’t really pose an issue – though I do plan to play with the Web Deployment Project and determine if it is possible to package the assemblies so that they can be resolved by the Ajax Scriptmanager.