Microsoft SQL Server 2008 Business Intelligence Development and Maintenance: Developing SSRS Reports

  • 4/15/2009

Case Scenario: Building Reports for the AdventureWorks Intranet

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?