CHAPTER 10 Developing SSRS Reports

chapter summary

  • Use the Report Server Project Wizard to create a new project and automatically start the Report Wizard to generate the first SSRS report. Then, use the Report Wizard to generate other reports. Use the Report Designer to enhance your reports and increase their functionality.
  • Drag new items onto the Report Designer Design tab to add new report items, and use the Properties window to customize existing items.
  • Use datasets to include more than one stream of data in your report.
  • Hide columns and use the toggle properties to add interactivity to your report. Reduce the amount of information offered to the user at the beginning of the report, and let users expand and investigate areas they are interested in.
  • Use groupings in Tablix data regions to summarize information by categories. You can also use multiple data regions.

Lesson 1: Creating SSRS Projects and Reports in BIDS

1. you want to include an image in a report. How do you display the Image Properties dialog box?

When you drag an image item from the Toolbox window to the Report Designer,

the Image Properties dialog box automatically opens.

2. you want to confi gure an amount to display the value in a currency format. Which property do you use?

To confi gure an amount to display a value in a currency format, select the report

item, and then set the format property to C or c.

3. What are data regions?

Data regions are report items that display repeated rows of summarized information

from datasets.

4. you want to generate a report that is formatted as a chart. Can you use the Report Wizard to create such a report?

no, the Report Wizard lets you create only tabular and matrix reports. you must

create the chart report directly by using the Report Designer.

5. you want to use BIDS to deploy a report to a different server than the one you chose in the Report Wizard. How can you change the server uRL?

you can right-click the project in Solution Explorer and then change the Target-Server uRL property.

6. Which rendering formats are affected by the PageSize properties?

Because only the Adobe PDf file, Word, and Image rendering extensions use

physical page breaks, they are the only formats that are affected by the PageSize

properties.

Lesson 2: Creating a Dataset from a Data Source

1. Can you use a stored procedure to provide data to an SSRS report?

yes, you can use a stored procedure to provide data to an SSRS report by con- fi guring the dataset to use a stored procedure command type. However, your stored procedure should return only a single result set. If it returns multiple result sets, only the fi rst one is used for the report dataset.

2. you want to use a perspective in an MDX query. How do you select the perspective?

use the Cube Selector in the MDX Query Designer to select a perspective.

3. Can you use data mining models in SSRS?

yes, you can use the DMX Designer to create data mining queries for SSRS reports. However, do not forget to fl atten the result set returned by the DMX query.

Lesson 3: Working with Advanced Report Object Properties

1. you want your report to display a hyperlink that will take users to your intranet. How do you configure such a hyperlink?

Create a text box item, set the action to Go To uRL, and then configure the uRL.

2. you want a report to display Sales by Category, SubCategory, and Product. You want users to see only summarized information initially but to be able to display the details as necessary. How would you create the report?

Group the Sales information by Category, SubCategory, and Product. Hide the SubCategory group and set the visibility to toggle based on the Category item. Hide the Product category group and set the visibility to toggle based on the SubCategory item.

3. you want to create an Excel interactive report from SSRS. In SSRS, can you create the same interactive experience in Excel that you would have on the Web?

no, you cannot create the same experience with SSRS. you can, however, use Excel to create such an experience.

Lesson 4: Applying Dataset Filters and Groups

1. What is the main difference between a Matrix report item and a Table report item?

The main difference between a Matrix and a Table report item is in the initial template. Actually, both report items are just templates for the Tablix data region.

2. When you do not use report caching, is it better to use parameters to filter information in the query or to use filters in the dataset?

from a performance perspective, it is better to use parameters because they let SSRS pull filtered data from the data source. In contrast, when you use filters, the queries retrieve all data and then filter the information in an additional step.

3. How do you configure a running aggregate in SSRS?

you can use the RunningValue function to configure a running aggregate.


Building Reports for the AdventureWorks Intranet

 

Case scenario

 

You have just successfully installed SSRS 2008. You have also created two shared data sources: one configured to retrieve data from the AdventureWorks relational database and the other to retrieve information from a sales and marketing data mart stored in an 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 are the main developer of a set of reports that will be used in the AdventureWorks portal, and you need to handle the following requirements:

  1. Your end users want a report that gives them near real-time information about sales by Product Category, Product Subcategory, and Product model. The report should show only the past week's sales and should have only three columns: Name, Quantity, and Amount. Users also want the ability to drill through the report from summary information to greater detail. You do not want to use the Report Wizard. Given these requirements, what is the best way to create the report?

  2. Your end users want a pivot table report that has Categories, Subcategories, and Models as columns and Year, Quarter, and Month as rows. The cell data should be filled with sales amount information. The information does not need to be real time. Given these requirements, what is the best way to create the report?

Answers

 

1. You can add a new report to the SSRS solution to satisfy this user requirement. Create a dataset that uses the AdventureWorks relational database. In the dataset's query, filter the information to retrieve only the last week’s sales by product category, subcategory, and model. Use a Table data region and create two additional groupings, one by category and another by subcategory. Set the initial visibility status of the Subcategory and Detail rows to hidden, the toggle property of the Subcategory grouping to change based on Category, and the toggle property of the Detail grouping to change based on Subcategory.


2. For this requirement, you can use the Report Wizard to create the report. On the Data Source page, select the multidimensional database, and then use the MDX Query Builder to create the MDX query. In the Data pane, drag the Product Model Categories hierarchy, the Date.Calendar hierarchy, and the SalesAmount measure onto the Results pane. Remove the Calendar Semester and Calendar data levels. Select a matrix report, and then assign the date-related information to columns and the product category information to the rows. Last, assign the amount as detail information.