Category: SSRS

SQL Server Reporting Services

How to display data in SSRS Report from multiple datasets

Since SSRS 2008 R2 multiple datasets can be joined in a single data region on the report. As a result of the expression field we can display the first matching value for the specified name from secondary dataset that contains name/value pairs.

SQL Reporting Services comes with three lookup functions having four parameters as given below

  1. Lookup (source_expression, destination_expression, result_expression, dataset)
  2. LookupSet (source_expression, destination_expression, result_expression, dataset)
  3. MultilookUp (source_expression, destination_expression, result_expression, dataset)


LookUp Functions Parameters

  • source_expression. Evaluated in the current scope and that specifies the name or key to look up. For example, =Fields!AgentID.Value.
  • destination_expression. Evaluated for each row in a dataset and that specifies the name or key to match on. For example, =Fields!Agent_ID.Value.
  • result_expression. Evaluated for the row in the dataset where source_expression = destination_expression, and that specifies the value to retrieve. For example, =Fields!TeamLeader.Value.
  • dataset. Name of a dataset in the report. For example, “Agents”


Lookup LookupSet MultiLookup
Retrieves the value from the specified dataset for a name/value pair where there is a 1-to-1 relationship. Retrieves multiple values for a single name or key field where there is a 1-to-many relationship Retrieves a set of values from a dataset for name-value pairs where each pair has a 1-to-1 relationship
For example, for an ID field in a table, you can use Lookup to retrieve the corresponding Name field from a dataset that is not bound to the data region For example, for a customer identifier in a table, you can use LookupSet to retrieve all the associated phone numbers for that customer from a dataset that is not bound to the data region For example, for a multivalue parameter that is based on primary key identifiers, you can use Multilookup in an expression in a text box in a table to retrieve associated values from a dataset that is not bound to the parameter or to the table.
Returns the result expression value Returns the set of result expression values

Because LookupSet/MultiLookup returns a collection of objects, you cannot display the result expression directly in a text box. You can concatenate the value of each object in the collection as a string

Use the Visual Basic function Join create a delimited string from a set of objects. Use a comma as a separator to combine the objects in a single line. In some renderers, you might use a Visual Basic line feed (vbCrLF) as a separator to list each value on a new line.

=Join(LookupSet(Fields!SiteGroupID.Value, Fields!ID.Value, Fields!SiteCategoryName.Value, “Category”),”,”)

=Join(MultiLookup(Split(Fields!SiteCategoryList.Value,”,”), Fields!ID.Value,Fields!SiteCategoryName.Value,”Category”)),”, “)

  • Evaluated after all filter applied
  • Only one level of lookup is supported. A source, destination, or result expression cannot include a reference to a lookup function.
  • Source and destination expressions must evaluate to the same data type. The return type is the same as the data type of the evaluated result expression.
  • Source, destination, and result expressions cannot include references to report or group variables.
  • Cannot be used as an expression for the following report items:
    • Dynamic connection strings for a data source
    • Calculated fields in a dataset
    • Query parameters in a dataset
    • Filters in a dataset
    • Report parameters
    • The Report.Language property


SSRS Lookup Example

SSRS Lookup report

Dataset1 includes an Agent Name identifier (agent_name_tpi) field. Dataset2 contains the corresponding AgentName and total calls answered AnsCalls.

In the following expression, Lookup compares the value of agent_name_tpi to AgentName in each row of the dataset2 and, when a match is found, returns the value of the AnsCalls field for that row.

SQL Server Reporting Services Data Alerts

SQL Server 2012 introduced data alerts as a way to notify users of changes to report data, enabling them to view reports only when changes in the data may have business implications. By using this data driven alerting solution you no longer have to seek out information, it comes to you. 

Data alert messages are sent by email. Depending on the importance of the information, you can choose to send messages more or less frequently and only when results change. You can specify multiple email recipients and this way keep others informed to enhance efficiency and collaboration.

Good example of the data alerts can be sending out monthly sales report data only if total sales amount is less than a specified amount.


