Execute Multiple Commands with 1 line in Execute Process Task (Arguments)

Separate commands by "&" or "&&" to execute multiple commands from Arguments in Execute Process Task.

&& will execute command 2 when command 1 is complete providing it didn't fail, & will execute regardless.

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.

Convert comma separated string to table and vice versa

Convert table to comma separated string:

Create a table and populate it with some data :

--Create a table and populate it with some data

CREATE TABLE MyTable (MyCol VARCHAR(50))

INSERT INTO MyTable (MyCol) VALUES ('Yellow')

INSERT INTO MyTable (MyCol) VALUES ('Blue')

INSERT INTO MyTable (MyCol) VALUES ('Green')

INSERT INTO MyTable (MyCol) VALUES ('Red')

--Retrieve all records from the column in the table as a comma separated string into variable

DECLARE @CommaString VARCHAR(300)

SET @CommaString = ''

SELECT @CommaString = @CommaString + MyCol / cast (MyCol as varchar(max))  + ','

FROM MyTable

--trim trailing comma of string and return variable

SELECT LEFT(@CommaString, LEN(@CommaString) -1)

Convert comma separated string to table:

Unfortunately converting a comma separated string into a table isn’t as easy. Well, I mean it is fairly *easy* to do it, but there isn’t a nice and clean way of doing it. .net developers are blessed with the string method .Split() and yes creating a CLR to chop up these strings is definitely one good option to achieve this if that is what you decide to do. Other options to chop up a string to a table include:

- Use a loop to read/parse the string from left to right whilst extracting the values to a temp table

- Cast the string to xml by strategically replacing the comma’s to xml nodes and then using the nodes method to shred the xml to a table

- Use a CTE to break the string down into a temp table

- Use a tally table as shown here in an SSC article

Although I’ve always thought that doing the xml technique is probably consistently the quickest, I was quite intrigued by the tally table approach and I wondered if I could use the same method but eliminate the tally table thus saving those valuable reads. After pondering on the problem for a short while I recalled the Itzik CTE tally table and proceeded to try and use that instead of an actual tally table.

So here is my first attempt at doing this:

--declare a variable and populate it with a comma separated string

DECLARE @SQLString VARCHAR(MAX)

SET @SQLString = '111,211,311,411,5,6,7,811,911,1199,2299,3399,4499,5599,699,799,899,999';

--append a comma to the string to get correct results with empty strings or strings with a single value (no commas)

SET @SQLString = @SQLString + ',';

--the main query

--CTE to generate us a lot of numbers (this will produce up to 65536)

WITH Nbrs_3(n) AS (SELECT 1 UNION SELECT 0),

               Nbrs_2(n) AS (SELECT 1 FROM Nbrs_3 n1 CROSS JOIN Nbrs_3 n2),

               Nbrs_1(n) AS (SELECT 1 FROM Nbrs_2 n1 CROSS JOIN Nbrs_2 n2),

               Nbrs_0(n) AS (SELECT 1 FROM Nbrs_1 n1 CROSS JOIN Nbrs_1 n2),

               Nbrs  (n) AS (SELECT 1 FROM Nbrs_0 n1 CROSS JOIN Nbrs_0 n2)

--chop up the string based on the position of the comma returned from the inner query

SELECT SUBSTRING(@SQLString, n+1, CHARINDEX(',', @SQLString, n+1) - n-1)

FROM

(

          --select a holding record to ensure we get the very first value in the string

               SELECT 0 AS 'n'

               UNION ALL

          --select the maximum amount of generated numbers that we will need.

          --this will be the len of the string -1. the last char of string is a comma

               SELECT TOP(LEN(@SQLString)-1) ROW_NUMBER() OVER (ORDER BY n) AS 'n'

    FROM Nbrs

) x

--only return the numbers that equate to the position of a comma in the original string

--returning 0 ensures that we get the first value in the string.

WHERE SUBSTRING(@SQLString, n, 1) = ',' OR n = 0

webpage error status code 500

Webpage error details

User Agent: Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 5.1; Trident/4.0; .NET CLR 2.0.50727; .NET CLR 3.0.4506.2152; .NET CLR 3.5.30729; .NET4.0C; .NET4.0E)

Timestamp: Wed, 1 Jun 2013 08:34:05 UTC

rverErrorException: An unknown error occurred while processing the request on the server. The status code returned from the server was: 500

Line: 5

Char: 62099

Code: 0

URI: http://m

Message: Sys.WebForms.PageRequestManagerSeysqlserver/Reports/ScriptResource.axd?d=XwwW1tMwtFzdBQ9-6KriOz3q0Wkgg-xPb7EWT8HUhJXnf8sz46FbnRIo5guVNx1JC-QFapCZ-oQvTRpjjwXFYypY46ebyJBSDV8_0QBsVijeeYDDkZolFtJT35QxeGTEsgsKCpzrB-ZJiu83PMYBwOjrroQ1&t=ffffffffb868b5f4

This problem is being caused by SQL server stopping a report being run because the request length exceeds a certain amount.

The solution to this is as follows:

Locate the web.config files for the ReportManager & ReportServer.

These should be found somewhere like this:

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager\Web.config

C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer\Web.config

Once located you need to edit the web.config files for both and add the following bit of code:

<appSettings>

<add key="aspnet:IgnoreFormActionAttribute" value="true" />

<add key="aspnet:MaxHttpCollectionKeys" value="100000" />

</appSettings>

These app settings should be added between between /system.web and runtime nodes, so it should look something like the following:

</system.web>
< appSettings>
< add key="aspnet:IgnoreFormActionAttribute" value="true" />
< add key="aspnet:MaxHttpCollectionKeys" value="100000" />
< /appSettings>
< runtime>

NOTE: The ReportManager may already have an app settings node so you will only need to paste the two add key lines.

The ReportServer will more than likely require all 4 lines (including the open and close appsettings nodes.