Archive

Archive for March, 2010

SQL Server Date Format – Convert to varchar

March 31st, 2010 No comments

Below is a nice little script to output the various formats displayed by sql server when using convert(varchar, getdate(), n).

It just outputs the current date in the various formats so you can quickly find the one your after

Declare @date datetime
Declare @i int
set @date = getdate()
set @i = 1

while(@i <= 31)
begin
  if(@i not in (15, 16, 17, 18, 19, 22, 23, 24,  25, 28, 29))
  begin	
  if @i < 22
    print  convert(varchar, @i) + ': ' + convert(varchar, @date, @i)		
    print  convert(varchar, @i+100) + ': ' + convert(varchar, @date, @i+100)	
  end
  set @i = @i + 1
end 

Hope it helps

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)

Categories: SQL Server Tags:

SQL Server Reporting Services (SSRS) – IIF statement divide by zero error

March 12th, 2010 8 comments

If you use the IIF statement to solve your divide by zero errors, you will most likely find that you still get divide by zero errors.

The following example is trying to divide last weeks total hours worked

The following errors:

=IIF(Sum(Fields!LWWorkingDays.Value) = 0, 
  0,
  Sum(Fields!LWTotal.Value)/Sum(Fields!LWWorkingDays.Value) 
 )

The Problem
Even though the number of days = 0 and it should evaluate to the true condition of 0, SSRS still evaluates the false result, and the false result still throws a divide by zero error because it is evaluating:
Sum(Fields!LWTotal.Value) / 0

The solution:
We need to try to make the false result not error when working days = 0.
The false result

Sum(Fields!LWTotal.Value) / Sum(Fields!LWWorkingDays.Value)

Needs to change to the following:

Sum(Fields!LWTotal.Value) / 
IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value))

Why does this work?
When working days = 0 the result is going to be 0. So we don’t really care what the false result is going to be we just don’t want it to error. So the new IIF statement on the denominator returns 1 when the working days are zero.

This evaluates to:

Sum(Fields!LWTotal.Value) / 1 

and this does not error.

Final Code Example

=IIF(Sum(Fields!LWWorkingDays.Value) = 0, 
    0,
    Sum(Fields!LWTotal.Value) / 
    IIF(Sum(Fields!LWWorkingDays.Value) = 0, 1, Sum(Fields!LWWorkingDays.Value)) / 60
 )

In short by converting the zero to a 1, it stops the false result from erroring and allows the calculation to work correctly when it is evaluated.

That is a work around for a problem that should never of existed, good one Microsoft..

VN:F [1.9.22_1171]
Rating: 10.0/10 (3 votes cast)

Categories: Reporting Services, SQL Server Tags:

SQL Server Reporting Services – Access textbox value in expression

March 3rd, 2010 No comments

If you need to progamatically access a control on your report then use the following expression

ReportItems!textbox1.Value
VN:F [1.9.22_1171]
Rating: 10.0/10 (1 vote cast)

Categories: Reporting Services, SQL Server Tags: