Friday, February 28, 2014

SSRS Divide by Zero Handling Alternative

So in creating an SSRS report recently which contained percent calculations in the totals I came across a divide by zero error I could not get around.  All the checks for null/0 in the world wouldn't get rid using the expression editor.

Here is the formula for the detail level where it works fine:

Margin = (Price-Cost)/Price

and checking for zero values

Margin = Iif((Price-Post)=0 or Price=0),0, (Price-Cost)/Price

But for totals, it throws an error

Margin = (Sum(Price) - Sum(Cost))/Sum(Price)

Margin = Iif(((Sum(Price)-Sum(Cost))=0 or Price=0)),0, (sum(Price)-sum(Cost))/sum(Price)

SHOULD be straight forward,,,,, but not.  It appears to be too complex in some way for the expression to be calculated and just returns errors.

So, and I'm borrowing here, I'm going to repost something simple but brilliant that was posted by William Mendoza on his blog site:

In the Menu; got to Report > Report Properties > Code and paste the code bellow

Public Function Quotient(ByVal numerator As Decimal, denominator As Decimal) As Decimal If denominator = 0 Then Return 0 Else Return numerator / denominator End If End Function
To call the function go to the the Textbox expresion and type:

 in this case I am putting the formula at the Group level so I am using sum. Otherwise it would be:

Thanks William for your post and help

Thursday, March 28, 2013

Remove Spaces and Non Alpahnumeric Characters




CUSTNMBR LIKE '%[^a-zA-Z0-9 ]%'


Friday, March 1, 2013

Excellent Query Analysis Tool

I'm always looking for new tools for SQL. 

Here is one that truly sets the standard for Query Execution Plan Analysis:


The feature I like about it this best is that you can navigate your sql code and the analysis plan window stays in sync with where you are in the code.  Conversely, the code window is sync'd to the analysis plan. 

On top of this, the tool also provides tabular performance metrics of the various operations instead of the user having to hunt through the diagrams to find the problems. 

Best of all, there is a free version you can check out and decide if you want to get the reasonably priced pro-version.

There is too much in this tool for me to list all the great points about it.  I realize this sounds like a sales pitch, but after you check it out you will be impressed too.

Friday, February 8, 2013

SMS Tips and Tricks SQL

I don't often link to other posts - Yeah I know, a fatal bloggers mistake.  However, here is a a really nice set of tips:

Wednesday, January 23, 2013

Single Quotes in String Values

The following code includes an example of building a string value which has a single quote character in the text and also searching a field for a single quote character.

declare @test varchar(1000)
declare @i int

IF OBJECT_ID('tempdb..#testtable') IS NOT NULL  
    DROP TABLE #testtable

create table #testtable
(val varchar(200)

-- Intentionally misspelled
set @i = 1
set @test =  'Had this been a real emergency, you''re keester would already by fried'

while @i < 20
  insert #testtable
  select cast(@i as varchar(1000))
  set @i = @i + 1

insert #testtable
select @test

-- find the one record 
select val
from #testtable
where val like '%''%'

Tuesday, December 4, 2012

Find instances of a string in all your stored procedures:

This is a great little trick I came across while we were moving from one server to another and had to change the prefix on all references to that SQL machine - but it work in any instance where you need to search through all your stored code:

USE <databasename>;
SELECT routine_name
FROM information_schema.routines
WHERE routine_definition LIKE '%<my search string>%'
  AND routine_type = 'PROCEDURE'
ORDER BY routine_name;

Thursday, November 1, 2012

SSRS - List User Parameter Selection

How do I list the parameter the user selected in the parameter drop down?

Place a label on the header of your report and create an expression for the value:

 = "Days Selected " + Parameters!parm_Days_Past_Due.Label

What if multi-select is enabled for the parameter and the've chosen more than one value?
 ="Location Type= " + Join(Parameters!Location_Type.Label,",")

In this case, the join function treats the drop down label as an array object and concantonates all the values separated by a comma.  You can also use other characters to separate the list, but a comma is the most easily understood.