Switch/Select Case Statement in Reporting Services

This is how you do a  Switch or Select Case statement in Reporting Services.  This would go in the Expression field of a field.    Note that the 'Else' condition is implemented by using the 'True' item as the last line in the statement.

 = switch (  
      Fields!Problems.Value = 1, "Units cannot be converted.",  
      Fields!Problems.Value = 2, "Price is zero.",  
      Fields!Problems.Value = 4, "Quantity is zero.",  
      True, Fields!Problems.Value  
 )  

Invalid Target Namespace When Deploying a Report to SQLServer 2008

If you get an error like the following when deploying a report to SQLServer ReportingServices 2008, it is usually because the report was created in a design environment setup for SQL Server 2008 R2, but your server is SQL Server 2008 R1 (I know, that’s not what it’s called, but you know what I mean).

The report definition is not valid. Details: The report definition has an invalid target namespace 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' which cannot be upgraded. (rsInvalidReportDefinition)

To fix this error:

• Edit the report RDL (Code View as it is called in BIDS).
• Replace the Report header with the following:

  <Report xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner" 
  xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition">
  
  

  
  
• Remove the <ReportSections> and <ReportSection> tags from the report. Note, remove the tags only.  Leave all content inside the tag.  Don’t forget to remove the closing tags for each of these.
  
• Save the report.  You should now be able to upload it.

Alternating Background Colors in a Tablix Group

When alternating background colors in a group in a tablix, you cannot use the traditional formula for alternating rows (which would be something like: =iif(RowNumber(Nothing) Mod 2, "White", "#E5E5E5")  )

Instead you must use the following:

1. To go the Code for the report (found in the Report Properties dialog)
2. Add an even number function, and an internal variable to track the current row:

    Public _evenRow As Boolean  
    Public Function EvenRow() As Boolean  
      _evenRow = Not _evenRow  
      return _evenRow  
    End Function  
   

   
   
3. Go to the “Group Properties” for the Row Group that you want to alternate, go to variables, and add a variable called EvenRow. The expression for this variable should be =Code.EvenRow().  This will be executed each time the group is created.
   
4. Finally, in the background property for the group row, you can enter a modified version of the original expression: =IIF(Variables!EvenRow.Value=true,”Red”,”Green”)

Divide by Zero Error in ReportingServices

Occasionally you need to do division in a ReportingServices report, for example where you have to calculate percentages.  But when your divisor is zero you get an ugly ‘#Error’ in the field where the division is done. 

Checking for a zero in the divisor seems like an obvious solution, but unfortunately the ‘IIF’ statement that you would use to check for this evaluates the True and False parts of the answer regardless of the result of the result it is evaluating.   So if Y = 0, and your field has the following: IIF(Y=0, 0, X/Y), you will still get the #Error message. 

The fix for this is to put the following code in the report:

  Public Function Divide(ByVal first As Double, ByVal second As Double) As Double  
     If second = 0 Then  
       Return 0  
     Else  
       Return first / second  
     End If  
   End Function  

Then in the field replace the IIF statement with the following Code.Divide (X/Y).

That should take care of it.

Column Headers in Reporting Services 2008

If the column headers in Reporting Services 2008 won't repeat on a new page, do the following.

• Click on the triangle button at the bottom of the screen, and click on "Advanced Mode".
• In the "Row Groups" section of the window, click on the "Static" element at the top of the list.
• Set the following properties:
• FixedData = True
• KeepWithGroup=After
• RepeatOnNewPage=True

Reporting Services Templates

When you want to setup a report as a template for creating other reports, put it in the following directory:

c:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\ProjectItems\ReportProject

Technorati Tags: ReportingServices