Creating Reports with the Report Builder

Now that a Report Model is in place on the server, users can create reports based on that model using the Report Builder. Three types of report layouts are available in the Report Builder: the table report, the matrix report, and the chart. We look at brief examples of all three reports but, first, let’s go over some of the basics.

Report Builder Basics

The Report Builder is a special type of Windows program known as a ClickOnce application, which is installed on your computer by following a link or clicking a button on a web form. The application is launched in the same way.

Launching the Report Builder Application

You launch the Report Builder by bringing up Report Manager in a browser, and then clicking the Report Builder button in the toolbar. You can also launch the Report Builder without first going to the Report Manager. This is done using the following URL:

 http://{ReportServerJ/ReportServer/ReportBuilder/ReportBuilder.application

where {ReportServer} is the name of your report server.

The Microsoft Report Builder launches and begins creating a new report. The Task pane is displayed on the right side of the screen. You must select a data source for the report. See Figure D-9. Instead of basing your report on the entire Report Model, you can select a perspective from within the model. As you learned earlier, a perspective is a subset of the information in the model. Usually, a perspective coincides with a particular job or work area within an organization.

Figure D-9: Selecting a source of data from a Report Model with four perspectives

If a plus sign is to the left of the model, then the model contains one or more perspectives. Click the plus sign to view the perspectives. If you select one of these perspectives as the data source for your report, only the entities in that perspective are available to your report. Because perspectives reduce the number of entities you have to look through to find the data you need on your report, it is usually a good idea to choose a perspective rather than using the entire Report Model.

You must also select a report layout. The Task pane shows the three types of report layouts available in Report Builder. The table report creates a report with rows and columns. This is the most familiar report layout. In a table report, the columns are predefined by the report layout and we will not know the number of rows until the data is selected at run time.

The matrix report creates a report containing what is known as either a crosstab or a pivot table. We do not know how many columns or rows will be in the matrix in advance, because it uses data selected at run time to define both. The matrix report can be somewhat confusing the first time you encounter it, but an example usually helps. If you are fuzzy on how a matrix works, be sure to check out the sample matrix report.

The chart creates a business graphic from the data. This can be a line graph, a bar chart, or a pie chart, among other things. While you can create a basic chart with a few mouse clicks, the chart report itself has a large number of options that enable you to format the chart just the way you want it.

If you are creating a new report, select the Report Model or the perspective that should serve as the data source along with the report layout and click OK. If you want to edit an existing report, click the Open button in the toolbar. You can then navigate the Report Server folder structure to find the Report Builder report you want to edit. You cannot use the Report Builder to edit reports that were created or edited using the Report Designer in Visual Studio 2005 or the Business Intelligence Development Studio.

Entities, Roles, and Fields

Reports are created in the Report Builder using entities, roles, and fields. Entities are simply the objects or processes our data know something about. Employees, Customers, and Deliveries are all examples of entities in the Galactic Report Model used in these examples. A single report may contain information from a single entity or from several related entities. Entities can be grouped together in entity folders within the Report Model or perspective to help keep things organized.

Roles show us how one entity relates to another entity. For example, a delivery is related to a customer through its role as a delivery of a package for that customer. An employee is related to a customer through the employee’s role as an account representative for that customer. A delivery hub is related to a delivery through its role as a stop on a delivery route.

Roles enable us to show information from multiple entities together on a single report in a meaningful manner. This may seem a bit confusing as you read about it but, remember, the roles are already defined for you by the Report Model. If the model has been created properly, you should find they are natural to the way you think about your business processes. Information from different entities should combine on your reports just as you expect, without having to get caught up in the technical structure behind the relationships.

The information about the entities is stored in fields. A field is simply one bit of information: a first name, an invoice number, a hiring date. Fields are what we place on our reports to spit out these bits of information.

The Entities List

Once a Report Model is selected for your data source, or an existing report is chosen for editing, the main Report Builder opens. When creating a new report with the Accounting Information perspective chosen as the source, the Report Builder appears similar to Figure D-10. Let’s take a look at each of the windows that make up this screen.

Figure D-10: The Report Builder screen

The Entities list, in the upper-left corner, shows the entities and entity folders in the selected Report Model or perspective. All the data in a Report Builder report comes from the entities displayed in this window. Once an entity has been selected and placed on the report, the Entities list shows that entity along with its roles.

The Fields List