Data Alerts Key areas

  • To create data alerts on reports, the reports must be saved or uploaded to a SharePoint library. This means that you cannot create alerts on reports saved to a report server in native mode or your computer. Also, you cannot create alerts embedded in custom applications.
  • You can create data alerts on reports with data source configured to use stored credentials, or no credentials. You cannot create alerts on reports configured to use integrated security credentials or prompt for credentials. The report is run as part of processing the alert definition and the processing fails without credentials.
  • SQL Server alerting database is automatically created with Reporting Services in SharePoint mode install. Data alert definitions and alerting metadata are saved in the alerting database. By default, this database is named ReportingServices<GUID>_Alerting.
  • When the data alert definition saved, alerting creates a SQL Server Agent job for the alert definition. The job includes a job schedule. The schedule is based on the recurrence pattern defined on the alert definition. Running the job initiates the processing of the data alert definition.
  • An alert has only one owner, the person who created it. The alert owner can view information about your data alerts and delete and edit your data alert definitions
  • Alerting administrators, users with SharePoint Manage Alerts permission, can manage data alerts at the site level. They can view lists of alerts by each site user and delete alerts.
  • Reporting Services data alerts are different from SharePoint alert. SharePoint alerts on any document type, including reports. SharePoint alerts are sent when the document definition changes. Data alerts are sent when the data shown in a report satisfied rules in the alert definitions.
  • Multiple alerts on a report and multiple users can create the same or different alerts on a report
  • Report has to have data regions, to create an alert on it.


Here is Microsoft diagram for data alert definition

data alerts process


 Creating data alert definition

  1. Locate the SharePoint library that contains the report that you want to create a data alert definition for.
  2. Run the report. Verify that the report shows the data that you want to receive alert messages about. NOTE: The parameter values you chose to run the report are saved in the alert definition and will be used when report is rerun as a step in processing the alert definition. To use different parameter values, you must create a new alert definition
  3. On the Actions menu, click New Data Alert. The Data Alert Designer opens, showing the first 100 rows of the first data feed that the report generates in a table.

actions to create new data alert

4. Select data feed in the Report data name drop-down list.

  1. Click Add rule. In the list, select the column that you want to use in the rule, and then select a comparison operator and enter the threshold value.
  2. Select an option in the recurrence list. Depending on the type of recurrence, enter an interval.
  3. Enter the email addresses of alert message recipients. Separate addresses with semicolons. If the email address of the person who created the alert definition is available, it is added to the Recipient(s) box.
  1. Click Save.


data alerts scheduling


Data Alerts Maintenance

Once setup, alerts can be maintained by right clicking on the report requiring alert maintenance, and then selecting Manage Data Alerts 

The alert maintenance screen provides the following information: last run, status, sent alerts.  Alert can be edited to change items, such as, the rule criteria, the dataset, or the email recipient.



SSRS Reports Caching

In SSRS there are two options for pre-loading SSRS Reports: caching and snapshots

  • A cached instance of a report is based on the intermediate format of a report. The report server generally caches one instance of a report based on the report name. However, if a report can contain different data based on query parameters, multiple versions of the report may be cached at any given time. The first user who runs the report with a unique region code creates a cached report that contains data for that parameters. Subsequent users who request the report using the same parameters get the cached copy.
  • The other option is running a report from the report snapshot. Snapshot is a report that contains layout information and data refreshed at a specific point in time. A report snapshot is usually created and refreshed on a schedule, allowing you to time exactly when report and data processing will occur. A report snapshot is stored in the intermediary form in the ReportServer database, where it is subsequently retrieved when a user or subscription requests the report. When a report snapshot is updated, it is overwritten with a new instance. The report server does not save previous versions of a report snapshot unless you specifically set options to add it to report history.

Note: Copy of the processed cached report after the execution is saved into into ReportServerTempDB. Data source with stored credentials on the report server must be used in order to implement caching.


