Building an enterprise wide corporate reporting solution. Introducing SQL Server 2005 Reporting Services, страница 2

Task № 2.2 action scenario

After completion:  You will get a modern versatile category based product catalogue. This catalogue will be equipped with hypertext link to product/spare part procurement history. Intended for supply department everyday use.

1.  Add a new report to the existing project

Tip:  Close the previously created report design sheet in order not to mix up with the new one.

·  Create a shared datasource, connect it to OLTP database. Choose AdventureWorks named database from drop-down listbox. Shared datasource is available on the Data panel for any other report within the project scope.

·  Add a new report and name it “Products”

2.  Create a new Dataset

·  Switch to Data panel

·  Choose New Dataset action. Call the new dataset dataProducts

·  Activate the dataset and click   button switch to visual  query designer

Tip:  You will use this button to switch between query modes in further tasks as well. Please mind this icon.

·  Create a query for products, their categories and subcategories. Add necessary tables by clicking  button. Enter Aliases for several Name fields as shown below

3.  Create multi-level tabbed report

·  Go to Layout pane and add a Table component from the toolbox. In table properties point to the dataset

Tip: To select the whole Table component click on the upper-left edge. Then activate the Properties Pane and look for the DataSetName section

·  Create a grouping by Category. Use CategoryKey instead of its Name. Grouping by numeric values is done much faster that by string values. Right-click mouse to activate underlying context menu

·  Put the database field into the Table – use drag and drop from the Datasets panel

·  Do the same job for subcategories. Place the Product Name field into the details section of the Table. Use properties to perform font style formatting. Adjust padding property to add some multi level appearance

·  Save and preview the report

4.  Add expand and collapse by group capabilities

·  Open Subcategory group properties and set Hidden – True, Toggle Item – CatName

Tip:  Select the whole row for Subcategories (as you do it in Excel) and activate Properties pane. Click the mouse over the left edge of the row

·  Do the same actions for the details band. Open Details group properties and set Hidden – True, Toggle Item – SubcatName