Add Subquery As A Column

The SQL below adds aggregated columns to a query by using sub queries. The key to using this type of query is to join the inner and outer tables on the correct field.

Use the Instant SQL Formatter to Format the SQL below.  Just copy and paste into the Formatter at: http://www.dpriver.com/pp/sqlformat.htm

SELECT DISTINCT a.eeo_cls,

                a.classification,

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[hispanic or latino] = 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[hispanic or latino] = 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[white] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[black or african american] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-W',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[black or african american] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-B',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[asian] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-A',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[native hawaiian or other pacific islander] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[american indian or alaska native] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.prem_gender = 'M'

                        AND b.eeo_cls = a.eeo_cls) AS 'M-I',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[white] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[black or african american] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-W',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[black or african american] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.asian <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-B',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[asian] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-A',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[native hawaiian or other pacific islander] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[american indian or alaska native] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-H',

                (SELECT Count(b.prem_emp)

                 FROM   #ee05temp b

                 WHERE  b.[american indian or alaska native] = 1

                        AND b.[hispanic or latino] <> 1

                        AND b.[white] <> 1

                        AND b.[black or african american] <> 1

                        AND b.[asian] <> 1

                        AND b.[native hawaiian or other pacific islander] <> 1

                        AND b.prem_gender = 'F'

                        AND b.eeo_cls = a.eeo_cls) AS 'F-I',

                (SELECT Count(C.prem_emp)

                 FROM   #ee05_multiple C

                 WHERE  C.prem_gender = 'M'

                        AND C.eeo_cls = a.eeo_cls) AS 'M-O',

                (SELECT Count(C.prem_emp)

                 FROM   #ee05_multiple C

                 WHERE  C.prem_gender = 'F'

                        AND C.eeo_cls = a.eeo_cls) AS 'F-O'

FROM   #ee05temp a

       INNER JOIN #ee05temp b

               ON a.prem_emp = b.prem_emp

       INNER JOIN #ee05_multiple

               ON a.prem_emp = b.prem_emp

GROUP  BY a.classification,

          a.eeo_cls

ORDER  BY a.eeo_cls 

SSRS - Top Directory

Below is a script that can be used to strip out the '/' from the SSRS Path so you can display the Top Folder/Directory.

Replace(Left(Catalog.Path,CHARINDEX('/',Catalog.Path,2)-1),'/','') As Top_Folder

The following query will return information regarding subscriptions located on your SQL Server.

SELECT catalog.NAME,
       catalog.type,
       catalog.description,
       catalog.creationdate,
       catalog.modifieddate,
       subscriptions.laststatus,
       subscriptions.lastruntime,
       users.username,
       catalog.path,
       Replace(LEFT(catalog.path, Charindex('/', catalog.path, 2) - 1), '/', '')
       AS
       Top_Directory
FROM   catalog
       INNER JOIN subscriptions
               ON catalog.itemid = subscriptions.report_oid
       LEFT OUTER JOIN users
                    ON catalog.createdbyid = users.userid
                       AND catalog.modifiedbyid = users.userid
                       AND subscriptions.modifiedbyid = users.userid
                       AND subscriptions.ownerid = users.userid
WHERE  subscriptions.lastruntime >= '8/1/2015'; 

Download All RDL Files

The following is a great PowerShell Script for downloading all RDL files from the ReportServer Database.  Without a script to download the RDL files you would have to go into each report from the Web Interface and select Download.  This script could save days worth of work.


<# .SYNOPSIS
      Export of all SSRS reports datasources and images
   .DESCRIPTION
      This PowerShell script exports all (or filtered) reports, data sources and images directly from the ReportServer database
      to a specified folder. For the file name the complete report path is used; for file name invalid characters are replaced with a -.
      Reports are exported with .rdl as extension, data sources with .rds and resources without any additional extension.
      Please change the "Configuration data" below to your enviroment.
      Works with SQL Server 2005 and higher versions in all editions.
      Requires SELECT permission on the ReportServer database.
   .NOTES
      Author  : Olaf Helper
      Requires: PowerShell Version 1.0, Ado.Net assembly
   .LINK
      GetSqlBinary: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqldatareader.getsqlbinary.aspx
#>


# Configuration data
[string] $server   = "ServerName";        # SQL Server Instance.
[string] $database = "ReportServer";        # ReportServer Database.
[string] $folder   = "\\Some Location";          # Path to export the reports to.

