Reporting

=Reporting Tools= Thursday, April 3

**Topic overview:**
In previous classes we explored techniques for creating robust, comprehensive, and high-quality analytical data repositories (data warehouses and data marts). In this class we will explore //reporting tools//, which help people turn the data stored in these repositories into useful information and knowledge.

//Reporting tools// describes a broad range of software packages that simplify the process of retrieving, analyzing, and presenting information from data repositories. In SQL Server 2005, these tools are called //Reporting Services//. We will use Reporting Services in class and in assignment #2 to explore large, complex data sets and to produce useful and visually appealing reports.

Specific topics that we will cover include:
 * Understanding the role of reporting tools in a BI program
 * Overview of reporting tool capabilities
 * Creating reports with Sql Server 2005 Reporting Services

**Preparation for class:**
Prior to class, you should have successfully installed and configured the SQL Server 2005 client tools and completed at least the first Reporting Services tutorial. SQL Server 2005 installation instructions are available here.

Successful installation in this case is determined by the fact that you are able to:
 * Run the SQL Server Management Studio and SQL Server Business Intelligence Development Studio clients on your machine
 * Connect to the AdventureWorks_BITT database on bmonroe-srv.tepper.cmu.edu using the userid and password assigned to you.
 * Submit queries from SQL Server Management Studio to the AdventureWorks database running on your server
 * Complete the first Reporting Services tutorial outlined later in this paper.

Once you have completed the installation and configuration of SQL Server 2005 please spend some time familiarizing yourself with SQL Server Management Studio and using it to access, explore, and query the AdventureWorks_BITT sample database available on bmonroe-srv.tepper.cmu.edu.

The AdventureWorks Report Samples provided below should allow you to connect to bmonroe-srv as ‘test-student’ and run a variety of reports. These sample reports are provided by Microsoft - you will likely find them very helpful for homework #2. (If you have user problems, click the "credentials" tab on the datasource properties - ie right click on SharedDataSource - AdventureWorks, select open and enter the credentials again. The datasource otherwise should be configured to directly connect to the server database)

Documentation on using SQL Server Management Studio is available in the SQL Server Books Online that is installed locally on your machine when you install SQL Server 2005. There is also a good overview and tutorial available on Microsoft’s web site at:
 * [|http://msdn2.microsoft.com/en-us/library/ms167593(SQL.90).aspx]

Information about other SQL Server 2005 tools and clients can be found at:
 * [|http://msdn2.microsoft.com/en-us/library/ms170486(SQL.90).aspx]

We will spend a significant amount of time introducing and working with Reporting Services in this class. To prepare for this, please read the following Reporting Services articles available at Microsoft's Developer Network site. Please feel free to investigate further as your interest guides you or to solve specific problems you run into with the tutorial(s) or homework.
 * Reporting Services Analyst Info Center:
 * [|http://msdn2.microsoft.com/en-us/library/ms157242(SQL.90).aspx]
 * Introducing Reporting Services:
 * http://msdn2.microsoft.com/en-us/library/ms155786.aspx
 * Reporting Services Scenarios:
 * http://msdn2.microsoft.com/en-us/library/ms159218.aspx
 * Creating, Managing, and Delivering Reports Overview
 * http://msdn2.microsoft.com/en-us/library/ms159090.aspx
 * Reporting Services Component Overview:
 * http://msdn2.microsoft.com/en-us/library/ms159218.aspx
 * Reporting Services Concepts
 * http://msdn2.microsoft.com/en-us/library/ms155938.aspx
 * Reporting Services Features
 * http://msdn2.microsoft.com/en-us/library/ms155938.aspx

Reporting Services Tutorial:
 * [|http://msdn2.microsoft.com/en-us/library/ms170246(SQL.90).aspx]

Please try to complete the first tutorial ‘Creating a basic report’ before class on Thursday 4/3. If you are able to get through the first tutorial and have time, please continue on to the second tutorial as well – ‘Adding Grouping, Sorting, and Formatting to a Report’. Completing the second tutorial is optional but will give you more familiarity with the tool and help greatly with homework #2. The further you can get before class the more productive our time in class will be.


 * Please remember that when doing the tutorials you should connect to the AdventureWorks_BITT database on bmonroe-srv.tepper.cmu.edu rather than the database on your local machine (which probably does not exist)**! You can connect either with the test-student userid and password (listed in slides) or with the userid and password assigned uniquely to you (andrewid and password listed in Gradebook). Note that you may need to select the 'Credentials' tab of the Data Source properties dialog box to specify that you want to use 'SQL Server Authentication' and input your userid and password. See the screens below for a visual of these instructions