How to collapse / expand a specific row group in a tablix in Microsoft Reporting Services 2008 / Report Builder 3.0

Let’s say you want to show costs on a parent child hierarchy in a Microsoft SQL Server Reporting Service 2008 R2 report and you want only to expand the children of parent "John":

 

image

 

Follow the steps below, to create this report:

 

Table design

image

 

Table contents

image

 

Add new report

Start Microsoft SQL Server 2008 R2 Report Builder 3.0 > New Report > Blank Report

image

 

Add a new data source to the report

Add a data source [Right click Data Sources > Add Data Source…]

image

 

The [Add Data Source Wizard]:

image

 

Add a new dataset to the report

Add a dataset [Right click Datasets > Add Dataset…]

image

 

The [Add Dataset Wizard]:

image

 

Add a tablix to the report

Add a tablix [Click in the ribbon on Insert > Table > Table Wizard…]

image

 

The [Add Table Wizard]

image

Add Parent and Child to Row groups and Cost to Values

image

Choose [Stepped subtotals above]

image

 

image

 

Row visibility

Right click on the row header of the [Child] row > Row Visibility…

image

Show or hide based on an expression: = IIF(Fields!Parent.Value = "John", False, True)

image

 

Group visibility

Make sure this expression is forwarded to the group visibility > right click on the row header of the [Child] row > Row Group > Group Properties…

image

image

 

InitialToggleState

Select row header of the [Parent] row > Properties > InitialToggleState > <Expression…>

=IIf(Fields!Parent.Value = "John", True, False)

image

 

image

 

Run the report

image

Solving: Error : rsAccessDenied (The permissions granted to user ‘MyDomain\Administrator’ are insufficient for performing this operation.)

When deploying reports by using the rs.exe you can get the error: "Error : rsAccessDenied (The permissions granted to user ‘MyDomain\Administrator’ are insufficient for performing this operation.)".

 

Solution

 

image

 

  • Go to the home folder
  • Go to Properties
  • Security
  • Add you’re account to the roles [Browser, Content Manager, My Reports, Publisher, Report Builder]

If the security chain is broken in the subfolders you will have to repeat the steps for each subfolder

 

image

Install a development Microsoft SharePoint 2010 RTM Server with PowerPivot and Microsoft SQL Server R2 Reporting Services integration

These are the steps I followed to install a development Microsoft SharePoint 2010 server with PowerPivot and Reporting Services integration.

 

I installed Microsoft SharePoint Server 2010 by following the steps on:

http://powerpivot-info.com/post/66-step-by-step-guide-on-installing-powerpivot-for-sharepoint

 

Then I followed the steps on: http://technet.microsoft.com/en-us/library/bb326356(SQL.105).aspx and http://msdn.microsoft.com/en-us/library/aa905871(SQL.105).aspx to enable Microsoft SQL Server 2008 R2 Reporting Services integration for Microsoft SharePoint Server 2010.

After following these steps, I was able to publish PowerPivot excel reports and Microsoft SQL Server 2008 R2 Reporting Services reports to one top level site collection on Microsoft SharePoint Server 2010. See screen dumps:

 

Start Microsoft SQL Server 2008 R2 setup > Installation > New installation or add features to an existing installation

image

New installation or add shared features

image

SQL Server Feature Installation

image

Reporting Services

image

Instance Configuration

image

Server Configuration

image

 

 

 

Start Reporting Services Configuration Manager

image

Choose Report Server Instance

image

Create a new report server database

image

Report Server Database Configuration Wizard

image

Report Server Mode > SharePoint Integrated Mode

image

Additional actions in Reporting Services Configuration Manager

Web Service URL > Apply

image

Report Manager URL > Apply

image

 

