The progression of inefficiency

I have thought about the irony of this from time to time, so I though I would write it down:

1970’s: The promise: Workers save 20 min every morning by driving their own car to work rather than waiting on a bus or walking.
The reality: Workers waste an hour in traffic each morning.

1980’s: The promise:  Computers will save time by letting us type it once and print it multiple times

The reality:  Workers waste an hour a day dealing with paper jams in dot matrix printers

1990’s:  The promise:  Microsoft Office will save us time by eliminating spelling errors and providing all the formatting options available from outside print houses.

The reality:  Workers waste an hour per document trying to fix small formatting errors.  An entire generation is born that will never know how to spell receive or which homonym to use.

2000’s:  The promise:  More features in the OS and larger hard drives will give us a paperless office, saving time looking for lost papers.

The reality:   Workers waste 20 minutes each morning waiting for their bloated OS to boot up and hours each day looking for lost documents in their huge hard drives.

2010’s:  The promise:  The cloud will save us time by allowing us to save our data and use our programs from anywhere.

The reality:  Slow transfer rates make fedex faster than large file transfers.  Losing your internet connection means your entire system is down.

If you think of any others, please leave a comment!

Link to post on SSRS report parameters not working

http://chrisblog.betterithosting.com/?p=38

SSRS divide by zero annoyance

I have this formula in a textbox on my report:

= IIF(Fields!CallInvoiceAmount.Value=0,0.00,
  Fields!CallProfit.Value/Fields!CallInvoiceAmount.Value
 )

Basically, I am just trying to calculate a percentage and I need to handle the cases where the denominator is zero to avoid a divide by zero error.

Now if all of the fields above are int fields, the formula works.  However, they are numeric fields (money values).  SSRS throws a #Error every time the CallInvoiceAmount value is 0.

After much aggravation trying to get this to work, I finally found some info on some message boards that spelled out the problem.  SSRS apparently evaluates the true and false conditions when there are numeric values involved, BEFORE it evaluates the IIF condition.

Now isn't that just illogical enough to raise Spock's eyebrow!

The prevailing suggestion was to embed code in the report a write a custom function to handle it and then call that.  However, there are issues invoved with that when working with aggregates, and I also need the following to work:

= IIF(sum(Fields!CallInvoiceAmount.Value)=0,0.00,
  sum(Fields!CallProfit.Value)/sum(Fields!CallInvoiceAmount.Value)
 )

The answer turned out to be simple, if still a work around.  I added a very tiny amount to the denominator.  This also allows me to remove the iif statement entirely:


= sum(Fields!CallProfit.Value)/(sum(Fields!CallInvoiceAmount.Value)+0.00001)

Of course, if the denominator or really zero you will get a huge percentage, but that is fitting since the real answer is undefined.
 

Did this help you?  If so, please leave a comment!

Converting a string to Numeric in SQL

I have run into this again and had to spend time compiling the answer, so I am posting it for future use.

I needed to convert a string to numeric in SQL.  Seems pretty straightforward, right?

select convert(numeric(19,5),'0.0')

So what happens with my string looks like this?

select convert(numeric(19,5),'abc0.0')

Error converting data type varchar to numeric.
Ok, so I need to make sure it is a number first.

select case isnumeric('abc0.0') when 1 then convert(numeric(19,5),'abc0.0') else 0 end

All done, right?  Not so fast.  All of the following return 1 (true)
select isnumeric('$0.0')

select isnumeric('123,000.0')

But the following result in an error:
select convert(numeric(19,5),'$0.0')

select convert(numeric(19,5),'123,000.0')

So we need to make some basic decisions about what we consider to be valid in our numeric value and replace out those offending characters before evaluating it.

select case isnumeric(replace(replace(replace(replace('123,000.0',',',''),'$',''),'+',''),'-','')) when 1 then convert(numeric(19,5),replace(replace(replace(replace('123,000.0',',',''),'$',''),'+',''),'-','')) else 0 end



Did this help you?  If so, please leave a comment!