Add alternating row color to SQL Server Reporting services report

Go to the table row’s BackgroundColor property and choose “Expression…"

Use this expression:

= IIf(RowNumber(Nothing) Mod 2 = 0, "Silver", "Transparent")

Display Reporting Services drill through reports in new browser

Solution

Use a little javascript with a customized URL in the “Jump to URL" option of the Navigation tab.

To get started, let’s pop up a parameterized report.

Follow these instructions:

1.                  Instead of using the “Jump to Report" option on the Navigation tab, use the “Jump to URL" option.

2.                  Open the expression screen (Fx button).

3.                  Enter the following:

=="javascript:void(window.open(‘http://ServerName/ReportServer/Pages/ReportViewer.aspx?%2fReportFolder%2fReports%2fReportName&rs:Command=Render:Parameters=true&Param1=” & Parameters!Param1.Value & "&Param2=" & Parameters!Param2.Value &"&Param3=" & Parameters!Param3.Value & "&Param4=" & join(Parameters!Param4.Value,"&Param4=") & ”’))"

Click OK twice, then save and deploy the report.

Note: The link will not work from the designer environment. You must deploy the report (hopefully to a test area) to try the link.

Getting Multi-Value parameters to work with Stored Procedures

1. The stored procedure for the report should declare any multi-value parameters as type    nvarchar(max).

2. In the Report Designer, your parameter should have the ‘Allow multiple values’ option ticked (obviously). Also, it helps to set the Data type to ‘Text’ even if it’s actually an integer or something, because we’re going to have to treat it as text later on in the Stored Procedure.

3. In the DataSet that calls the stored procedure, go to the Parameters tab:

4. For each parameter that needs to be a multi-value one, press the Fx button and change the default:
=Parameters!TerritoryMulti.Value

to
=Join(Parameters!TerritoryMulti.Value, ",")

This will just string the multi-values together into a comma separated string.

5. In the database, you need a function that can split comma-separated values back out into a table format. There are lots of variations of this sort of function around, here’s the one I use:

CREATE FUNCTION [dbo].[ufnSplit]

   (@RepParam nvarchar(max), @Delim char(1)= ',')

RETURNS @Values TABLE (Item nvarchar(100))AS

  BEGIN

  DECLARE @chrind INT

  DECLARE @Piece nvarchar(100)

  SELECT @chrind = 1

  WHILE @chrind > 0

    BEGIN

      SELECT @chrind = CHARINDEX(@Delim,@RepParam)

      IF @chrind  > 0

        SELECT @Piece = LEFT(@RepParam,@chrind - 1)

      ELSE

        SELECT @Piece = @RepParam

      INSERT  @Values(Item) VALUES(@Piece)

      SELECT @RepParam = RIGHT(@RepParam,LEN(@RepParam) - @chrind)

      IF LEN(@RepParam) = 0 BREAK

    END

  RETURN

  END

6. The query inside the stored procedure that uses the multi-value parameter should use it like this:

where sometable.TerritoryID in (select Item from dbo.ufnSplit(@TerritoryMulti,’,’))

… that is, it uses ufnSplit to split the passed values out into a table, and then selects from that table and uses ‘in’ to check for membership.

SSRS reports are blank on Chrome

There is a frustrating issue with SQL 2008 R2 Reporting Services when rendering on Chrome (and Safari) - in that they simply display a completely blank screen.

After eventually convincing the users in question that it wasn’t SQL’s fault, or a problem with the report definition, since they displayed fine on internet explorer, 

I set about tracking down a fix.

Basically though, you need to update the ReportingServices.js file on the report server, as Chrome & Safari render a certain type of element in a slightly different way to IE.

Connect to your Reporting Services server, and find the relevant ReportingServices.js file - this will probably be somewhere like

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\js\ReportingServices.js

C:\Program Files(x86)\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\js\ReportingServices.js

First up, take a copy of this file - let’s not be silly here and assume this is a 100% foolproof solution!

Next, edit this file in notepad. Do a quick search for PageLoad, to ensure no-one else has already hacked applied this fix, then jump to the bottom of the existing text.

Simply append the following code:

function pageLoad() {
var element = document.getElementById("ctl31_ctl10");
if (element)
{
element.style.overflow = “visible";
} }

Save the file, and restart Reporting Services - you should now find that Chrome displays your reports again.