ReportServer Database ReportServerTempDB
Stores information about the report schema, report property, data sources, parameters, stores the folder hierarchy, report Execution log Stores only the cached copy of the report data
Always exists until changes are made to the RDL Schema Expires based Expiry settings
Helps to access the structure Helps to improve the performance of report execution as it is loading data from cache
Data always exists during SSRS service restarts Services restarts will clear the Temp Data, all cached instances are reinstated when the Report Server Web service comes back online

Refreshing the Cache

A cached report is replaced with a newer version when a user selects the report after the previously cached copy has expired. Reports that are configured to run as cached instances are removed from the cache at regular intervals based on expiration settings. You can set a report’s expiration in minutes or at a scheduled time, as determined by the data’s immediacy requirement. You cannot delete reports from the cache directly unless you use the SOAP API.

To configure cache expiration, you can use a shared schedule or report-specific schedule. If you use a shared schedule and it is subsequently paused, the cache does not expire while the schedule is inoperative. If the shared schedule is subsequently deleted, a copy of the schedule settings is saved as a report-specific schedule.

If a schedule expires or if the scheduling engine is unavailable at a cache expiration date, the report server runs a live report until scheduled operations can be resumed (by either extending the schedule or starting the scheduling service).

Preloading the Cache

To improve server performance, you can preload the cache. You can preload the cache with a collection of parameterised report instances in two ways:

  1. Create a cache refresh plan. When you create a refresh plan, you can specify a schedule for a single report or specify a shared schedule.
  2. Create a data-driven subscription that uses the Null Delivery Provider. When you specify the Null Delivery Provider as the method of delivery in the subscription, the report server targets the report server database as the delivery destination and uses a specialized rendering extension called the null rendering extension. In contrast with other delivery extensions, the Null Delivery Provider does not have delivery settings that you can configure through a subscription definition.

When you specify a schedule or when you create the data-driven subscription, you schedule how often the reports are delivered to the cache. In order for new copies to be delivered to the cache, the old copies must have expired. Therefore, the Execution properties of the report must be configured to include cache expiration settings. The expiration setting must be consistent with the subscription schedule that you define. 

For example, if you create a subscription that runs every night, the cache should also expire every night prior to the subscription’s run time. If the Execution properties do not include expiration times, newer deliveries are disregarded.


To schedule the expiration of a cached report

      1. Start Report Manager (SSRS Native Mode).
      2. In Report Manager, navigate to the Contents page. Navigate to the report for which you want to set caching properties, hover over the item, and click the drop-down arrow.
      3. In the drop-down menu, click Manage.
      4. In the left frame, click the Processing Options.
      5. On the page, select Always run this report with the most recent data.
      6. Select one of the following two cache options and configure expiration as follows:
        • To configure a cached copy to expire after a particular time period, click Cache a temporary copy of the report. Expire copy of report after a number of minutes. Type the number of minutes for report expiration.
        • To configure a cached copy to expire on a schedule, click Cache a temporary copy of the report. Expire copy of report on the following schedule. Click Configure, or select a shared schedule to control report expiration. My Schedule runs every morning

        Processing Options: Always run this report with the most recent data

      7. Click Apply.
      8. To create Cache Refresh Plan which create a schedule for preloading the cache with temporary copies of data for a report click Cache Refresh Options and click on New Cache Refresh Plan.
      9. Fill in the blanks, set Shared schedule. Then click on OK button.

ssrs cache refresh options


Global Site Schedules: one to generate cached report and the other to delete cashed report

expired cache schedule

report cache schedule


Some useful T-SQL queries (SSRS caching)

Check logging Enable:

Use [ReportServer]


select * from ConfigurationInfo where Name=’EnableExecutionLogging’


Get the current retention period for the Execution Log

Use [ReportServer]


select * from ConfigurationInfo where Name=’ExecutionLogDaysKept’


