Different ways to add parameters in SSRS reports

Dependent parameter in SSRS

How do I create a dependent parameter in SSRS? For example, select a Supervisor in the first drop down and then the Employees for that Supervisor appear in the second drop down.

Master Skills of Manipulating Report Parameters

Before I answer the question, I’d like to draw your attentions to skills report developers need to master.

Building interactive SQL Server Reporting Services reports not only require report developers to master skills of using parameters, but also manipulating report parameters in many different ways. Here are some of the ways we can manipulate the report parameters:

1. Cascading parameters – example: users select a supervisor from the dropdown, then the employees who report to the selected supervisor will appear in the second dropdown.
2. Available Values – example: pre-populate a dropdown list with all the supervisors.
3. Default Values – example: the Start Date and End Date parameter will be pre-populated with the Beginning of the Month, and the current date, respectively.
4. Multi-value – example: to see productivity of several employees at the same time, users can select multiple employees from a pre-populated dropdown list
5. Custom code in SQL stored procedures – Stored procedures allow the ultimate flexibility to use report parameters. One example is to use SQL code to split comma separated list into table values.
6. Using parameters to dynamically change report item properties – example: use a report view parameter to dynamically show data at aggregation or detail level
7. Using parameters in Drill Through and Sub reports

SSRS : What is Report Builder

What is Report Builder

Microsoft SQL Server Reporting Services Report Builder 3.0 is a report authoring tool that features a Microsoft Office-like authoring environment and new features such as new sparkline, data bar, and indicator data visualizations, the ability to save report items as report parts, a wizard for creating maps, aggregates of aggregates, and enhanced support for expressions. For more information about new features in Report Builder 3.0, see What's New in Report Builder 3.0 in Report Builder 3.0 Help on msdn.microsoft.com.

Table,Matrix,Chart & Map Wizard in Report Builder 3.0

Easy-to-use wizards walk you through the steps of creating a table, matrix, chart, or map on your report. Steps to create a table, matrix, or chart include

• Adding a connection to a data source.
• Creating or importing a dataset.
• Arranging fields.
• Choosing a layout or a chart type.
• Choosing a preliminary style.

With the Map Wizard and Map Layer Wizard, you can add maps and map layers to your report to help visualize data against a geographic background.

Use the wizards as a starting point for creating a data region that you can continue to modify after you've finished the wizard.

Modify Reports from other Environments

You can customize and update all existing reports, regardless of where they were initially designed. Thus, your organization can create more advanced reports within Microsoft SQL Server Business Intelligence Development Studio and distribute them to end users, who can customize them in Report Builder to meet their needs. You can also update reports created in previous versions of Report Builder.

Multiple Data Regions and Sources

Full support for all of the capabilities of Report Definition Language (RDL) means that you can use Report Builder to create reports with multiple data regions (such as tables and charts), as well as data from multiple data sources within a single report. Report Builder also supports queries that run directly against relational data sources (SQL Server, Oracle, Teradata, OLE DB, and ODBC) and multidimensional data sources (SQL Server Analysis Services, Oracle, Hyperion Essbase, SAP NetWeaver BI). Report Builder also supports user-friendly data access via published report models (SMDL) based on SQL Server, SQL Server Analysis Services, Oracle, and Teradata.

Please feel free to comment

Thanks and Regards 
Subodh 

SSRS : What is a report manager

Report Manager is a Web-based report access and management tool that you use to administer a single report server instance from a remote location over an HTTP connection. You can also use Report Manager for its report viewer and navigation features. You can use Report Manager to perform the following tasks:

• View, search, print, and subscribe to reports.
• Create, secure, and maintain the folder hierarchy to organize items on the server.
• Configure role-based security that determines access to items and operations.
• Configure report execution properties, report history, and report parameters.
• Create report models that connect to and retrieve data from a Microsoft SQL Server Analysis Services data source or from a SQL Server relational data source.
• Set model item security to allow access to specific entities in the model, or map entities to predefined clickthrough reports that you create in advance.
• Create shared schedules and shared data sources to make schedules and data source connections more manageable.
• Create data-driven subscriptions that roll out reports to a large recipient list.
• Create linked reports to reuse and repurpose an existing report in different ways.
• Launch Report Builder to create reports that you can save and run on the report server.
  
  

Starting Report Manager

1. Open Microsoft Internet Explorer 6.0 or later.
   
   
2. In the address bar of the Web browser, type the Report Manager URL. By default, the URL is http://<ComputerName>/reports. The report server might be configured to use a specific port. For example, http:// <ComputerName>:80/reports or http:// <ComputerName>:8080/reports.

Icon Descriptions

Icon	Description	Action
	Report	Click the report icon or name to open the report. The report opens in a separate window.
	Report model	Click the report model icon to open model property pages.
	Linked report	Click the report icon or name to open the linked report. The report opens in a separate window.
	Folder	Click the folder icon or name to open the folder.
	Subscription	Click a subscription icon or description to edit a subscription.
	Data-driven subscription	Click a data-driven subscription icon or description to edit a subscription.
	Resource	Click the resource icon or name to open the resource. The resource opens in a separate window.
	Shared data source item	Click a shared data source icon to open the property pages, report list, and subscription list of the data source.
	Property page	Click the property icon to access additional pages to set properties and security.

