SQL Server Reporting Services - Creating Professional Tool Tips

Creating tools tips in SSRS charts is very straightforward, simply right click on the series in the chart and then select Series Properties.

Then you can choose one of the fields in the data set as the tool tip using the drop down arrow.

This gives quite basic information but you can create a more customised tool tip using the fx button. Once you are in the function section you can

• Add labels
• Include the position on the X or Y axis
• Show multiple pieces of information on separate lines
• Format numbers using commas or decimal point accuracy

The code to achieve this is

="Channel: " + Fields!Channel.Value & VbCrLf + "Day" + ": "  + Fields!DayNameOfWeek.Value &  VbCrLf  + "Orders" + ": " +  FormatNumber(CStr(SUM(Fields!Tickets.Value)), 0)

The spaces between the lines can be created by & VbCrLf

When using numbers you must cast it as a string using CStr() around the field value. By wrapping the string with FormatNumber( , 0) you can format the way a number appears. This will include a comma between thousands and not include any decimal places. If you change the 0 to 2 you will include two decimal places.

All of the strings can be concatenated together using the + character. Labels can be created by enclosing text in between quotation marks. 

SSRS - Order By Day Of The Week

In SQL Reporting Services there is no out of the box way of order a report or chart by the days of the week. If you try to order by the name you end up with the days alphabetically sorted which is not very helpful.

One of the ways around this is to click use a switch statement in the Sort By section.

=SWITCH(Fields!DateNamePart.Value = "Monday", 1, Fields!DateNamePart.Value = "Tuesday", 2, Fields!DateNamePart.Value = "Wednesday", 3, Fields!DateNamePart.Value = "Thursday", 4, Fields!DateNamePart.Value = "Friday", 5, Fields!DateNamePart.Value = "Saturday", 6, Fields!DateNamePart.Value = "Sunday", 7)

After you have added this the days are now sorted in order and the data now has some visual value and it is much easier to spot when the trend in the chart.

DAX for DATEADD(DD, -1, GETDATE())

In DAX the equivalent of GETDATE() is NOW().


The DATEADD function varies depending on what time unit you are adding. To add or subtract days it couldn't be easier.


NOW() -1 takes a day from the current time. NOW() + 5 adds five days.


To add a month you use the EDATE function. So to add two months to the current date you use EDATE(NOW(), 2).


So to use DATEADD(DD, -1, GETDATE()) in a filter or calculate table you would do something like.


CALCULATETABLE('Contacts','Contacts'[CreatedOn] > now() -1 )

SSRS 2008 R2 - Alternate Bar Chart Colours

A simple Bar Chart or Column Chart in SQL Services Reporting Services (SSRS) that only has one category group can look a bit boring.

An easy way to make it a bit more interesting on the eye is to change the fill colour on the bars (or columns) for the alternating ones.

Right click on the series and select Series Properties.

Select Fill and then click on the expression button.

In the expression box simply paste in the same code you use to alternate rows in a table.

=IIF(RunningValue(Fields!County_Name.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0, "#4573D5","#7399EA")

In this case I'm using colour codes #4573D5 and #7399EA but you can also use names like this:

=IIF(RunningValue(Fields!County_Name.Value,COUNTDISTINCT,NOTHING) MOD 2 = 0, "LightGrey","White")

Your chart will now be a bit more pleasing on the eye.

KB2661254 Kills My Sharepoint Foundation Web Front End

The Windows Update KB2661254 took down one of my Sharepoint Foundation Web Server Front Ends today. Luckily I have split when my servers update so it did not take down my Farm.

This update only seems to be affecting the Web Front Ends on Windows Server 2008, the Web Front Ends on Windows Server 2008 R2 are unaffected.

This Windows Update is to fix a problem that Microsoft are having with minimum certificate key length. It is not a security update so I blocked it in WSUS and uninstalled it and now I am back up and running.

Be sure to test this update before putting it onto your live Sharepoint 2010 Farm.

Sharepoint 2013 - Sign in as Different User has been removed

The ability to sign in as a different user, which was very handy for testing permissions has been removed in Sharepoint 2013.

To get around this I had to use the old "Shift + Right Click" Run As Different User, which isn't quite as convienient.

Where is the Recycle Bin in Sharepoint 2013

In Sharepoint 2013 the link to the Recycle Bin is no longer in the quick launch.

To get to the Recycle Bin click on Site Contents and you will then find it in the top right corner.