Execution Log details, including 
  • InstanceName
  • ReportPath
  • UserName
  • ExecutionId 
  • RequestType: {Interactive, Subscription}
  • Format  rendering
  • Parameters 
  • ReportAction: { Render, BookmarkNavigation, DocumentMapNavigation, DrillThrough, FindString, GetDocumentMap, Toggle, Sort }
  • TimeStart and TimeEnd
  • TimeDataRetrieval, TimeProcessing, TimeRendering
  • Source: { Live, Cache, Snapshot, History, AdHoc, Session, Rdce }
  • Status
  • ByteCount
  • RowCount
  • AdditionalInfoIn short, an XML property bag containing additional information about the execution.

select * from ExecutionLog 

select * from ExecutionLog2

select * from ExecutionLog3 


  • ExecutionLog is for backward compatibility and available with SQL 2008 onward.
  • ExecutionLog2 is introduced with SQL 2008 
  • ExecutionLog3 is introduced with SQL 2008 R2. 

ExecutionLog2 and ExecutionLog3 are similar the difference in column name ReportPath – ItemPath, ReportAction – ItemAction

Cashed reports metadata:

SELECT C.Name, EC.*, SD.*

FROM ReportServerTempDB.dbo.ExecutionCache AS EC

INNER JOIN ReportServer.dbo.Catalog AS C ON EC.ReportID = C.ItemID

INNER JOIN ReportServerTempDB.dbo.SnapshotData AS SD ON SD.SnapshotDataID = EC.SnapshotDataID 

System.OutOfMemoryException Error in MS Reporting Services

A client was trying to view MS SQL Reportign Services report in the IE browser.

I received the error message looking like this:

reporting services error - outofmemory

That was not much help.

I logged onto my SQL Server 2008R2 and looked in Event Log and I found the following error message:

Exception information:

Exception type: OutOfMemoryException
Exception message: Exception of type ‘System.OutOfMemoryException’ was thrown

Reporting Service was not in running state either


Here what I found Microsoft website MS Knowledge Base Article 909678

You may receive the “System.OutOfMemoryException” error message when you use SQL Server Reporting Services

To resolve this issue, use one of the following methods.

Method 1

Add sufficient physical memory to the computer.

Method 2

Schedule reports to run at off-hours when memory constraints are lower.

Method 3

Adjust the MemoryLimit setting accordingly.

Note: When you render a report through the Reporting Services Web service, the Reporting Services Web service obtains the MemoryLimit setting from the Machine.config file. However, a scheduled report is rendered by the Report Server Windows service. The Report Server Windows service obtains the MemoryLimit setting from the RSReportServer.config file.

Method 4

Upgrade to a 64-bit version of Microsoft SQL Server 2005 Reporting Services to allow Reporting Services to utilize more memory.

Method 5

Redesign the report. To do this, use one of the following methods.

Redesign the report queries. You can reduce memory consumption by redesigning the report queries in the following ways:

Return less data in the report queries.

Use a better restriction on the WHERE clause of the report queries.

Move complex aggregations to the data source.

Method B

Export the report to a different format. You can reduce memory consumption by using a different format to display the report.

Method C

Simplify the report design.

You can reduce memory consumption by simplifying the report design in the following ways:

Include fewer data regions or controls in the report.

Use a drill through report to display details.


Here my Action Plan

1. Configure Max server memory for SQL Server


EXEC sp_configure 'show advanced options', 1


EXEC sp_configure 'max server memory (MB)', 2048


EXEC sp_configure 'show advanced options', 0


Note: Only 4GB RAM was available


2. Set working set memory in reportsevice config file

Reporting Services can use all available memory, to override default behaviour you need to configure an upper limit on the total amount of memory resources that are allocated to Reporting Services server applications.

WorkingSetMaximum and WorkingSetMinimum define the range of available memory. This setting does not appear in the RSReportServer.config file unless you add it manually.

When the value for WorkingSetMaximum is reached, the report server does not accept new requests. Requests that are currently in progress are allowed to complete. New requests are accepted only when memory use falls below the value specified through WorkingSetMaximum

Add Line 3 to RSReportServer.config file (Line 1 & 2 should be in the file)

