SQL Server Reporting Service – Security

Reporting Services provides an authentication subsystem and a role-based authorization model that determines access to the report server and to items that are managed by the report server.

  • Authentication is based on Windows Authentication or a custom authentication module that you provide.
  • Authorization is based on roles that you assign to users or groups in your organization.

Authentication and authorization models vary depending on whether the report server runs in native mode or SharePoint integrated mode. (SharePoint permissions determine who has access to the report server)

Reporting Services uses role-based security to control access to items that are stored on a report server. When you grant a user access to a report server, you typically do so by creating a pair of role assignments:

  • At the site level
  • On Home, which is the root node of the report server folder hierarchy

Security is inherited within the report server folder hierarchy. Creating role assignments at the site level and on the Home folder sets permission inheritance that extends to all items and operations on a report server.

You can override permission inheritance by defining security for individual items. Items that you can secure individually include:

  • Folders
  • Reports
  • Report models
  • Resources
  • Shared data sources

Other constructs, such as schedules and subscriptions, are not explicitly secured. Schedules and subscriptions operate within the security of a report.

Limit Permission to Shared data sources:

  1. Assign System User role to the user/group
  2. Assign Report Builder role (view reports, view folders, view models, Consume reports) on /Home
  3. Assign Browser role (view reports, view folders, view models) on Data Sources folder
  4. Assign My Reports role (view data sources) on shared data sources

Grant Admin permission to user

  1. Assign System Admin role to user
  2. Assign Content Manager (set security, view report, etc) on /Home

The report server database is an internal component, accessed only by the report server. The credentials and connection information you specify for the report server database are used exclusively by the report server. Users who request reports do not require databases permissions or a database login for the report server database.


SQL Server Reporting Services – Report Models

A report model is a metadata description of a data source and its relationships. In order to create reports using Report Builder, you must first build a report model based on your SQL Server database, SQL Server 2005 or later Analysis Services cube, or Oracle databases running version or later.

  1. For the user building the report, report models expose only specified views and items to choose from.
  2. A report model is a metadata layer on top of a physical database that identifies business entities, fields, and roles.
  3. To create model-based reports, at least one report model needs to be available.
  4. To build a report using the model that you just created, you must publish it to a report server. The data source and data source view are included with the model when it is published.

In order to build a model, you must first create a report model project. A report model project is a container for the model and consists of one or more data source (.ds) files, one or more data source view (.dsv) files, and one or more report model (.smdl) files. Only one Data Source (.ds) and Data Source View (.dsv) can be referenced in an .smdl file (Report Model).

Models are published to a report server or SharePoint library and users must have permissions to access the model in order to use it in Report Builder.

Create Report Model Project:

1. Open Business Intelligence Development Studio.

2. Create new project and select “Report Model Project” template.

3. Define a Data Source for this project.

After defining the data sources that you will use in your report model project, your next step is to define a data source view for the project. A data source view is a logical data model based on one or more data sources. However, only data source views based on a single data source can be used for report models.

Data source views facilitate the model design process by providing you with a useful representation of the data that you specified. Because metadata is stored in the data source view, you do not need to be connected to the underlying data source to work with the objects. You can rename tables, and fields, as well as add aggregate fields and derived tables, in a data source view without changing the underlying data source. For an efficient model, add only those tables to the data source view that you intend to use.

A report model is a metadata layer on top of a physical database that identifies business entities, fields, and roles. Once published, these models enable Report Builder users to develop reports without having to be familiar with database structures or understand and write queries. Models are comprised of entities, which are sets of related report items that are grouped together under a friendly name, predefined relationships between these business entities, and predefined calculations. Models are defined using an XML language called Semantic Model Definition Language (SMDL). The file extension for report model files is .smdl.

Report models have the following features:

  • Database fields and views can be given logical business names, so knowledge of the database structure is not required to produce reports.
  • Items can be grouped logically.
  • Relationships between items can be defined.
  • Model elements can be secured so that users can see only the data that they have permission to see.

SQL Server 2008 – SSRS Q&A

Q: How to display Parameter value on SSRS Reports

A: To display the parameter value on the report, create a textbox on the report and then set it’s expression to your parametername.value. Right Click => Expression


Q: How to add code behind to SSRS report?

A: Report Properties => Code, call the function in Expression:  =Code.ChangeWord(Fields!Category.Value)


MSDN: Add Code to a Report (SSRS)

Q: How to populate the report parameter with a list of available values?

A: Create a DataSet to generate a list of values for the report parameter first. Report Data => Parameters => Report Parameter Properties => Available Values select Get value from a query and define value and label field. 



MSDN: Adding Parameters to Create a List of Available Values

Q: How to create a drillthrough (drill down and up) report?

A: To create a drillthrough report, you must design the drillthrough report first, before you create the drillthrough action in the main report. You can add drillthrough links to text boxes (including text in the cells of a table or matrix), images, charts, gauges, and any other report item that has an Action property.


MSDN: Add a Drillthrough Action on a Report

Q: How to concatenate strings using Expression?

A: In Expression Window type: = Fields!Province.Value & ” : ” & Count(Fields!City.Value)

Work with Report Model in SSRS

The report model is the metadata layer component that serves as the basis for building reports. Report designers, both end users and IT staff, will use the report model as the data source for the report instead of directly accessing a relational database or OLAP cube data source when designing reports. 

The report model contains three key elements necessary for the ad hoc reporting process to function smoothly:

  • bindings to the underlying data-source schema
  • a logical representation of the schema in the form of business entities that are familiar to end users
  • the default information to be used according to the role that the entity plays in the report (primary entity, lookup entity, summary, and so on).

