“Parameter is not valid” error in SSRS after adding a tooltip

Symptoms

All other tooltips work, but when you add it to the series, as soon as you hover the mouse over the chart the report errors out with “Parameter is not valid.”  It turns out this only happens with Stepped graphs.

This only happens in Visual Studio.  If you Preview the report, or press F5 to run it, you will get the same result.  However, if you deploy the report to a report server, it runs fine without an error and the tooltip displays properly.

Easy to Reproduce

We investigated some more and were able to reproduce the problem on other PCs (all using Visual Studio 2010 / SSRS 2012).  Obviously this is a bug in SSRS.  You can easily recreate the problem yourself:

    1. Create a new report
    2. Put this query in your dataset:
SELECT 188292 AS ListingID, 4 AS NumLeads
UNION ALL SELECT 179922,6
UNION ALL SELECT 77439,7
UNION ALL SELECT 185587,11
UNION ALL SELECT 78421,11
UNION ALL SELECT 188279,14
UNION ALL SELECT 85894,14
UNION ALL SELECT 44601,20
UNION ALL SELECT 12820,22
UNION ALL SELECT 183158,23
  1. Create a new chart in the report, a standard line graph
  2. Add NumLeads to the Values of the chart
  3. Go to the properties for the Series and set the Tooltip to Sum(NumLeads)
  4. Preview the report and confirm it displays properly
  5. Change Chart Type… to Stepped
  6. Preview the report and the error will occur

Solution/Workaround

The workaround is simple: do not use a Stepped graph.

However, if you really want to use a Stepped graph, it may be possible.  We were able to get a series tooltip to work on a Stepped graph in one instance.  This was on a chart that had already been created and configured on an existing report.  Apparently, if the chart is configured in certain ways, this problem will not occur.  But with a newly-created chart, the error occurs every time.  You may just have to fiddle with the settings to get it to work.

This is clearly a bug in SSRS.  We submitted a bug report to Microsoft Connect.  If you found this page because you experienced the same problem, be sure to go to Microsoft Connect and add your vote: https://connect.microsoft.com/VisualStudio/feedback/details/786718/parameter-is-not-valid-error-in-ssrs-report-after-adding-a-tooltip