Line 1. <MemorySafetyMargin>80</MemorySafetyMargin>
Line 2. <MemoryThreshold>90</MemoryThreshold>
Line 3. <WorkingSetMaximum>3000000</WorkingSetMaximum>

3. Restart Reporting Service in Configuration Manager


No more OutofMemory errors

Reporting Services migration and upgrade

There are two distinct options in updating SQL Server environment: upgrade and migrate.

  • Upgrade.It involves actual upgrade of technology on the servers and instances where they installed at the moment. Sometimes it’s called “in place” upgrade.To upgrade you need to run the SQL Server Installation Wizard. This will upgrade the report server program files, database, and all application data. You need to check what are prerequisites are, what versions and editions can be migrated. For SQL Server 2012 click here
  • Migrate. It includes more steps as upgrade as you need to install a new environment, copy your metadata and data to the new environment, and finally configure the new environment to use existing content.Before committing to migration review requirements to determine whether your hardware and software can support edition and version of SQL Server Reporting Services SSRS

Reporting Services migration checklist

  • Back up the encryption key. Use the Reporting Services Configuration tool to back up the key
  • Back up the report server database
  • Back up the following report server configuration files:
    • Rsreportserver.config
    • Rswebapplication.config
    • Rssvrpolicy.config
    • Rsmgrpolicy.config (Native mode only)
    • Reportingservicesservice.exe.config (Native mode only).
    • Web.config forthe report server ASP.NET applications).
    • Web.config for the Report Manager ASP.NET application (Native mode only).
    • Machine.config (for ASP.NET if you modified it for report server operations).
  • Move the report server database and other application files from your existing installation to a new SQL Server installation (use Install but do not configure option).
The easiest way to move the report server databases is to attach and detach them. However, this approach requires that you take the report server offline while you detach the database. Backup and restore is a better choice if you want to minimize service disruptions, use T-SQL commands script to backup and restore report server database and report server tempdb database
  • Configure the report server by following few easy steps
    1. Start the Reporting Services Configuration Manager and open a connection to the report server
    2. On the Database page, click Change Database. Click Next
    3. Click Choose an existing report server database. Click Next
    4. Select the SQL Server that now hosts the report server database and click Test Connection. Click Next
    5. In Database Name, select the report server database that you want to use. Click Next
    6. In Credentials, specify the credentials that the report server will use to connect to the report server database. Click Next
    7. Click Next and then Finish
  • Restore the encryption keys. This step is necessary for enabling reversible encryption on pre-existing connection strings and credentials that are already in the report server database.
  • Edit RSReportServer.config to include any custom settings from the previous installation
  • Optionally, configure custom Access Control Lists (ACLs) for the new Reporting Services Windows service group.
  • Test your installation
  • Remove unused applications and tools after you have confirmed that the new instance is fully operational

Note: A Reporting Services installation requires that the SQL Server Database Engine instance include the RSExecRole role. Role creation, login registration, and role assignments occur when you set the report server database connection through the Reporting Services Configuration tool.


Reporting services Migration tools

  1. Reporting Services Migration Tool.  It’s a Microsoft original tool. It provides UI and commnd line utility to migrates reports and other artifacts from one report server to another report server. It can also be used as a backup and restore tool for Reporting Services. Source and target server must be SQL Server Reporting Services 2008 R2 or later. Unfortunately the Target server must be SharePoint integrated mode only at the time of writing.
  2. Reporting Services Scripter. It’s a .NET Windows Forms application. It helps in scripting and transfer of all Microsoft SQL Server Reporting Services catalog items. It can also be used to easily move items on mass from one Reporting Services folder to another on the same server. Reporting Services Scripter can also transfer all catalog item properties such as descriptions, history options, execution options (including report specific and shared schedules), subscriptions (normal and data driven) and server side report parameters. Available only for SQL2000, SQL2005, SQL2008.
  3. Reportsync. The tool can be used to sync SSRS report between two report SSRS servers. Other use include download rdls from SSRS to local PC, upload files to a SSRS server, automatically attach datasources on upload