A report model definition is an XML file that follows the Semantic Model Definition Language (SMDL) specification.

Report Model Components

Report models primarily consist of the following three item types:

  • Entities are the equivalent of objects or tables in a database. Entities are composed of attributes and roles.
  • Attributes are the equivalent of object properties or columns in a database table.
  • Roles are the equivalent of object references or relational foreign keys and join operations.

Additionally, report models incorporate some organization functionality, allowing administrators to create folders (containers) and perspectives (sub-models). Consequently, report models for larger data sources can be more focused for their intended audience.

Clickthrough Reports ***

In SQL Server 2005 Enterprise Edition, Report Builder includes a practical feature that enables users to drill through on the data in their reports without having to first design target reports and connect traditional drill-through links to them. This feature is called clickthrough reports. Clickthrough reports are generated automatically from the report model metadata.

For example, while a user is looking at last month’s sales summary report, he might need to find out more about a certain order. Clickthrough reports allows him to click on the Order ID column, triggering an automatically generated report that shows the full order details for that order. Then, he might need to find out about a specific product or about the customer that placed that order. Again, he can click on the Product ID column or on the Customer Name field to see another clickthrough report about those fields.

Using SQL Server Management Studio, model designers can attach a custom report as the drill-through target if the automatically generated report is not adequate.

Because automatic generation of clickthrough reports relies entirely on the report model metadata, a well-designed report model is critical for a successful user experience and for reducing the need to develop additional custom reports. A correctly adjusted model will greatly increase user productivity and satisfaction. A poor or incomplete model will sit unused at best. Automatically generated report models from relational sources should be considered only a starting point for your model, not an end; you will need to edit the model to customize it in a business-savvy way.

Creating a report model with the Report Model Wizard involves several steps:

1.      Create a connection to a data source.

2.      Create a Data Source View (DSV) to contain schema metadata.

3.      Review the resulting DSV to do a basic clean-up and revise the metadata to fit your business requirements.

4.      Using the revised DSV, invoke the Report Model Wizard to create the base model.

5.      Edit the report model to adjust it to expected usage.

6.      Deploy the report model to the Reporting Services report server. The model is deployed automatically when it is generated through Report Manager or SQL Server Management Studio.

What Is the Difference Between Microsoft SSRS, SSIS and SSAS?

Microsoft complements its relational database engine, SQL Server, with several add-on services that manage different aspects of enterprise business intelligence and information processing such as data integration, reporting and analysis. While SQL Server can function independently of these services, they add value by enhancing SQL Server’s capacity as a robust information platform for developing comprehensive business intelligence solutions. Services include the SQL Server Integration Services, SQL Server Reporting Services and SQL Server Analysis Services. These services are core products of the SQL Server 2008 R2 version, but they differ significantly in the services they provide as part of the full server suite.


SQL Server 2008 R2 Services Availability in Editions

One key consideration and difference is that not all services come with the different editions of Microsoft SQL Server 2008 R2. The six SQL Server Editions for SQL Server 2008 R2 are Datacenter, Enterprise, Standard, Web, Workgroup and Express. Integration Services is included in the Datacenter and Enterprise editions. Reporting Services is included in all editions of SQL Server 2008 R2. However, full reporting services are limited in the Standard, Web Workgroup and Express editions. Lastly, Analysis Services is available in the Datacenter, Enterprise and Standard editions with advanced analytical functions in the Enterprise and Datacenter editions only. The Standard edition does not include SQL Server PowerPivot for SharePoint that is part of the basic Analysis Services package.

SQL Server Integration Services

The SQL Server Integration Services (SSIS) is the data-warehousing arm of the SQL Server 2008 R2 suite — equipped with superior Extract, Transform and Load (ETL) capabilities. It provides the vehicle for moving data from different data sources to another and changing the data, if necessary. The three components in the SSIS platform for starting the data integration process are the Import and Export Wizard, SSIS Designer and SSIS API Programming. The Import and Export Wizard simply transfers data from source to destination but does not include data transformation capabilities. The SSIS Designer is an integrated component of the Business Intelligence Development Studio used for the development and maintenance of integration services packages. SSIS API Programming module allows you to code SSIS packages using any number of programming languages.

SQL Server Reporting Services

The SQL Server Reporting Services (SSRS) is a framework of reporting mechanisms such as the Report Builder, Report Designer, Report Manager and Report Server that work together through a Web interface to enable the development of concise interactive reporting solutions in print or Web format. Report Builder and Report Designer are two SSRS components for generating reports. Report Builder is a simple solution for the information worker or business user to create quick reports without the need to understand the core structure of the data. The Report Designer is a tool for developers because it adds complexity to custom reports development. Using this tool requires an understanding of the Business Intelligence Development Studio shell in Visual Studio and the underlying structure of the data. According to Microsoft, the Report Server is the core process engine in SSRS that manages the processing and delivery of reports using processors. The Report Manager is an administrative tool that controls reporting services through a Web interface.

SQL Server Analysis Services

The SQL Server Analysis Services, or SSAS, is a multidimensional analysis tool that features Online Analytical Processing, powerful data mining capabilities, and deeper dimensions to business information within a relational database. Multidimensional analysis is an OLAP technique that produces the ability to analyze large quantities of data by storing data in axes and cells instead of the traditional relational two-dimensional view in rows and columns. SSAS places predictive analytic capabilities in the hands of information workers by creating an instant connection to backend data using familiar applications such as Microsoft Excel and SharePoint for analysis, visual presentation and collaboration.


Reference: http://smallbusiness.chron.com/difference-between-microsoft-ssrs-ssis-ssas-34689.html