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
)

Advertisements

Cortana Analytics Suite videos

Here you’ll find a couple of videos from Microsoft about the new Cortana Analytics Suite (which combines Azure ML with Power BI and Cortana speech recognition):

Dartmouth-Hitchcock revolutionizes the U.S. healthcare system



Setting a new land-speed record with Cortana Analytics Suite



Of course there are also a few funny commands in Windows 10 which you can try 🙂
Windows 10: Cool Cortana Voice Commands



So where does Cortana come from? Halo!
Halo 4: Cortana Death Scene



Cortana Analytics Suite

I am Cortana

The Cortana Analytics Suite is soon available on Microsoft Azure!
Simply said…it combines the existing Azure ML features, Power BI (e.g. dashboarding and Q&A) with the Cortana speech recognition (as seen in Windows 10).


More info about the Cortana Analytics Suite (presentation screenshots from Microsoft):

Cortana Analytics Suite - Power BI with Cortana

Cortana Analytics Suite - Realtime Health Dashboard example

Cortana Analytics Suite


Cortana Analytics - Transform data into intelligent action


Cortana Analytics scenarios






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

ssrs-xml-buffer-overflow-error





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.

ssrs-xml-buffer-overflow-error-non-existing-textbox-items





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:

ssrs-xml-buffer-overflow-error-which-explains