Report Builder is a great tool for authoring basic reports. But when we created the second report in the "Create a Report by Using the Table Report Layout" section in the main article, you probably thought that although you didn't need to understand sophisticated database concepts to generate the report, there must be an easier way to get the information in a format that better suits your needs. Let's revisit that report model and make a few improvements. Start by opening the Report Model Project in Visual Studio 2005.
Organize entities in folders . On the Report Model output window, you'll see that the root Model node has many entities. A quick win is to organize some of these entities in folders to make locating them easier.
To create a new folder, select the root Model node, right-click in the model-design area pane (see Figure A to locate the model-design area), and select New, Folder. Type Product Details as the folder name. To add entities to the Product Details folder, select the root Model node again and scroll to the bottom of the tree until you see the Product Details folder. In the detail list to the right of the tree, drag all the entities that begin with the word Product (except the Product entity) into the Product Details folder. Continue this process to create additional folders to organize the remaining entities.
Customize the Sales Person entity . Remember that we used the AdventureWorks sample relational database to create the data source. In AdventureWorks, all salespeople are employees. It would be nice to also see Employee properties when we look at the Sales Person entity. In addition, many of the details for an Employee (e.g., his or her name) are located in the Contact entity . To make the Full Name property part of the Sales Person entity, we need to implement two customizations:
- In the Report Model output window, click the Employee entity. Right-click in the model design area pane and select New, Expression. Set the expression value to (First Name & " ") & Last Name (as Figure B shows), set Nullable property to True, and rename the expression to Full Name.
- In the Report Model output window, click the Sales Person entity. In the Properties pane, find and expand the Inheritance property. Then, click the InheritsFrom attribute and select Employee from the drop-down box. Click the Binding attribute (below the InheritsFrom property) and select FK_SalesPerson_Employee_SalesPersonID from the drop-down box—this is the relationship you'll use to join the Sales Person and Employee entities.
Reformat the Commission Percentage attribute . In the Report Model output window click the Sales Person entity, select the # Commission Pct attribute, and change the Format property to P (for percentage) as Figure A shows. Set the DefaultAggregateAttribute property to None because commission is non-additive (i.e., we want the commission value rather than an aggregate to be the default value). Optionally, go ahead and change the Format property of the Sales attributes (such as Sales YTD and Sales Quota) to C (for currency) to display these values as currency. You can also adjust the Width property for these attributes to automatically accommodate large dollar amounts.
Set default detail attributes for an entity . If you examine an entity in the Report Model output window, you'll see several properties related to attributes (e.g., DefaultDetailAttributes, IdentifyingAttributes, SortAttributes) in the Properties pane. These properties are used when the system generates infinite drill-through reports and when a user drags an entity (rather than entity fields) onto the report layout window in the Report Builder client. To modify the Details attribute associated with the Sales Person entity, click the Sales Person entity, select the DefaultDetailAttributes property, and click the button that appears to the right of the property. Replace the National ID Number member with Full Name. Move Full Name to the top of the list (above Commission Pct).
Author the report . We could do more, but let's take a look at the changes we just made. To review your changes, you need to redeploy the report model (see "Phase 2: Deploy the Report Model to a Report Server" in the main article) and launch Report Builder.
Select File, New from the Report Builder toolbar. Select the Example 2 data source and a table report layout. Locate and drag the Sales Person entity to the report layout window. You'll notice that the Full Name and Commission Pct fields—the default detail attributes we specified—are automatically displayed. Next, drag the Total Sales Quota and Total Sales YTD fields (from the Fields pane in Figure B) to the right of Commission Pct. Refer to the "Create a Report by Using the Table Report Layout" section in the main article for an explanation of how to add a drop-down box to filter the report by Sales Territory. Overall, you should notice that these model changes provide a better authoring experience.
