Tag: SSRS

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.

Reference

http://technet.microsoft.com/en-us/library/gg492251%28v=sql.110%29.aspx

http://technet.microsoft.com/en-us/library/gg492252.aspx

http://en.wikipedia.org/wiki/List_of_SMS_gateways

 

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]

go

select * from ConfigurationInfo where Name=’EnableExecutionLogging’

go

Get the current retention period for the Execution Log

Use [ReportServer]

go

select * from ConfigurationInfo where Name=’ExecutionLogDaysKept’

go

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 

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

SQL Server 2008 R2 Tools

SQL Server 2008 R2 comprises number of new features and tools that can be used to develop and manage databases and database solutions.

SQL Server Management Studio

SQL Server Management Studio SSMS first launched with Microsoft SQL Server 2005. It’s used as an integrated environment for accessing, configuring, managing, and administering all components within SQL Server. Management Studio includes number of graphical tools and rich script editors.

Business Intelligence Development Studio

Business Intelligence Development Studio BIDS is an integrated environment for developing business intelligence solutions including Analysis Services cubes, data sources, Reporting Services reports, and Integration Services packages.

It is based on the Microsoft Visual Studio development environment with  customised SQL Server solutions and projects. One solution can include projects of different type and independent from specific server.

Reporting Services Components and Tools

SQL Server 2008 Reporting Services SSRS makes available Report server components for data and report processing and report delivery. It also include Report Designer, Report Builder, Model designer, Report manager and Reporting Services Configuration tool  to configure a Reporting Services installation.

Integration Services Tools and Utilities

SQL Server 2008 Integration Services SSIS provides complete SSIS package creation and management tools such as SSIS Designer for building data transformation packages that include control flows, data flows, and event-driven logic, Query Builder, Expression Builder, and several command prompt utilities for managing and running packages.

SQL Server Configuration Manager

SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers.

SQL Server Profiler

SQL Server Profiler is a tool that captures SQL Server events from an instance of the Database Engine. The events can be saved in a trace file or in database for analysis and troubleshooting.

Database Engine Tuning Advisor

SQL Server Database Engine Tuning Advisor assists database administrator in creating effective indexes, indexed views, and partitions.

Command Prompt Utilities

The SQL Server Database Engine offers additional tools that can be run from the command prompt. Most popular tools are bcp, sqlcmd and tablediff. Bcp (bulk copy program) tool addresses the bulk movement of data into and out of a SQL Server database. Sqlcmd enables database administrator interacting with SQL Server from the command line. Tablediff command line utitlity provides the ability to compare the content of two tables in a database.