CHAPTER 11 Extending and Deploying SSRS Reports

chapter summary

  • Use report models to help users create their own reports without having to learn the complexity of the database schema or the SQL language.
  • Use BIDS to create and personalize relational report models.
  • Use query parameters to filter information retrieved from the database, and use report parameters to interact with end users. Create new datasets to provide a list of alternative values.
  • Use expressions to dynamically change report formatting.
  • Enhance expressions by using custom code, either embedded in the report or from a custom assembly.
  • Manage the report server from your code by using the SSRS Web service.
  • Include SSRS reports in your applications by using Visual Studio report viewer controls.

Lesson 1: Assigning Parameters Within Reports

1. What is the main purpose of a report parameter?

The main purpose of a report parameter is to add interactivity to your reports, letting users change the report behavior based on options they select.

2. What is the main purpose of a query parameter?

The main purpose of a query parameter is to filter data in the data source.

3. you want your users to select a parameter from a list of values in a list box. How should you configure the parameter?

you should create a data source that contains the possible values and then bind the data source to the parameter.

Lesson 2: Using Expressions to Perform Advanced Report Item Formatting

1. What is the main benefit of using embedded code in a report?

The main benefit of using embedded code in a report is that the code you write at the report level can be reused in any expression in the report.

2. What programming language would you use to create embedded functions in SSRS?

An SSRS report supports only visual Basic .nET embedded code.

3. How do you reference an embedded function in a report expression?

use the Code prefix and the name of the function to reference an embedded function in a report expression.

Lesson 3: Deploying New Reports and Changes

1. How can you manage reports from your application if the report server is deployed in SharePoint integrated mode?

use the ReportService2006 endpoint of the SSRS Web service if your report server is deployed in SharePoint integrated mode.

2. In which processing mode of a report viewer control can you use the full functionality of your report server?

you should use the remote processing mode to use the full functionality of your report server.

Lesson 4: Using Reports in Your Code

1. How can you manage reports from your application if the report server is deployed in SharePoint integrated mode?

use the ReportService2006 endpoint of the SSRS Web service if your report server is deployed in SharePoint integrated mode.

2. In which processing mode of a report viewer control can you use the full functionality of your report server?

you should use the remote processing mode to use the full functionality of your report server.

Creating a Reporting Services Infrastructure


Case scenario

 

You just successfully installed SSRS 2008. You also created two shared data sources: one configured to retrieve data from the AdventureWorks relational database and the other configured to retrieve information from a marketing/sales data mart stored in an online analytical processing (OLAP) database. The data mart is populated once a week. The schemas of the relational and OLAP databases are the same as the sample databases provided by SQL Server 2008. You will be the main developer of a set of reports that will be used in the Adventure-Works portal, and you need to address the following requirements:


  1. End users want the ability to create their own reports. The users are knowledge workers who have Excel expertise but no database experience. What is the best way to create the reports, without giving end users direct access to the database? How will you build the infrastructure?
  2. In the previous version of the reports, users had a set of reports that were identical to each other except that each report grouped information at different levels of the organization. Users still want the flexibility to look at the information grouped in different ways, but you want to build a single report rather than multiple reports. Given these requirements, what is the best way to create the new report?

Answers

 

1. You can configure report models that will let users create their own reports. You need to create two separate models, one for the relational engine and another for the SSAS database. The relational database model should be created and configured in BIDS, and the OLAP data source should be created from SSMS or Report Manager.


2. You could create a parameterized report that prompts the user for the desired level of aggregation and then dynamically creates the group by statement. Alternatively, you could use the same query for all the aggregation levels, use a table or matrix report item, and hide the grouping level based on the user selection. The main advantage of the first approach is that it pulls only the required data when the report is executed. The main advantage of the second option is that it allows the reuse of the cache if the report uses report caching.