Useful tips on Reporting Services

Introduction

When working with reporting services, I found out a few small things that I thought I can share. It can be taken as tips on using Reporting services

Parameter Representation

When we write a stored procedure, there will definitely be a need to define parameters. If we are using SQL Server that is not a problem. We could do the same using the symbol ‘@’ and parameter name. If we are using ‘Oracle database’ as the backend, then we’ll have to use the symbol ‘:’ and parameter name.

Preserving Space in the Reports

In our reports, we may some times want the spaces to be preserved, meaning say we put some space in a text box and some text following that. When we render the report this will be ignored by the Report rendering engine. To make sure that the Report rendering engine does not ignore this, we need to insert alt+0160, (i.e. hold the key and enter 0160) instead of pressing the space bar button.

Adding Null Values for Multi-Valued Parameters

When we use multi-valued parameters, we would want to have null values as an option as we may want it to be selected by default. The problem is, there is no null value for multi-valued parameters. One possible solution could be to create a view that has a null value. To do this, we can make use of the UNION ALL command present in SQL.

Example:

CREATE VIEW viewDomainWithNull
AS
  SELECT -1 AS [Domain Id],'NULL' AS [Domain Name]
	UNION ALL
		SELECT [Domain Id],[Domain Name] FROM Domain
For the default value in the parameter dialog box, we can enter the value ‘-1’.
This would make null be selected by default.

Use of Alternating Colors for the Rows in the Report

To give alternating colors to the rows, we can use the “IIF“statement. First select the data row that must have alternating color and select expression for the background color option.

The general syntax is as follows

=IIF( RowNumber("datasource name") Mod 2,"color1","color2")

RowNumber returns the number of rows.

Color1 if the mod expression returns true
Color2 if the mod expression returns false

This will give two colors alternating for the data rows.
The same expression can be extended to allow more colors to be used.

=IIF( RowNumber("datasource name") Mod 3 = 0,"color1",
    IIF(RowNumber("datasource name") Mod 3 = 1,"color2","color3"))

This will give three colors alternating for the data rows.

Adding External Images as Background Image

When setting the background image for a “textbox” we can use embedded images, but what if we don’t want to use that. Say, we have an image in the report project folder that can be used. Under the Background image option, for the value field enter the name of the file.

If we have a file named “test.gif” then enter that into the value field.