The Fields list, in the lower-left corner, shows the fields available for the selected entity. Some of these fields contain information coming directly from the database, while others contain information calculated by the report. The icon to the left of the field identifies the type of data being stored in the field. A pound sign (#) indicates a field contains numeric data. A small letter a indicates a field containing alphanumeric data. A check box indicates a field that contains yes or no, true or false data. A calendar indicates a date and time. A grouping of three yellow boxes indicates a calculated field that combines values from a number of items into one value, for example, the sum of all invoice amounts for a customer.

You can create your own calculated fields by clicking the new field button at the upper-right corner of the Fields list. This displays the Define Formula dialog box shown in Figure D-11. Use this dialog box to define your calculated field. You can create expressions by dragging existing fields on to the Formula area. The fields values can be combined using the arithmetic and string operator buttons below the Formula area. You can also use the Functions tab to reference a large number of functions that can be used in your expressions. Once you click OK to create the calculated field, it is displayed in the Fields list of the Report Builder.

Figure D-11: The Define Formula dialog box

Clickthrough Reports

As discussed earlier, entities are related through roles in our data model. Sometimes it can be helpful, as you are analyzing your data, to follow these role relationships through your data. The Report Builder enables you to do this by using clickthrough reports.

Clickthrough reports are automatically created when you click on a role relationship to move from one entity to another. The clickthrough reports are defined on the fly according to the data in the model for the entity being reported on. Each clickthrough report can lead to other clickthrough reports. Using clickthrough reports, you can continue to follow your data wherever it may lead to perform in-depth analysis.

Creating a Table Layout Report

We begin by creating a table layout report showing customers and their deliveries. We create the basic report, modify the report to show monthly information, and then add additional formatting.

Creating the Basic Table Layout Report

We start by creating the basic report.

1. Open a browser and bring up the Report Manager using the following URL:

    http://{ReportServer}/Reports

   where {ReportServer} is the name of your Reporting Services Report Server.

2. Click the Report Builder button in the Report Manager toolbar.

3. Click the appropriate responses to any security warning dialog boxes that may appear to allow the Report Builder application to be downloaded and installed.

4. Make sure the Galactic Report Model is selected as a source of data for your report. Also, make sure the Table (columnar) Report Layout radio button is selected. Click OK.

5. Double-click the Click to Add Title text box on the report layout. Enter Customer Deliveries.

6. Click Customer in the Entities list. Drag Customer and drop it on the Drag and Drop Column Fields area of the report layout. A grouping is created for Customer with three columns: Name, Customer Number, and Billing Contact.

7. The string of Xs in each column is the template for the data. It shows you how wide and in what format the data will appear. The data in the Customer Number column is much narrower than the heading. To save space, let’s make this a two-line heading. Position the mouse pointer so you can drag the Customer Number column narrower as shown in Figure D-12. Drag the column just narrow enough so the Customer Number heading wraps to two lines.

   
   Figure D-12: Narrowing a column in the Report Builder

8. Now, drag the headings row tall enough to accommodate the two-line heading.

9. The Customer Number is helpful in our report, but Billing Contact is not necessary. Right-click the Billing Contact column and select Delete from the Context menu. The Billing Contact column will be removed from the report.

10. Next, you want to use the date of pickup to identify each delivery. If you are familiar with the Report Model, you can use the Entities list and the Fields list to find the appropriate field. If you are not familiar with the Report Model, you may need some assistance. Click the magnifying glass in the upper-right corner of the Explorer pane to get that assistance. The Search dialog appears.

11. Enter delivery date for the Search text. Select Entire Data Source to search the entire Report Model rather than the current item only. (The current item would be the Customer entity, because that was the last entity placed on the report.) Click Find.

12. You see a list of all fields in the Report Model that contain “delivery date.” Note, the search is not case-sensitive. Scroll down until you find the Delivery Date Time field with a location of Customer/Deliveries in the Search Result. (Make sure you select the Delivery Date Time field from the correct location!) Select this item and click OK. You jump to the Delivery Date Time field in the Fields list.

13. Click the Delivery Date Time field and drag it onto the report layout, over the top of the Customer Number column. You see a blue insert bar as shown in Figure D-13. Drop the field to create a new column at the location of the insert bar.

    
    Figure D-13: Adding a column to the report

14. There is a template for the content of the Delivery Date Time column. It shows the date portion will be displayed, but the time portion will not. With a package delivery, both the date and time are important. To change this formatting, rightclick on the Delivery Date Time and select Format from the Context menu. The Format dialog box appears.

15. Select the Number tab. (Why this is called Number when it contains other formatting options as well is beyond me.)

16. You can see a number of available formats for the Delivery Date Time. Select a format that contains both the date and the time. Click OK to return to the report layout.

17. Expand the Delivery Date Time column, so the template and the heading do not wrap.

18. Now let’s add the cost of each delivery. The cost is in the Service Type entity, because cost is based on the type of service used. In the Entities list, click Service Type. The Fields list now shows the fields for the Service Type entity. Select Total Cost and drag-and-drop it on the report layout. Note, as you drag the Total Cost field over the existing layout, you could put it in between any of the existing columns. For this layout, drop it on the right side. Your report layout should appear as shown in Figure D-14.

    
    Figure D-14: The table report layout

19. Let’s preview the report and see what we have so far. Click Run Report in the Report Builder toolbar. The report should appear as shown in Figure D-15.

    
    Figure D-15: The table report preview

Modify the Report to Show Monthly Information

Let’s add some basic formatting to the report and modify our grouping, so the report shows monthly information.

1. You can see the Total Cost column has four decimal places, which is not how we usually like to see dollar amounts. Click Design Report in the Report Builder toolbar to return to the report layout.

2. Right-click the template in the Cost column on the report layout. Take a moment to look at the options available in the Context menu shown in Figure D-16.

   • Format   Displays the Format dialog to modify the colors, borders, and formatting of the column content.

   • Edit Formula   Displays the Define Formula dialog box to modify the content of the column.

   • Delete   Removes this column from the report.

   • Show Subtotal   Unchecking this option removes all the subtotaling from the report.

   
   Figure D-16: The column Context menu

3. Select Format from the Context menu. The Format dialog box appears.

4. Select the format that includes the dollar sign and click OK.

5. Repeat this for each field in the Total Cost column. You can use SHIFT to select and change the format of both remaining fields at the same time.

6. Looking at the cost for every delivery is a bit overwhelming for the user. Instead, let’s look at the cost for each month. To do this, you need a field that contains the year and month from the Delivery Date Time field. Select Delivery in the Entities list, and then click the New Field button above the Fields list. The Define Formula dialog box appears.

7. Enter Delivery Month for Field Name.

8. Select the Functions tab and expand the Conversion entry. Double-click the TEXT function. This function converts a numeric or date value to text.

9. Select the Fields tab. Expand the Delivery Date Time entry in the Fields list. Double-click the Delivery Date Time Year field, which is the third entry under Delivery Date Time. Notice this field was placed inside the TEXT function. The formula takes the year portion of the Delivery Date Time and converts it from a number to text.

10. Press END to move the cursor to the end of the formula. Click the ampersand button.

11. Select the Functions tab and expand the Text entry. (This is the Text entry with the folder icon, not the TEXT entry with the fx icon.) Double-click the RIGHT function. This function returns the rightmost characters of a string.

12. Replace the highlighted text with " " and click the ampersand button.

13. Double-click the TEXT function. The TEXT function is nested inside the RIGHT function. Functions may be nested up to seven levels deep.

14. Select the Fields tab. Double-click the Delivery Date Time Month field. This is the second entry under Delivery Date Time. This field is placed inside of the TEXT function.

15. Replace the yellow-highlighted word “length” with 2. This second part of the formula appends the month number, as a string, after the year. The RIGHT function adds a space before single-digit month numbers, so they sort properly with the two-digit month numbers. When complete, your formula should match the formula shown in Figure D-17.

    
    Figure D-17: The Delivery Month field formula

16. Click OK to exit the Define Formula dialog box. The new Delivery Month calculated field appears at the top of the Fields list.

17. Drag the Delivery Month field from the Fields list and drop it to the left of the Delivery Date Time field in the report layout.

18. Right-click the Delivery Date Time column in the report layout and select Delete from the Context menu. Now the Total Cost column is part of the Delivery Month group. The report layout appears as shown in Figure D-18.

    
    Figure D-18: The table report layout with monthly information

19. Click Run Report to preview the report.

Add More Formatting

Now, let’s add more formatting, filtering, and sorting.

1. Note, there are entries from both 2004 and 2005. Let’s look at only 2005 deliveries. Click Design Report in the Report Builder toolbar to return to the report layout.

2. Click Filter in the Report Builder toolbar. The Filter Data dialog box appears.

3. Select Delivery in the Entities list of the Filter Data dialog box, and then expand Delivery Date Time in the Fields list. Double-click Delivery Date Time Year in the Fields list of the Filter Data dialog box. Enter 2005 for the Equals value. (See Figure D-19.)

   
   Figure D-19: The Filter Data dialog box

4. While we are filtering, let’s also limit the report to only those deliveries made to the city of Axelburg. Double-click the Delivery City field in the Fields list of the Filter Data dialog box.

5. Notice how the Report Builder creates a drop-down list of all unique values in the Delivery City field. Select Axelburg from this drop-down list.

6. The Filter Data dialog box should appear as shown in Figure D-19.

7. Click OK to exit the Filter Data dialog box.

8. Let’s also change the sort order, so we can see the most recent month first. Click Sort and Group in the Report Builder toolbar. The Sort dialog box appears.

9. Select the Delivery Month group in the Select Group list. Pick Delivery Month from the Sort By drop-down list and select Descending for this sort. The dialog box should appear as shown in Figure D-20.

   
   Figure D-20: The Sort dialog box

10. Click OK to exit the Sort dialog box.

11. Click Run Report to preview the report. Note, there are now only entries for 2005 and the months are in descending order.

12. Click Design Report. We’ll try one more modification. Suppose, now that we have seen the information in this report, we want to analyze not only which companies are sending packages to Axelburg, but also where those packages are originating from. We need to look at the location of each company. We use the city from the billing address to make that determination.

13. Select Customer in the Entities list, and then select the Billing Contact Info folder under Customer in the Entities list.

14. Click the Billing City field in the Fields list and drag it to the left of the Customer column in the report layout. A new grouping is created for Billing City. Your report layout should appear as shown in Figure D-21.

    
    Figure D-21: The completed table report layout

15. Click Run Report. The report should appear similar to Figure D-22.

    
    Figure D-22: The completed table report preview

Clickthrough Reports and Saving the Report Builder Report

You have seen how the Report Builder enables us to quickly build a table report. You have also seen how you can enhance that report to gain more information as you conduct your ad hoc analysis. Beyond this, exploring related information during ad hoc analysis is often helpful. Further, once you have a report that works well, you may want to share that report with others.

Let’s look at how to do both of these tasks.

1. Click Run Report, if the report is not running.

2. Hover the mouse pointer over Landmark, Inc. on Page 1 of the report. The mouse pointer changes from an arrow to a hand. This indicates a clickable link is at this location in the report.

3. Go ahead and click this link. A report showing information about the customer, Landmark, Inc., appears. This is a clickthrough report. This report did not exist before you clicked the link. Instead, it was built on the fly when you asked for it.

4. Click the 2 across from # Invoice Headers. A clickthrough report showing the two invoice headers appears.

5. Click the 10 under # Invoice Details. A clickthrough report showing the ten invoice detail lines for this invoice appears.

6. Click the 7 under Line Number. A clickthrough report showing the information from invoice detail line 7 appears. As long as there is related information, you can continue to navigate to new reports using the clickthrough links.

7. Click the Back to Parent Report arrow in the Report Viewer toolbar to navigate back through the clickthrough reports. The back arrow is shown in Figure D-23. Continue going back until you get to the original Customer Deliveries table report.

   
   Figure D-23: The Back to Parent Report arrow in the Report Viewer

8. Click Save in the Report Builder toolbar. The Save As Report dialog box appears. The Save Report dialog box always starts at the Report Server Home folder.

9. Double-click Galactic Delivery Services in the list of folders. Double-click Shared Reports. Enter Customer Table Report for Name, and then click Save. (To open an existing Report Builder report, click Open on the Report Builder toolbar.)

10. Return to the Report Manager running in your browser and navigate to the Shared Reports folder. An entry for the Customer Table Report is in the Shared Reports folder. This entry was created when you saved the report. In fact, the only way to save a Report Builder report is to place it in a folder on the Report Server.

11. Click the entry for the Customer Table Report to run the report. You see it looks exactly the same as when you ran the report in the Report Builder. You can even click on items to jump to clickthrough reports.

Once you have a report in the Report Manager, you can save the report definition to a file in your file system and edit it in a report project with Visual Studio 2005. This makes it possible for an ad hoc report to serve as a starting point for the authoring of a standard report. To do this, click the Properties tab while you are viewing the Customer Table Report in the Report Manager. The Edit link under Report Definition enables you to save this report definition to a file. See Chapter 10 for more information.

If you do edit a Report Builder report in Visual Studio 2005, you may have to modify the data source and query used in the dataset of the report. Once these changes have been made, the report cannot be edited in the Report Builder. You may be able to make some minor formatting, without previewing the report in Visual Studio, save the report back to the Report Manager, and still edit the report in the Report Builder.

Creating a Matrix Layout Report

Next, we look at a Report Builder report that functions a bit differently. This is the matrix layout report. As mentioned earlier, a matrix layout report is the same as what other tools call a pivot table or crosstab report. Let’s see how it works.

Creating the Basic Matrix Report

1. Return to the Report Builder, if you are not already there. Click Design Report, if the previous report is still being run.

2. Click New in the Report Builder toolbar.

3. We use one of the perspectives from the data model for this report. Expand the Galactic Report Model entry in the Task pane and select the Package Tracking perspective.

4. Select Matrix (cross-tab) for the report layout in the Task pane.

5. Click OK in the Task pane.

6. Double-click the Click to Add Title text box.

7. Enter Deliveries by Customer by Service Type into this text box as the report title.

8. Select Customer in the Entities list.

9. Drag the Name field from the Fields list and drop it on the Drag and Drop Row Groups area of the report layout.

10. In the Entities list, select Deliveries. Also in the Entities list, select Service Type under Deliveries.

11. Drag the Description field from the Fields list and drop it on the Drag and Drop Column Groups area of the report layout.

12. Drag the #Deliveries field from the Fields list and drop it on the Drag and Drop Totals area of the report layout.

13. Click Run Report. Just that quickly, you have a report showing the number of deliveries of each type for each customer.

Adding Groupings to the Matrix

Let’s add more grouping levels to the matrix.

1. Click Design Report.

2. Drag the Pickup City field from the Fields list and drop it to the left of Customer in the matrix. This creates a grouping on the pickup city.

3. Click Run Report. Note how the report now has a row grouping for each Pickup City. You can expand a Pickup City to see the row groupings for the Customers within that Pickup City.

4. Click Design Report.

5. Because this report grows horizontally, it should use a landscape page layout. Right-click somewhere on the page layout area that is not occupied by a report item. Select Page Setup from the Context menu. The Page Setup menu appears.

6. Select Landscape and click OK.

7. Expand Pickup Date Time in the Fields list. Drag the Pickup Date Time Year field from the Fields list and drop it below the Service Type (#Deliveries) column heading as shown in Figure D-24. This creates a column grouping for each year with columns within each year for each Service Type.

   
   Figure D-24: Adding a new column grouping to the matrix

8. Click Run Report. Note the new column grouping.

9. Click Save in the Report Builder toolbar. The Save As Report dialog box appears.

10. Double-click Galactic Delivery Services in the list of folders. Double-click Shared Reports. Enter Deliveries by Customer by Service Type Report for Name. Click Save. This report is now available in the Shared Reports folder on the Report Server.

Creating a Chart Layout Report

Finally, we look at a Report Builder report that creates a business chart.

Creating the Chart Layout Report

1. Return to the Report Builder, if you are not already there. Click Design Report, if the previous report is still being run.

2. Click New in the Report Builder toolbar.

3. Select the Galactic Report Model in the Task pane.

4. Select Chart for Report layout in the Task pane. Click OK in the Task pane.

5. Right-click somewhere on the page layout area that is not occupied by a report item. Select Page Setup from the Context menu. The Page Setup menu appears.

6. Select Landscape and click OK.

7. Charts like lots of space. Click the chart. Click the sizing handle on the right side of the chart and drag the chart as wide as the page. This is shown in Figure D-25.

   
   Figure D-25: Expanding the chart report item

8. Select the text box containing the word “Filter:” and drag this text box down to the bottom of the page.

9. Click the chart. Click the sizing handle on the bottom of the chart and drag the bottom of the chart, so it is just above the Filter text box.

10. Select Customer in the Entities list. Drag the Name field from the Fields list and drop it on Drag and Drop Category Fields.

11. Select Deliveries in the Entities list. Drag the #Deliveries field from the Fields list and drop it on Drag and Drop Data Value Fields.

12. Expand the Pickup Date Time field in the Fields list. Drag the Pickup Date Time Year field from the Fields list and drop it on Drag and Drop Series Fields.

13. Right-click the chart and select Chart Options from the Context menu. The Chart Options dialog box appears.

14. The Chart Type tab of the dialog box enables us to change the type of chart being used to display the data. We use the default chart type, the Column chart. Select the Titles tab.

15. Enter Deliveries by Customer by Year for Chart Title. Enter Customers for Category Title. This is the label on the X axis of the chart. Enter Number of Deliveries for Value Title. This is the label on the Y axis of the chart.

16. Select the 3-D Effect tab.

17. Check the Display Chart with 3-D Visual Effect check box. Also, check the Orthographic check box.

18. Click OK to exit the Chart Options dialog box.

19. Click Run Report. Your report should appear as shown in Figure D-26.

    
    Figure D-26: The chart report preview

20. Click Save in the Report Builder toolbar. The Save As Report dialog box appears.

21. Double-click Galactic Delivery Services in the list of folders. Double-click Shared Reports. Enter Deliveries by Customer by Year Chart for Name. Click Save. This report is now available in the Shared Reports folder on the Report Server.