# Select-Statement for file name & blob data with filter.
$sql = "SELECT CT.[Path]
              ,CT.[Type]
              ,CONVERT(varbinary(max), CT.[Content]) AS BinaryContent
        FROM dbo.[Catalog] AS CT
        WHERE CT.[Type] IN (2, 3, 5)";

# Open ADO.NET Connection with Windows authentification.
$con = New-Object Data.SqlClient.SqlConnection;
$con.ConnectionString = "Data Source=$server;Initial Catalog=$database;Integrated Security=True;";
$con.Open();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Started ...");

# New command and reader.
$cmd = New-Object Data.SqlClient.SqlCommand $sql, $con;
$rd = $cmd.ExecuteReader();

$invalids = [System.IO.Path]::GetInvalidFileNameChars();
# Looping through all selected datasets.
While ($rd.Read())
{
    Try
    {
        # Get the name and make it valid.
        $name = $rd.GetString(0);
        foreach ($invalid in $invalids)
           {    $name = $name.Replace($invalid, "-");    }

        If ($rd.GetInt32(1) -eq 2)
            {    $name = $name + ".rdl";    }
        ElseIf ($rd.GetInt32(1) -eq 5)
            {    $name = $name + ".rds";    }

        Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Exporting {0}" -f $name);

        $name = [System.IO.Path]::Combine($folder, $name);

        # New BinaryWriter; existing file will be overwritten.
        $fs = New-Object System.IO.FileStream ($name), Create, Write;
        $bw = New-Object System.IO.BinaryWriter($fs);

        # Read of complete Blob with GetSqlBinary
        $bt = $rd.GetSqlBinary(2).Value;
        $bw.Write($bt, 0, $bt.Length);
        $bw.Flush();
        $bw.Close();
        $fs.Close();
    }
    Catch
    {
        Write-Output ($_.Exception.Message)
    }
    Finally
    {
        $fs.Dispose();
    }
}

# Closing & Disposing all objects
$rd.Close();
$cmd.Dispose();
$con.Close();
$con.Dispose();

Write-Output ((Get-Date -format yyyy-MM-dd-HH:mm:ss) + ": Finished");

Reference:
https://gallery.technet.microsoft.com/scriptcenter/Export-of-all-SSRS-reports-57910227

SSRS-Users, Reports and Permissions

Working with SSRS security can be frustrating at times because of the limitations of the web interface, especially if your company has thousands of reports and users.  If you work with SSRS it is important to get familiar with the ReportServer database in SSMS.  All of the data, including the XML that produces reports on the report Server is located in ReportServer tables.

The following is a simple query to return UserName, Roles, Role Descriptions, Report Path and Report Name.  This script will come in handy if you are ever asked to provide a list of users, their roles and what reports they can access.

Select C.UserName, D.RoleName, D.Description, E.Path, E.Name 

from dbo.PolicyUserRole A

   inner join dbo.Policies B on A.PolicyID = B.PolicyID

   inner join dbo.Users C on A.UserID = C.UserID

   inner join dbo.Roles D on A.RoleID = D.RoleID

   inner join dbo.Catalog E on A.PolicyID = E.PolicyID

Where C.UserName = 'DOMAIN\USERNAME'

order by C.UserName   

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

FnSplit Function

The FnSplit Function comes in handy when using stored procedures within SSRS reports.  The function splits out the values for parameters and separates the values with commas.  This is needed if you want to select multiple values within a report.  There is an option for multiple values in the Report Builder and if your SQL is In-Line within the report you can simply use the following:

Select Field
From Table
Where School in (@SCHOOL)

However, this does not work properly in Stored Procedures.

There may be other options, but the one I use most often is the FnSplit Function.

Select Field
From Table
Where School IN(SELECT Value FROM dbo.FnSplit(@SCHOOL,','))

Depending on the report criteria you may need to add more code.  For example, if you want to select all values and have the ability to also select multiple values you will need to add an If statement to the stored procedure.

The first thing you have to do is add an option to your parameter.  If your parameter is a query you can do something like the following:

Select SchoolID, SchoolName
From Schools
Union
Select '000', '000-All Schools'

Stored Procedure:

If @School = '000'
Begin

Select Field
From Table
End

Else

Select Field
From Table
Where School IN(SELECT Value FROM dbo.FnSplit(@SCHOOL,','))
End