Update: Microsoft responded to the bug report and said they no longer support problems with Visual Studio 2010.  Oh well! :(

Special thanks to Lance England who helped me investigate this issue (check out his blog).

How to Handle Divide-by-Zero in SSRS

The Error(s)

How do you handle divide-by-zero errors when doing division at the report level in SSRS?  Unfortunately, a single IIf statement does not work because it evaluates both results before deciding which one to act on.  For example, when the denominator is 0, this returns an error:

=IIf
    (
     Fields!Denominator.Value = 0
    ,NOTHING
    ,Fields!Numerator.Value / Fields!Denominator.Value
    )

(Actually, it either returns “#Error”, “Infinity”, “-Infinity”, or “NaN” depending on the data types involved.  My next blog post will provide more detail on that.  Let’s assume in this blog that the numerator and denominator are both DECIMAL, which will result in “#Error” when the denominator is zero.)

Pros and Cons

Here are the pros and cons of the different techniques.  If you have some more insight on one of these, or know of another technique, let me know in the comments.

DivideByZero_ProsAndCons

 

Technique #1 – Nested IIf Statement

The first and most commonly used workaround is the nested IIf statement:

=IIf
    (
     Fields!Denominator.Value = 0
    ,NOTHING
    ,Fields!Numerator.Value / IIf
	                         (
	                          Fields!Denominator.Value = 0
	                         ,1
	                         ,Fields!Denominator.Value
	                         )
    )

As you can see, this works because when the FALSE condition of the first IIf is evaluated, it does not try to divide by zero.

Technique #2 – Math Trick

=IIf
    (
     Fields!Denominator.Value = 0
    ,NOTHING
    ,Fields!Numerator.Value * (Fields!Denominator.Value ^ -1)
    )

As you know, in algebra:
X * (Y ^ -1)  =  X * (1 / Y)  =  X / Y

So it is the same as dividing by the denominator.  But why does it work when the denominator is 0?  This works because (0 ^ -1) evaluates to “Infinity” in SSRS regardless of the data type.  Be aware this does not work in other places, like T-SQL:

DivideByZero_TSQL

Or the plain ol’ Windows calculator:

DivideByZero_Calc

Or the TI-83 calculator:

DivideByZero_ChuckNorris

Interestingly, however, it does work in PowerShell:

DivideByZero_PowerShell

I would never expect this to work because it doesn’t really make mathematical sense!  Dividing by zero and raising 0 to the power of -1 are supposed to be the same thing.  If you are a stickler for math, you may be a little hesitant to use this trick in your reports.

Technique #3 – Custom Code

You may prefer to return 0 instead of NOTHING.  If so, you have the additional option of using custom code:

............................
Public Shared Function SafeDivision(ByVal numerator as Decimal, ByVal denominator As Decimal) As Decimal 
          If denominator = 0 Then
              SafeDivision = 0
          Else
              SafeDivision = numerator / denominator
          End If
    End Function
............................

=Code.SafeDivision(1, 0)

This allows for very compact syntax in your expression.  But it cannot return NOTHING.  Even when you try to return NOTHING as below, it still returns 0:

............................
Public Shared Function SafeDivision(ByVal numerator as Decimal, ByVal denominator As Decimal) As Decimal 
          If denominator = 0 Then
              SafeDivision = NOTHING
          Else
              SafeDivision = numerator / denominator
          End If
    End Function
............................

=Code.SafeDivision(1, 0)

 

Comments?

If you have some more insight on one of the techniques above, or know of another technique, please let me know in the comments.

 

Notes:
* SSRS behaves the same when dividing by NULL
** You get 2 points if you caught the Chuck Norris joke above

What are the newest certifications for SQL 2012?

Microsoft has changed their certifications for 2012.  As usual, they make it difficult to understand.  I compiled all this information and put it into one table.  I hope this helps any SQL people who plan on getting a certification.

(compiled from http://www.microsoft.com/learning/en/us/certification-overview.aspx)

 

Also note these statements from their FAQ:

  • The certifications have new names: MCSA, MCSE, MCSM, and MCSD (but that last one is not relevant to SQL)
  • Over time, the legacy MCTS, MCITP, and MCPD certifications will retire
  • Microsoft Certified Solutions Expert (MCSE) certifications will require recertification every three years

Cutesy Manifesto

Maybe I’m being picky, but when bloggers add “cutesy” things to their posts, it can be distracting and sometimes frustrating.  Sometimes it is a great idea to crack a joke or say something silly, but sometimes it gets in the way of the reader.  To avoid this problem on BIsmiths, I developed this manifesto:

The Golden Rule

Always remember that BIsmiths is all about the reader.  Every thing you add to a post must serve the purpose of delivering information to the reader.

The Good

Cutesy things are good when they:

  • Make the reader comfortable, e.g.:
    • break the ice in the beginning
    • lighten the mood throughout
    • smooth out transitions
  • Keep the reader from falling asleep
    • but do not treat the reader like an impatient child who has to be bribed with a piece of candy just to pay attention
  • Serve the overall purpose of providing information to the reader

The Bad

Cutesy things are bad when they:

  • Interrupt the flow of reading
  • Look like the author is just trying to show how clever he is
  • Take on their own purpose of just “being funny”
    • Being funny is good when it serves the overall purpose of helping deliver information to the reader
ClintEastwoodIsNotAmused
Clint Eastwood is not amused

The Beautiful

When in doubt, err on the side of less “cutesy.”  A beautiful post is worth reading, so you really don’t have to butter up the reader too much.  Be confident in your content and let it speak for itself.