Visibility based on Parameter selection in SSRS

Add a function called "IsColumnSelected" that accepts the multi-value parameter and a column name and returns a boolean value:
''' <summary>
''' Return whether the passed column name has been selected
''' in the multi-value parameter, whether it should be visible
''' or not.
''' </summary>

Public Shared Function IsColumnSelected(ByRef objMultiValueParam() As Object, ByVal strColumnName As String) As Boolean
    ' return whether the passed column name is in the multi-value
    ' parameter array
    Return 0 < InStr(vbNullChar & Join(objMultiValueParam, vbNullChar) & _
        vbNullChar, vbNullChar & strColumnName & vbNullChar)
End Function

using an expression that needs to be put in each column's "Visibility" property which calls "IsColumnSelected". Put the expression below in the "Visibility" property of the first column:

=Not Code.IsColumnSelected(Parameters!ColumnsToDisplay.Value, "Column1")

Alternate Row Coloring

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

OR

= iif(RunningValue(Fields![rowgroupfield].Value.ToString,CountDistinct,Nothing) Mod 2,"Gainsboro", "White")

OR

=IIf( RunningValue (Fields!FieldName, CountDistinct, Nothing) MOD 2, Color1, Color2)

OR

Private bOddRow As Boolean'*************************************************************************
' -- Display green-bar type color banding in detail rows
' -- Call from BackGroundColor property of all detail row textboxes
' -- Set Toggle True for first item, False for others.
'*************************************************************************
Function AlternateColor(ByVal OddColor As String, _
         ByVal EvenColor As String, ByVal Toggle As Boolean) As String
    If Toggle Then bOddRow = Not bOddRow
    If bOddRow Then
        Return OddColor
    Else
        Return EvenColor
    End If
End Function

=Code.AlternateColor("AliceBlue", "White", True)

Pagination in SQL

-- SQL Server stored procedure - SQL pagination - SQL row_number function

CREATE PROCEDURE uspContactInfoByPage

                @PageSize   INT,

                @PageNumber INT

AS

  BEGIN

    WITH cteContact

         AS (SELECT ContactID,

                    FirstName,

                    MiddleName,

                    LastName,

                    EmailAddress,

                    Phone,

                    ROW_NUMBER()

                      OVER(ORDER BY LastName, FirstName, ContactID) AS SEQUENCE

             FROM   Person.Contact)

    SELECT Name = replace(FirstName + ' ' + isnull(MiddleName,'') + ' ' + LastName,

                          ' ',' '),

           [Email Address] = EmailAddress,

           Telephone = Phone

    FROM   cteContact

    WHERE  SEQUENCE BETWEEN @PageSize * @PageNumber + 1

    AND @PageSize * (@PageNumber + 1)

  END

GO

EXEC dbo.uspContactInfoByPage

  50 ,

  100

GO

Ranking Functions

-- Microsoft T-SQL ranking functions comparison

------------

-- MSSQL row_number (row number sequence) - NTILE

-- MSSQL rank and dense_rank functions - over order by clause

-- Data is rounded down to the thousands for ranking

USE AdventureWorks;

SELECT  

         c.AccountNumber                  AS CustAccount,

         FLOOR(h.SubTotal /1000)          AS [SubTotal (Thousands $)],

         ROW_NUMBER()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS RowNumber,

         RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS Rank,

         DENSE_RANK()

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS DenseRank,

         NTILE(5)

           OVER(ORDER BY FLOOR(h.SubTotal /1000) DESC) AS NTile

FROM     Sales.Customer c

         INNER JOIN Sales.SalesOrderHeader h

           ON c.CustomerID = h.CustomerID

         INNER JOIN Sales.SalesTerritory t

           ON h.TerritoryID = t.TerritoryID

WHERE    t.Name = 'Germany'

  AND    YEAR(OrderDate) = 2004

  AND    SubTotal >= 4000.0

ORDER BY RowNumber;

GO

/* Results

CustAccount	SubTotal (Thousands $)	RowNumber	Rank	DenseRank	NTile
AW00000230	100	1	1	1	1
AW00000230	88	2	2	2	1
AW00000302	77	3	3	3	1
AW00000320	68	4	4	4	1
AW00000536	68	5	4	4	1
AW00000536	64	6	6	5	1
AW00000266	58	7	7	6	1
AW00000302	44	8	8	7	2
AW00000687	43	9	9	8	2
AW00000482	36	10	10	9	2
AW00000176	36	11	10	9	2

Import & export images in SQL

USE AdventureWorks;

-- Create image warehouse for importing image into sql database

CREATE TABLE dbo.ImageWarehouse (

  ImageWarehouseID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY,

  ImageName        VARCHAR(100),

  Photo            VARBINARY(MAX))

GO

-- SQL Server import image - sql storing images database sql server

INSERT INTO dbo.ImageWarehouse

           ([ImageName])

VALUES     ('5THAVE.JPG')

UPDATE dbo.ImageWarehouse

SET    Photo = (SELECT *

                FROM   OPENROWSET(BULK 'e:\image\photo\5THAVE.JPG',

                       SINGLE_BLOB) AS x)

WHERE  [ImageName] = '5THAVE.JPG'

GO

-- Check table population

SELECT *

FROM   dbo.ImageWarehouse

GO

-- SQL Server export image

DECLARE  @SQLcommand NVARCHAR(4000)

-- Keep the command on ONE LINE - SINGLE LINE!!! - broken here for presentation

SET @SQLcommand = 'bcp "SELECT Photo FROM AdventureWorks.dbo.ImageWarehouse"

                   queryout "e:\image\photo\exp5THAVE.jpg" -T -n -SPROD\SQL2005'

PRINT @SQLcommand -- debugging

EXEC xp_cmdshell   @SQLcommand

GO

------------

-- T-SQL Export all images in table to file system folder

-- Source table: Production.ProductPhoto  - Destination: K:\data\images\productphoto\

------------

USE AdventureWorks2008;

GO

DECLARE  @Command       VARCHAR(4096),   -- dynamic command

         @PhotoID       INT,

         @ImageFileName VARCHAR(128)

DECLARE crsImage CURSOR  FOR             -- Cursor for each image in table

SELECT ProductPhotoID,

       LargePhotoFileName

FROM   Production.ProductPhoto

WHERE  LargePhotoFileName != 'no_image_available_large.gif'

OPEN crsImage

FETCH NEXT FROM crsImage

INTO @PhotoID,

     @ImageFileName

WHILE (@@FETCH_STATUS = 0) -- Cursor loop 

  BEGIN

-- Keep the bcp command on ONE LINE - SINGLE LINE!!! - broken up for presentation

    SET @Command = 'bcp "SELECT LargePhoto FROM

    AdventureWorks2008.Production.ProductPhoto WHERE ProductPhotoID = ' +

    convert(VARCHAR,@PhotoID) + '" queryout "K:\data\images\productphoto\' +

    @ImageFileName + '" -T -n -SYOURSERVER'

    

    PRINT @Command -- debugging 

/* bcp "SELECT LargePhoto FROM AdventureWorks2008.Production.ProductPhoto

WHERE ProductPhotoID = 69" queryout

"K:\data\images\productphoto\racer02_black_f_large.gif" -T -n -SYOURSERVER

*/

    

    EXEC xp_cmdshell @Command     -- Carry out image export to file from db table

    

    FETCH NEXT FROM crsImage

    INTO @PhotoID,

         @ImageFileName

  END  -- cursor loop

CLOSE crsImage

DEALLOCATE crsImage

/*output

NULL

Starting copy...

NULL

1 rows copied.

Network packet size (bytes): 4096

Clock Time (ms.) Total     : 16     Average : (62.50 rows per sec.)

NULL

.....

*/