The SharePoint 2010 pre-requisite installer now automatically guides you to download and install the Reporting Services 2008 R2 for SharePoint 2010 Add-in.  Btw, you can download the Reporting Services add-in manually as well. In most cases you can just activate the Report Server Feature in SharePoint Central Administration (http://technet.microsoft.com/en-us/library/bb677366(SQL.105).aspx). In some case you must first download and install the add-in manually, in my case I re-configured the Reporting Services 2008 R2 for SharePoint 2010 Add-in and then activated the Report Server feature on the Central Administration site.

Reconfigure the Microsoft SQL Server 2008 R2 Reporting Services Add-in > Start > Control Panel > Programs and Features > Right click SQL 2008 R2 Reporting Services SharePoint 2010 Add-in > Configure

image

Activate Report Server Integration Feature and Report Server Central Administration Feature > Central Administration > Site Actions > Site Settings > Site Collection features

image

Configure Reporting Services Integration > Central Administration > General Application Settings > Reporting Services Integration

image

Reporting Services > Reporting Services Integration

image

Reporting Services > Set server defaults

image

 

Create a Report Library > Site Actions > More Options > Report Library

image

Enable Report Server Content Types

http://technet.microsoft.com/en-us/library/bb326289(SQL.105).aspx

 

Create a Report > Report Library > Documents > New Report Builder Report (Click once deployment Report Builder 3.0 should start)

image

 

Run click once deployment Report Builder 3.0 (78.2 MB)

image

Create you’re first report

image

 

The Microsoft SQL Server 2008 R2 sample databases can be found at: http://sqlserversamples.codeplex.com/ (82.5 MB). At this moment the November CTP sample database are the last version. RTM sample database should be released soon.

 

Save the report to SharePoint 2010

image

Open report in Microsoft SharePoint 2010

image

Result full screen

image

Result as webpart

image

How to deploy a report (*.rdl) file to SQL Server Reporting Services using PowerShell

If you want to deploy a report (*.rdl) file to SQL Server Reporting Services in PowerShell, use the following script:

 

PowerShell script

"Set execution policy to [Unrestricted]"
Set-ExecutionPolicy Unrestricted

"Load assembly"
[System.Reflection.Assembly]::LoadFrom("C:\Temp\Ada.Cdf.dll")

"Create report"
$report = New-Object Ada.Cdf.Deployment.SSRS.Report
$report.SSRSWebServiceUrl = "http://localhost/ReportServer/ReportService2005.asmx"
$report.SSRSFolder = "ADA Sales Reports"
$report.FileSystemPath = "C:\Reports\AanvragenStats.rdl"
$report.Create()

 

C# code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Ada.Cdf.ReportService2005;
namespace Ada.Cdf.Deployment.SSRS
{
public class Report
{
public string FileSystemPath { get; set; }
public string SSRSWebServiceUrl { get; set; }
public string _ssrsFolder = string.Empty;
public string SSRSFolder 
{
get
{
// Use "/" as default value
string firstCharacter = "/";
if (string.IsNullOrEmpty(_ssrsFolder))
{
_ssrsFolder = firstCharacter;
}
// Report folder should start with one "/"
_ssrsFolder = firstCharacter + _ssrsFolder.TrimStart(firstCharacter.ToCharArray());
return _ssrsFolder;
}
set
{
_ssrsFolder = value;
}
}
/// <summary>
/// Create a report
/// </summary>
public void Create()
{
// Validate properties
if (string.IsNullOrEmpty(this.FileSystemPath)) { throw new ArgumentNullException("this.FileSystemPath"); }
if (!File.Exists(this.FileSystemPath)) { throw new ApplicationException(string.Format("The file [{0}] does not exist", this.FileSystemPath)); }
if (string.IsNullOrEmpty(this.SSRSWebServiceUrl)) { throw new ArgumentNullException("this.SSRSWebServiceUrl"); }
if (string.IsNullOrEmpty(this.SSRSFolder)) { throw new ArgumentNullException("this.SSRSFolder"); }
// Initialize webservice proxy
ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();
rs.Url = this.SSRSWebServiceUrl;
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Determine filename without extension (used as name in SSRS)
FileInfo fileInfo = new FileInfo(this.FileSystemPath);
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(fileInfo.FullName);
// Determine filecontents
byte[] fileContents = File.ReadAllBytes(fileInfo.FullName);
// Publish report
Warning[] warnings = rs.CreateReport(fileNameWithoutExtension, this.SSRSFolder, true, fileContents, null);
// Log warnings
if (warnings != null)
{
foreach (Warning warning in warnings)
{
Global.Logger.Warn(warning);
}
}
}
/// <summary>
/// Delete a report
/// </summary>
public void Delete()
{
// Initialize webservice proxy
ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();
rs.Url = this.SSRSWebServiceUrl;
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Determine filename without extension (used as name in SSRS)
FileInfo fileInfo = new FileInfo(this.FileSystemPath);
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(fileInfo.FullName);
// Determine full SSRS path
string ssrsPath = string.Format("{0}/{1}", this.SSRSFolder, fileNameWithoutExtension.TrimStart("/".ToCharArray()));
// Delete report
rs.DeleteItem(ssrsPath);
}
}
}

 

Result

image

How to deploy a datasource in SQL Server Reporting Services with PowerShell

If you want to use PowerShell to deploy a datasource in SQL Server Reporting Services use the following script:

 

PowerShell script

"Set execution policy to [Unrestricted]"
Set-ExecutionPolicy Unrestricted

"Load assembly"
[System.Reflection.Assembly]::LoadFrom("C:\Temp\Ada.Cdf.dll")

$datasource = New-Object Ada.Cdf.Deployment.SSRS.DataSource
$datasource.SSRSWebServiceUrl = "http://localhost/ReportServer/ReportService2005.asmx"
$datasource.ImpersonateUser = $false
$datasource.WindowsIntegratedSecurity = $true
$datasource.UserAccountName = "MyDomain\saAsaWeb"
$datasource.UserAccountPassword = "MyPassword"
$datasource.ConnectionString = "Data Source=.;Initial Catalog=AdaSales;Integrated Security=SSPI;"
$datasource.FileSystemPath = "C:\Temp\Reports\AdaSales.rds"
$datasource.SSRSFolder = "ADA Sales Reports"
$datasource.Create()

 

 

C# code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Ada.Cdf.ReportService2005;
namespace Ada.Cdf.Deployment.SSRS
{
public class DataSource
{
public bool ImpersonateUser { get; set; }
public bool WindowsIntegratedSecurity { get; set; }
public string UserAccountName { get; set; }
public string UserAccountPassword { get; set; }
public string ConnectionString { get; set; }
public string FileSystemPath { get; set; }
public string SSRSWebServiceUrl { get; set; }
public string _ssrsFolder = string.Empty;
public string SSRSFolder
{
get
{
// Use "/" as default value
string firstCharacter = "/";
if (string.IsNullOrEmpty(_ssrsFolder))
{
_ssrsFolder = firstCharacter;
}
// Report folder should start with one "/"
_ssrsFolder = firstCharacter + _ssrsFolder.TrimStart(firstCharacter.ToCharArray());
return _ssrsFolder;
}
set
{
_ssrsFolder = value;
}
}
/// <summary>
/// Create a DataSource
/// </summary>
public void Create()
{
// Validate properties
if (string.IsNullOrEmpty(this.FileSystemPath)) { throw new ArgumentNullException("this.FileSystemPath"); }
if (!File.Exists(this.FileSystemPath)) { throw new ApplicationException(string.Format("The file [{0}] does not exist", this.FileSystemPath)); }
if (string.IsNullOrEmpty(this.SSRSWebServiceUrl)) { throw new ArgumentNullException("this.SSRSWebServiceUrl"); }
if (string.IsNullOrEmpty(this.SSRSFolder)) { throw new ArgumentNullException("this.SSRSFolder"); }
if (string.IsNullOrEmpty(this.UserAccountName)) { throw new ArgumentNullException("this.UserAccountName"); }
if (string.IsNullOrEmpty(this.UserAccountPassword)) { throw new ArgumentNullException("this.UserAccountPassword"); }
// Initialize webservice proxy
ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();
rs.Url = this.SSRSWebServiceUrl;
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Determine filename without extension (used as name in SSRS)
FileInfo fileInfo = new FileInfo(this.FileSystemPath);
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(fileInfo.FullName);    
// Create DataSourceDefinition
DataSourceDefinition definition = new DataSourceDefinition();
definition.CredentialRetrieval = CredentialRetrievalEnum.Store;
definition.ConnectString = this.ConnectionString;
definition.UserName = this.UserAccountName;
definition.Password = this.UserAccountPassword;
definition.Enabled = true;
definition.EnabledSpecified = true;
definition.Extension = "SQL";
definition.ImpersonateUser = this.ImpersonateUser;
definition.ImpersonateUserSpecified = true;
// Use the default prompt string
definition.Prompt = null;
definition.WindowsCredentials = this.WindowsIntegratedSecurity;
// Create datasource
rs.CreateDataSource(fileNameWithoutExtension, this.SSRSFolder, true, definition, null);
}
/// <summary>
/// Delete a DataSource
/// </summary>
public void Delete()
{
// Validate properties
if (string.IsNullOrEmpty(this.FileSystemPath)) { throw new ArgumentNullException("this.FileSystemPath"); }
if (string.IsNullOrEmpty(this.SSRSWebServiceUrl)) { throw new ArgumentNullException("this.SSRSWebServiceUrl"); }
if (string.IsNullOrEmpty(this.SSRSFolder)) { throw new ArgumentNullException("this.SSRSFolder"); }
// Initialize webservice proxy
ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();
rs.Url = this.SSRSWebServiceUrl;
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Determine filename without extension (used as name in SSRS)
FileInfo fileInfo = new FileInfo(this.FileSystemPath);
string fileNameWithoutExtension = Path.GetFileNameWithoutExtension(fileInfo.FullName);
// Determine full SSRS path
string ssrsPath = string.Format("{0}/{1}", this.SSRSFolder, fileNameWithoutExtension.TrimStart("/".ToCharArray()));
// Delete datasource
rs.DeleteItem(ssrsPath);
}
}
}

 

 

Result

image

How to create a folder in SQL Server Reporting Services with PowerShell

If you want to use PowerShell to create a folder in SQL Server Reporting Services use the following script:

PowerShell script

"Set execution policy to [Unrestricted]"
Set-ExecutionPolicy Unrestricted

"Load assembly"
[System.Reflection.Assembly]::LoadFrom("C:\Temp\Ada.Cdf.dll")

$folder = New-Object Ada.Cdf.Deployment.SSRS.Folder
$folder.SSRSWebServiceUrl = "http://localhost/ReportServer/ReportService2005.asmx"
$folder.Name = "ADA Sales Reports"
$folder.Parent = "/"
$folder.Create()

 

 

 

C# Code

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
namespace Ada.Cdf.Deployment.SSRS
{
public class Folder
{
public string SSRSWebServiceUrl { get; set; }
public string _parent = "/";
public string Parent
{
get
{
// Use "/" as default value
string firstCharacter = "/";
if (string.IsNullOrEmpty(_parent))
{
_parent = firstCharacter;
}
// Parent should start with one "/"
_parent = firstCharacter + _parent.TrimStart(firstCharacter.ToCharArray());
return _parent;
}
set
{
_parent = value;
}
}
public string _name = string.Empty;
public string Name
{
get
{
if (string.IsNullOrEmpty(_name))
{
_name = string.Empty;
}
// FolderName should not start with"/"
_name = _name.TrimStart("/".ToCharArray());
return _name;
}
set
{
_name = value;
}
}
/// <summary>
/// Create a report
/// </summary>
public void Create()
{
// Validate properties
if (string.IsNullOrEmpty(this.SSRSWebServiceUrl)) { throw new ArgumentNullException("this.SSRSWebServiceUrl"); }
if (string.IsNullOrEmpty(this.Name)) { throw new ArgumentNullException("this.Name"); }
if (string.IsNullOrEmpty(this.Parent)) { throw new ArgumentNullException("this.Parent"); }
// Initialize webservice proxy
ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();
rs.Url = this.SSRSWebServiceUrl;
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
try
{
rs.
// Create report folder, this will throw an exception if the folder already exists
rs.CreateFolder(this.Name, this.Parent, null);
}
catch (Exception ex)
{
Global.Logger.Info("Folder already exists", ex);
}
}
/// <summary>
/// Delete a report
/// </summary>
public void Delete()
{
// Initialize webservice proxy
ReportService2005.ReportingService2005 rs = new ReportService2005.ReportingService2005();
rs.Url = this.SSRSWebServiceUrl;
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
// Determine full SSRS path
string ssrsPath = string.Format("{0}/{1}", this.Parent.TrimStart("/".ToCharArray()), this.Name);
// Delete report
rs.DeleteItem(ssrsPath);
}
}
}

Result

image

Explaining SSRS datasource options “Use as Windows credentials when connecting to the datasource” and “Impersonate the authenticated user after a connection has been made to the datasource”

In SSRS ther are two options you can set, when the credentials of the datasource are stored securely in the report server:

  • Use as Windows crendentials when connecting to the datasource

This option must be checked, when you want to use “Windows Integrated Security Authentication” and not “SQL Authentication”

  • Impersonate the authenticated user after a connection has been made to the datasource

This option must be checked, when you want to use the current user to query the database, instead of the given account. The given account is used to connect to the database, but after the connection has been made, the current user is used to query the database. If the current user does not have permissions an exception will occur.

image

ReportViewer control does not show default images on IIS7

If you use the ReportViewer control in you’re C# web application and the ReportViewer does not show the default images (refresh button etc) then you can check you’re web.config if it contains the correct axd handler:

<add name=": Reserved-ReportViewerWebControl-axd" path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler" resourceType="Unspecified" preCondition="integratedMode" />

In mine web.config it look like:

<system.webServer>
<validation validateIntegratedModeConfiguration="false"/>
<modules runAllManagedModulesForAllRequests="true">
<remove name="ScriptModule"/>
<remove name="UrlRoutingModule"/>
<add name="ScriptModule" preCondition="managedHandler" type="System.Web.Handlers.ScriptModule, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add name="UrlRoutingModule" type="System.Web.Routing.UrlRoutingModule, System.Web.Routing, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
</modules>
<handlers>
<remove name="WebServiceHandlerFactory-Integrated"/>
<remove name="ScriptHandlerFactory"/>
<remove name="ScriptHandlerFactoryAppServices"/>
<remove name="ScriptResource"/>
  <add name=": Reserved-ReportViewerWebControl-axd" path="Reserved.ReportViewerWebControl.axd" verb="*" type="Microsoft.Reporting.WebForms.HttpHandler" resourceType="Unspecified" preCondition="integratedMode" />
    <add name="ScriptHandlerFactory" verb="*" path="*.asmx" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add name="ScriptHandlerFactoryAppServices" verb="*" path="*_AppService.axd" preCondition="integratedMode" type="System.Web.Script.Services.ScriptHandlerFactory, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add name="ScriptResource" preCondition="integratedMode" verb="GET,HEAD" path="ScriptResource.axd" type="System.Web.Handlers.ScriptResourceHandler, System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31BF3856AD364E35"/>
<add name="UrlRoutingHandler" preCondition="integratedMode" verb="*" path="UrlRouting.axd" type="System.Web.HttpForbiddenHandler, System.Web, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"/>
</handlers>
</system.webServer>