Please Comment if have any Questions.

Thanks and Regards

Subodh Jena

SQL Server Reporting Services Overview and Installing in Windows Server 2008

Introduction

In this article, we would see what SQL Server Reporting Services are and a high-level overview of the architecture and the components involved in running reports on the SQL Server. This article is the first part of the series on SQL Server Reporting Services right from the architectural overview to running the customized report on different servers with deployment.

Overview

SQL Server Reporting Services provides a server based reporting platform, which allows customized reporting functionality for a variety of data that is transformed from different data sources. SQL Server Reporting Services 2008 provides a rich set of ready-to-use tools and services that help the developers and the designers to Create, Design, Deploy and Manage the Reports. It includes the basic report for the organization and provides customized reports with programming features to provide a rich user dashboard to extend and customize the functionality requirement.

SQL Server Reporting Services has a report server. It is a Windows Service with a set of features and runs separately in different application domains. SSRS has 2 components which are quite important for the architectural perspective. The first component is the Report Server, which acts as the heart of the report. Whenever a new report is created, it will be saved in the report server. It is the centralized location where all the reporting modules resides and provides an access to all the report. The second component is the Report Designer, which is also used to design the report and maintain the report design with the help of the Microsoft Visual Studio 2010 IDE or the latest IDE based on the requirement.

SQL Server Reporting Services also provides a useful feature of Scheduling and Delivery processor that pushes the reports to email inboxes or ftp locations based on the requirement. Now let us see the steps to install the SQL Server Reporting Services on to the development environment (We can make use of SQL Server 2008 R2 or SQL Server Denali CTP3) and follow the step-by-step process.

Steps to Install SQL Server Reporting Services

Download Microsoft SQL Server Denali or Microsoft SQL Server 2008 R2. In our series, we are going to use the latest version Denali reporting services. We can download the installer for SQL Server Denali CTP using the link Download SQL Server Denali CTP3

Once the download is completed, there will be 4 files downloaded as shown in the screen below. Now double click on theSQLFULL_x64_ENU_Install application file to extract the required files for installation. It would take some time to extract all the files to the same location as shown in the screen below.

Once the extraction is completed, refresh the folder. We will see a new folder created SQLFULL_x64_ENU as depicted in the screen below.

Now navigate through the folder. There will be a list of files as shown in the screen below. Point at Setup file and right-click on the file and select Run as administrator.

This will start the installation process. We can see the status message as shown in the screen below.

A new window will open leading to a step-by-step guide for installation process and some links providing help for our installation steps as shown in the screen below. We can now select the Hardware and Software Requirement to check the necessary things available for our installation. Else we can navigate to the link http://msdn.microsoft.com/en-us/library/ms143506(v=sql.110).aspx and check for the same.

In the left hand side menu, navigate to Installation. The option New SQL Server stand-alone installation or add features to an existing installation is listed as shown in the screen below. Click on the option to proceed further.

A set of Setup Support Rules runs is the background to check if the required process is available for installation. Once completed, a high-level result of how many rules failed or skipped is shown as depicted in the screen below.

To check the details of the passed results we need to click on the Show details button. This will showcase a complete list of rules ran and their result as shown in the screen below.

Now click on OK button. As the next step, a new window with evaluation options for the licenses is presented. Select Evaluation license or Express license as per the requirement. Since in this article we are going to evaluate the product installation we will select Evaluation from the list and click on Next button as shown in the screen below.

Clicking on Next button will navigate to the terms and conditions of the installation. Select the check box as shown in the screen below and click on Next button

A list of updates available to be downloaded and installed will be listed. As mentioned in the screen, it first installs the Setup Update (30MB). Remaining is downloaded & installed later. Click on Next to proceed further after selecting the updates as shown in the screen below.

Next we can see the Setup Update being downloaded as well as the status of the steps as shown in the screen below. Once the download is completed, click on Install to proceed further.

Next step is the new set of Setup Support Rules running and providing a result as depicted in the screen below. We can see two warnings. Since the server is domain controlled it shows the warning. Just ignore it and we can proceed further by clicking on Nextbutton as shown in the screen below.

To proceed further the type of installation needs to be selected. Select SQL Server Feature Installation and click on Next as shown in the screen below.

This will present a list of Features that needs to be installed. An option of Select All is available at the bottom of the list to select the complete list. In addition, we can select shared feature directory option by selecting the folder as shown in the screen below. Click onNext button to proceed further.

Now we can see some set of Installation Rules running and get the result as shown in the screen below. Click on Next to proceed further.

Further, as a next step we need to provide instance with a Name. Here we have provided with DENALICTP as the instance name, then select the install root folder and click on Next button as shown in the screen below.

A summary will be presented showing the Disk Space available in the server and the required space for the installation of Denali CTP3 as shown in the screen below. Click on Next to proceed further.

