The Ad-hoc Demo
Now that you have a good high-level overview of the RS ad-hoc model, it's worth walking through the implementation steps. Suppose that you are a Business Intelligence architect with a fictitious company called Adventure Works. The Adventure Works management has decided to empower business users by giving them an option to generate ad-hoc sales reports. To meet this requirement, you will use Microsoft Reporting Services 2005.
Creating the Report Model Project
You start by creating a new report model project in the Business Intelligence Development Studio (BI Studio) which is a lightweight shell of Visual Studio.NET. BI Studio comes bundled with SQL Server 2005 and it doesn't require a separate license. Using BI Studio, you can design and manage BI (Reporting Services, Analysis Services, and Integration Services) projects, as shown in Figure 3
. You'll get the greatest benefit by working through the process to build the sample, but you can also download the sample project
to get the code.
|Figure 3. BI Studio's New Project Screen: BI Studio lets you design and manage BI projects.|
Here's how to get started:
Setting up the Data Source
- Launch Business Intelligence Development Studio and create a new project.
- From the New Project dialog select the Report Model Project template.
- Name the project ReportBuilderDemo and click OK.
- BI Studio generates the Report Model project template. The Solution Explorer shows Data Sources, Data Source Views, and Report Models folders that correspond to the ad-hoc model layers depicted in Figure 2.
Next, set up a data source that points to the AdventureWorks sample database.
- Right click on the Data Sources folder in the Solution Explorer pane and choose "Add New Data Source." The Data Source Wizard starts.
|Figure 4. The Connection Manager. You use the Connection Manager to create a new connection to a data source.|
- Click Next to advance to the "Select how to define a connection step." Because you don't yet have any connections defined, you need to create a new connection. Click on the New button to start the Connection Manager (see Figure 4).
- Expand the Provider dropdown and select the SQLClient Data Provider from the .NET Providers section. Because the Report Server is implemented in .NET managed code the SqlClient provider gives you the best performance when connecting to SQL Server-based data sources.
- Fill in the rest of the fields to finalize the data source setup. Click OK to return to the Data Source Wizard.
- Click Finish and name your data source Adventure Works.