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/

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

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

 

 


 


 

SSRS Dashboarding: a ‘webdesign-look’

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

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

Koos discusses the changes he made to my dashboard on his blog:
http://www.msbiblog.com/2016/01/28/ssrs-non-ugly-5-the-nitty-griddy-details/#more-402

Here are both dashboards in full size:

 





 


 


 

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

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:

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:
2015-12-01

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,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)

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].&["
+CSTR(DATEPART(DateInterval.Year,CDate(Parameters!DateFrom.Value)))+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Month,CDate(Parameters!DateFrom.Value)))
)+"-"+
IIF(LEN(CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))) = 2,
CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value))),
"0"+CSTR(DATEPART(DateInterval.Day,CDate(Parameters!DateFrom.Value)))
)
+"T00:00:00]"

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

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

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
Else
Return dividend / divisor
End If
End Function


And here’s how you should use it:
=Code.Divide(X,Y)

Other more creative solutions are:
=REPLACE(X/Y,"NaN","0")
Or:
=IIF(Y = 0, 0, X/Y)
Or:
=Switch(
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()
Try
Return Report.User!UserID
Catch
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.

Copy/Pasting elements in SSRS…argh!

Report Builder was unable to paste successfully.

Hmmm….that one buggers me.
Turns out that it doesn’t like have custom code in elements which you would like to copy/paste.
It’s a known issue for years and has been reported, but they decided not to fix it…
https://connect.microsoft.com/SQLServer/feedback/details/767968/visual-studio-2008-unable-to-copy-and-paste-textboxes-in-ssrs-reports-with-custom-code-after-2008-r2-sp2-upgrade

So what’s the work-around you might ask? Well…you’ll need to comment out all the ‘Code.’-sections in the XML code of the report.

Workaround:

Step 1) Open up the XML view for the report, either in Visual Studio by right clicking the report and selecting View Code, or by editing in any text editor.
Comment out Code. blocks. Ctrl + F your way through the document looking for Code.. The goal is to preserve the code in some way, while temporarily commenting out the line. Depending on your actual code, this might be different, but I here’s what I like to do:

Step 2)
Find: =Code.
Replace With: =’Code.

Step 3) Go back to the Designer View and Copy and Paste the Tablix. You can do this by right clicking the top left corner of the tablix control: Copy Tablix

Step 4) Go back to the XML View and reverse the find and replace
Find: =’Code.
Replace With: =Code.

Visual Basic code
Another things it that is has a habit of putting Visual Basic code in front of your SSRS expression, like for example:

Microsoft.VisualBasic.Information.
Microsoft.VisualBasic.Interaction.
Microsoft.VisualBasic.Strings.
Microsoft.ReportingServices.RdlObjectModel.ExpressionParser.VBFunctions.


When you copy/paste anything, afterwards view the XML code and just replace remove these strings with Find&Replace.

ReportItems!Textbox.Value
In case your using ReportItems!Textbox.Value, make sure to check a few that Visual Studio ‘changed’ the number accordingly. Don’t know why, but most of the time it does understand the logic and also adjusts the textbox-numbers accordingly. But sometimes it doesn’t 🙂

SSRS: SQL Agent Job Status report

I’ve created a report that shows a little more information than the SQL Agent Activity Monitor and it can be scheduled via SSRS:

Download:
Click here to download the files:
SSRSSQLAgentJobsReport.zip

Here’s another report of mine which you might find interesting:
SSRS: SQL Agent Job Schedule Timeline report

SSRS: Easily calculate the difference between two fields/textboxes with an expression

This might seem to be a simple approach, but for some it takes a while to figure out that such tricks are even possible in SSRS.
For example to calculate the difference/variance between Actual and Budget values in a report:

=ReportItems!Textbox50.Value - ReportItems!Textbox171.Value


Simply lookup the textbox’ name in the Properties-window:

This approach also works for ReportItems outside of the table/matrix (across the report even).
In some cases you’ll need to combine two KPI values from different matrixes/tables in the report and for example run another formula to come up with a definite rating/value.

Instead of ‘Fields!‘ use ‘ReportItems!‘ to refer to actual items on the report canvas (this can be on the header, footer or body of the report).
In my experience you may need to use a temporary hidden textbox to store the value temporary in order to use it in the header or footer. So in case you run into some sort of constraint, try this one out first.