A list of services and the Start-up type of each service (Manual or Automatic) will be shown based on the selection made as shown in the screen below. Click on Next to proceed further.

Now comes the Server configuration. We can use either Windows Authentication or the Mixed Authentication modes as per the requirement. Then provide a valid Password. At the bottom, we can see a button Add Current User, click on that button to add the current user to the list as shown in the screen below. Click on Next to proceed further.

Next is the Error Reporting option. If we need to report the error to Microsoft then we can initiate it by selecting the Check box as shown in the screen below. Click on Next to proceed further.

Now Configuration Rules will be triggered and provide the results as shown in the screen below. Click on Next to proceed further.

A summary of the list of service and applications that are going to be installed is presented as shown in the screen below. Click onINSTALL to proceed further and start the installation.

This will start the installation and the installer will do step-by-step process of installing all the necessary components as shown in the screen below.

Once the installation is completed we can see the result and the status as shown in the screen below. Click on Close to complete the process.

Summary

So in this first part of the series of articles. We have seen what SQL Server Reporting Services are and how to install it on a development environment to start designing the report. In our next article, we will see how to design a report using the Design wizard.

Getting Started with SSRS - Designing a report using Report Wizard

Pubs database is Needed for this exercise, You can Download the pubs database Here

Introduction

This article frames a guideline on how to make the use of the Report Wizard and create a simple report using the Business Intelligence Studio. A Report Wizard is the simplest form for creating a readymade report by simply selecting the data that should be displayed and by selecting the inbuilt templates available as per the requirement. Let us now see how to create the reports using the Report Wizard Template of the Business Intelligence Studio.

Steps

The first step towards our task is to open Business Intelligence Studio. To open it, navigate to Programs >>  Microsoft SQL Server 2008 as per the installed version and then select Business Intelligence Development Studio as shown in the screen below.

This will open Visual Studio. Visual Studio is a Business Intelligence Development Studio for SQL Server and looks as shown in the screen below.

Next, select File >> New >> Project to create a new project. We can even use the shortcut Control + Shift + N directly as shown in the screen below.

Once a new project is selected, a list of templates will be available as shown in the screen below.

As we are in Business Intelligence Studio, the list of template will not have the normal templates (C#, VB.Net, Office etc).

Now the next step is to create a report using the Report Wizard. To do this, select the Report Server Project Wizard as shown in the screen below.

Once we select the Report Wizard and provide a valid name to the project as shown in the screen above, click on OK to create the project. Once the project is created, we can see a screen as shown below.

This is the start-up screen. To stave off the start up screen in the future, we can select the option “Don’t show this page again”. Once we are done with the above changes, click on the Next button to proceed further.

Now we can see a new window requesting for the data source input details. In this input request, we can either configure a shared data source or create a new data source as per the requirement. In our example, we will create a new data source as shown in the screen below.

Next, click on the Edit button and edit the connection string to connect to the database from which the data has to be retrieved to present in the reports. On clicking Edit button a window will open as shown in the screen below.

Once we have provided the above details (It may change as per your system database), click on Test Connection to test if the connection is successful. Once the connection is successful, click on OK and we can see the window as shown in the screen below.

Clicking on Next button will move to the next step in the wizard, which is the Query Builder. With this Query builder, we can build our query based on the requirement of the report. We can use this screen in the same manner as we write a query in a Query Analyzer as shown in the screen below.

To make use of the query builder click on the Query Builder button. It will open a new window as shown in the screen below.

Now, a query has to be written to pull up the record as shown in the screen below. Once the query is written, click on the Executebutton. This will give complete details of the data for our reference to finalize the query. We can do as many executes as required to get a final data as per the business requirement. This is shown in the screen below. Once done, click on OK to close this window and navigate back to the wizard.

This will load the query that we selected back in the wizard as shown in the screen below. Now click on Next to proceed further.

Now we can see an option to select the format in which the data can be displayed. The Wizard has two options (Tabular and Matrix) that can be used based on the requirement.

Once we select the required format and click on the Next button, we will get a window to select fields that has to be displayed. Here we have more control over the data display, such as; we can select the data that should be displayed at the header or to group the data in a particular format as shown in the screen below.

Once selected, click on the Next button to move to the next screen. The next screen allows selecting the layout of the report as shown in the screen below.

Selecting Stepped option and clicking on the Next button will open a new window to select the style. This will allow us to display the report with a good look and feel. The report wizard displays a list of styles available as shown in the screen below. Select the required style and click on Finish button to complete the wizard.

A summary of the options selected will be presented with the data source as shown in the screen below. Click on Finish button to complete the process.

Clicking on Finish button will open the report in the designer view of the Business Intelligence Development Studio as shown in the screen below.

Now Press F5 or hit the Play button from the tool bar to start building and executing the report. Once the process is started and we can see the report generation in progress as shown in the screen above.

Once the report generation is completed, we can see report in a nice format as shown in the screen below, which is also our expected output.

Summary

Therefore, in this article we have seen how to create a report in SQL Server Reporting Services using Business Intelligence Development Studio Report Wizard template.