Waste of digital real estate?

Some of you already know that I fancy Pie Charts. Indeed they are a waste of digital real estate, but it somehow brakes the static look and feel of dashboards with just tables and Line Charts. (Looking forward to the new types in SSRS 2016 btw!)

For now, anyone who would like the recreate this one: http://blog.hoegaerden.be/2009/10/25/pie-chart-techniques/

I do fancy Pie Charts

And what do you think about maps? A nice addition or a waste of digital real estate?

Geo Map - SSRS 2012 2014

Two dashboards I’ve made for my clients, all of them are extremely happy! 🙂
(Both can be made on SSRS 2008R2, 2012 and 2014)

BumbleBI Dashboard example


BumbleBI Dashboard - SSRS 2012 2014




SSRS Dashboarding: a ‘webdesign-look’

Couple of weeks ago, I shared my SSRS dashboard with the community on LinkedIn:

SSRS Dashboard shared on LinkedIn

Koos van Strien asked for the source (RDL) and restyled the dashboard with his ‘webdesign-look’. Pretty nice!

SSRS Dashboard response on LinkedIn

Koos discusses the changes he made to my dashboard on his blog:

Here are both dashboards in full size:

SSRS Dashboard Clint Huijbers


SSRS Dashboard Koos van Strien




SSRS: How to use a date/time parameter as an input for an MDX query (instead of a list of text values)

I guess we’ve all seen it before, VS/SSDT/BIDS automatically generates datasets for input parameters (which is fine to start with).

But for Date/Time parameters it always (by default) selects the ‘Text’-datatype (drop-down list), instead of the ‘Date/Time’-datatype (nice calendar).

What we want to achieve
So this is what we want to achieve (in red):

SSRS datetime input parameter instead of text for mdx

Start a clean slate
First step is to drop the generated date/time parameter(s) we’re not going to use and create a new one:

SSRS datetime input parameter instead of text for mdx - properties

Make sure to select the ‘Date/Time’ as the datatype, which enables the nice calender-type input. Leave the ‘Available Values’-section unchanged/empty.

Hussle the Date-value in the correct format (YYYY-MM-DD)
A difference between both datatypes is that the ‘Date/Time’-datetype will return values formatted based on your local/regional settings (e.g. YYYY-M-D or in my case D-M-YYYY).
Where I need it to be YYYY-MM-DD (including 0’s), so something like:

To achieve this, we need to cast/convert a little bit and include a ‘0’ when we need it:

=IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,

Turn it into a MDX value
The final step is to get it into the desired value for MDX:
[Dim Creation Date].[Date].&[2015-12-05T00:00:00]

Here’s the full code expression which you can use as a parameter value (expression):

="[Dim Creation Date].[Date].&["
IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
IIF(LEN(CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))) = 2,

Use the expression as an input value for your dataset
The final step is to use the expression:

SSRS datetime input parameter instead of text for mdx - dataset properties

That’s it! Bye bye ugly drop-down list 🙂

SSRS datetime input parameter instead of text for mdx - final result

SSRS Drill-through: passing multiple values to another report (MDX list of values)

When I build large dashboards, I often fall back to basic stored procedures to combine datasets, but also to be able to manipulate parameter values (month, first week, previous week, previous year totals, etc.).
An annoying thing is that when you want to create a drill-though action to another report which uses MDX, you’ll need to magically join these values.

Well…here’s how you could achieve it.
Let’s say I have these three values (keys): XXX, YYY and ZZZ

MDX expects a prefix and a suffix (according to the dimension properties/levels).
So here’s what MDX expects:

[Dim Customer].[Customer Number].&[XXX]
[Dim Customer].[Customer Number].&[YYY]
[Dim Customer].[Customer Number].&[ZZZ]

Important: MDX expects different objects/rows, like you need to use separate rows in VS/SSDT for the default parameter values.

Your dataset returns the basic key values, first step is to join these (including the prefix and suffix):

="[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]"

The results is:

[Dim Customer].[Customer Number].&[XXX],[Dim Customer].[Customer Number].&[YYY],[Dim Customer].[Customer Number].&[ZZZ]

Which seems correct, but MDX isn’t expecting a multi-valued string (compared to SP’s).

Final thing, you’ll need to do is to split it again in order to provide a record/row per value:

=Split("[Dim Customer].[Customer Number].&["+JOIN(Parameters!Customer.Value,"], [Dim Customer].[Customer Number].&[")+"]",",")

SSRS: NaN, Infinity or even #Error

I’ve seen many IIF’s, CASE-statements, +0.00001 additions and others to suppress or solve one of these ‘errors’:
NaN, Infinity or even #Error

Let’s start with the basics, when do these things emerge and why?
Normally, you get NaN when trying to divide 0 / 0 or Infinity when you are dividing any number by 0.

I personally use this function to overcome such obstacles:
Public Function Divide(ByVal dividend As Double, ByVal divisor As Double) As Double
If IsNothing(divisor) Or divisor = 0 Or IsNothing(dividend) Or dividend = 0 Then
Return 0
Return dividend / divisor
End If
End Function

And here’s how you should use it:

Other more creative solutions are:
=IIF(Y = 0, 0, X/Y)
X/Y = "NaN",Nothing,
X/Y = "Infinity",Nothing,
X/Y = "Infinity",Nothing

SOLVED: “The size necessary to buffer the XML content exceeded the buffer quota” (SSRS)


OMG…I hate this one.
Most of the times when you’re using textbox references (like: ‘Reportitems!Textbox33.Value‘) and when I think you press ‘Delete’ on certain cells, VS automatically renews the textbox-numbers. So things get messed up.


So…check if any of your report items are referencing non-existing textbox items, non-existing paramters or fields that are not in existing dataset scope.

Funny thing is that when you start deleting other report items (of which you think they work properly), VS somehow makes up his mind by giving you a decent error message:


Data driven subscriptions cannot be created because the credentials used to run the report are not stored,the report is using user-defined parameters values, or if a linked report, the link is no longer valid.

Well SSRS is stil bugging you huh?


There are a few things you may need to check:

1) Make sure that you’ve stored the credentials of the data source IN the report

2) Since the data-driven subscription is running without an actual user-context, the use of User!UserID is not allowed.

Start by adding this custom code to your report (under ‘Report Properties‘ -> ‘Code‘):

Public Function UserName()
Return Report.User!UserID
Return "System"
End Try
End Function

Now you need the find: User!UserID

And replace it by: code.UserName()

in the Code-view of the report.