Export (0) Print
Expand All

Using a Report Builder

Updated: December 20, 2010

Applies To: Operations Manager 2007 R2

After you have a query that retrieves the data that you need, you are ready to create a report. There are several tools that can be used to create a report: Microsoft Business Intelligence Development Studio (BIDS), Microsoft Visual Studio, Report Builder, or Microsoft Excel. This guide uses BIDS because it is already installed with Microsoft SQL Server and integrates seamlessly with Microsoft SQL Server Reporting Services (SSRS).

Creating a New Report Project

The easiest way to create a new report is to use the report wizard, which creates a simple report and populates it with a basic data display that you select. You can also create a report server project, which creates a blank report project that you then populate with a report and the data that you want to display.

To create a new report project by using the report wizard

  1. Open BIDS, click File, click New, and then click Project.

  2. In the New Project window, in the Project Types pane, make sure that Business Intelligence Projects is selected. In the Templates pane, select Report Server Project Wizard. Enter a name for the project, and then click OK.

  3. The Report Wizard starts. On the welcome page, click Next.

  4. On the Select the Data Source page, enter DataWarehouseMain as the name, leave the Type as Microsoft SQL Server, and make sure that you select the Make this a shared data source check box. Click the Edit button.

  5. The Connection Properties dialog box appears. In the Server Name field, enter the name of the server. In the Log on to the server box, enter the appropriate credentials, as discussed in Setting up the Environment. In the Connect to a database box, select the Select or enter a database name option. In the drop-down box, select the OperationsManagerDW database, and then click OK. The wizard will reappear with the Connection String box filled in. Click Next.

  6. The Design the Query page of the wizard appears. In the Query string box, if you have created the query already, as described in Creating Custom Queries, paste the query into the Query String field. If you have not yet created a query, click the Query Builder button to start the Query Designer.

  7. Within the Query Designer, the default view is the graphical query designer. For more information about how to design a query visually, see the guide to Query Designer (http://go.microsoft.com/fwlink/?LinkID=207899). Click the Edit As Text button to edit the query directly in the window. Click the Import… button to use a query that you saved previously. The ! button runs the query and displays the results in the lower pane of the Query Designer. If the report does not show the data that you expected, you can revise the query and test it without recreating the report. After you have created the query, click OK.

  8. You are returned to the Design the Query page of the wizard. Click Next.

  9. On the Select the Report Type page of the wizard, select Tabular or Matrix. A tabular report returns columns; a matrix report uses an intersection of columns and rows for a more precise view of the data. Click Next.

  10. On the Design the Table page of the wizard, select how you want the data in the table to be organized. The options are as follows:

    • The Page option indicates the fields that appear at the page level of the report.

    • The Group option indicates the fields by which to group the data in the table.

    • The Details option indicates the fields that are displayed in the details section of the table.

    To change the order of the fields in any group, select a field, and then click the up button or the down button. You can also accept the defaults by clicking Next.

  11. On the Choose the Table Style page of the wizard there is a list of several styles that affect color and layout choices. Select the visual style you prefer for the table. Click Next.

  12. On the Choose the Deployment Location page of the wizard, select where the report will be deployed. By default, the URL for the Operations Manager reporting server is entered. However, you should confirm that this URL is correct. Enter a name for the Deployment folder or accept the default.

  13. On the Completing the Wizard page, enter a name for the report. This will be displayed on the report page. You can review the report summary data on this page. If you want to adjust any of the settings, click the Back button and make the appropriate changes. If you select the Preview report check box, the editor will open in the Preview tab instead of the Design tab. Click Finish to continue.

  14. The basic layout of the report appears in the main window of BIDS. You will see the following two tabs:

    • The Design tab, which shows the layout of the report with the fields returned from the query in place

    • The Preview tab, which shows what the data looks like in the report

To create a report without using the report wizard

  1. Open BIDS, click File, and then click New Project.

  2. In the New Project window, in the Project Types pane, make sure that Business Intelligence Projects is selected. In the Templates pane, select Report Server Project. Enter a name for the project, and then click OK.

  3. The project is created, but still shows the Start page as there is no report in the project yet. In the menu bar, click Project, and then click Add New Item. The Add New Item dialog box appears. In the Templates pane, select Report, and then click Add. The design surface appears in the main window with the Design tab selected, which shows a blank report.

  4. You must add a data source and a dataset to your report in order to display any data. In the Report Data pane, click New, and then click Data Source. The Data Source Properties dialog box appears. Provide a name for the data source (DataWarehouseMain is recommended) or accept the default. If you have not created a shared data source, select Embedded connection; for the Type, select Microsoft SQL Server, and for the Connection string enter the connection information for the OperationsManagerDW database. If you created a shared data source previously, as discussed in Setting up the Environment, select Use shared data source reference, and then select the shared data source in the box. Click OK.

  5. In the Report Data pane, click New, and then click Dataset. Enter a name for the dataset or use the default. Select the Data source, usually DataWarehouseMain, and then enter the query. This can either a query created earlier, or click the Query Designer button and create the query using the Graphical Query Designer. When you have finished, click OK.

  6. The dataset appears in the Report Data window with the available dataset fields retrieved by the query displayed below the dataset name.

  7. Click View, and then click Toolbox to display the Toolbox dialog box. The Toolbox contains several standard items that can be used in reports. From the Toolbox, select a Table, Matrix, Chart, or Gauge, depending on how you want to represent the data, and drag it onto the design surface of the report. From the Report Data window, drag the desired fields onto the Table, Matrix, Chart, or Gauge tool, as appropriate.

  8. Check the appearance of the report by clicking the Preview tab.

The following sections discuss how to change specific aspects of the report. For more information about report design, see SQL Server 2008 Books Online(http://go.microsoft.com/fwlink/?LinkID=207899).

Changing the Data in the Report

To change the data that’s displayed in the report, you must change the query.

To change the query

  1. In the Report Data tree, right-click the dataset, by default this is DataSet1, and then select Query. This starts the Query Designer.

  2. Within the Query Designer, click the Edit As Text button to edit the query directly in the window. Click the Import… button to use a query that you saved previously. The ! button runs the query and displays the results in the lower pane of the Query Designer. If the report does not show the data that you expected, you can revise the query and test it without recreating the report.

Changing the Appearance of the Report

Within the report dataset, you can change the rows, columns, or individual cells. When you click any cell in the dataset, borders will appear around the table. You can click the border to select a row or a column. To select a specific cell in the table, click in that cell. The properties for the row, column, or cell you selected are displayed in the Properties window. By default, this window is located at the lower-right corner of the interface.

To change the name of a column, click in the column heading and edit the text in the text box. You can also change the font, background color, text color, and many other properties of the text box with the Properties window. Drag the borders of the column to change the column’s size. Reorder the columns by dragging them to the new desired position.

To enable sorting in table columns

  1. Right-click the column to which you want to add sorting, and click Text Box Properties. The Text Box Properties window opens.

  2. Click the Interactive Sorting tab. Select the check box Enable interactive sorting on this text box. You can select to sort by detail rows or by groups. Select the column to sort by, and then click OK.

Note: If you want the report to sort on a particular field by default, add an ORDER BY clause to the query.

Adding a Chart to the Report

Instead of, or in addition to, the table or matrix you may want to include a chart in the report for a visual representation of the data.

To add a chart to the report

  1. You may need to expand the report area to provide sufficient room for the chart by using the resize handles.

  2. Click View, and then click Toolbox. The toolbox dialog box opens; you may want to pin the toolbox in place. In the toolbox, click Chart, and then click in the report to place the chart, or drag to size the chart in the report space.

  3. The Select Chart Type dialog box appears, giving you various chart choices. Select the type of chart you want to add, and then click OK. The chart appears on the design surface.

  4. Drag the chart in the report to move it or use the resize controls to resize it.

  5. Click the area labeled Axis Title to change the text on the axis.

  6. To add data fields to the report, select the Report Data pane (click View, and then Report Data if the Report Data pane is no longer visible), and expand the dataset to show the data fields the query retrieves. On the design surface, click the chart that you added. This reveals three areas around the border of the chart as follows:

    1. Drop data fields here: Drag data fields here that you want to represent as values in the chart (for example, the height of the bars in a bar graph).

    2. Drop category fields here: Drag data fields here for which you want to present the values (for example, the items on the x-axis in a bar graph).

    3. Drop series fields here: These fields are optional. Drag data fields here to add an extra dimension to the chart (for example, if a bar graph has multiple data fields for a single category field).

  7. With the chart selected, the Properties window lets you define the properties of the chart itself, such as the colors, borders, and size.

For more information about charts, see Working with Chart Data Regions in SQL Server Books Online: http://go.microsoft.com/fwlink/?LinkID=207928.

Adding an Image to the Report

You may want to add a static image to the report, for example, a company or department logo.

To add an image to the report

  1. Open the Toolbox, click the Image item, and drag it onto the design area. The Image Properties dialog box appears.

  2. In the Image Properties dialog box, provide a name for the image (which will not be displayed), and an optional tooltip (displayed when the users hovers the pointer over the image). The Select the image source box has the following three options:

    • External: For images that exist on the report server or another external source. If you select this option, you must provide a URL to the image location in the Use this image field. If the image is changed at its source, the image in the report will change dynamically.

    • Embedded: For images that do not have to be changed. Click the Import button and browse to the image location. The image is embedded in the report and will not change dynamically.

    • Database: For images stored in the database. Provide the path of the image in the Use this field box. In the Use this MIME type box, select the appropriate MIME type for the image.

  3. The Size, Visibility, Action, and Border tabs in the Image Properties dialog box enable you to change the appearance of the image in the report, and also enable you to set the image as a link to another report or URL.

For more information about how to use images in reports, see Images in SQL Server Books Online (http://go.microsoft.com/fwlink/?LinkID=207938)

Adding an Additional Dataset to the Report

You may want to add another set of data to the same report, using a different dataset. This lets you consolidate reports into a single document for ease of reference.

To add an additional dataset to the report

  1. In the Report Data window, click New, and then click Dataset. The Dataset Properties dialog box appears.

  2. In the Name field, enter a name for the dataset or use the default.

  3. In the Data source field, select the data source, usually DataWarehouseMain.

  4. In the Query field, enter the query. This can either a query created earlier or click the Query Designer button to create a query. When you have finished, click OK.

  5. A second dataset will appear in the Report Data window. From the Toolbox, select a Table, Matrix, Chart, or Gauge, depending on how you want to represent the data. Drag the selected control onto the design surface, and a new data item will appear in the report.

  6. From the Report Data window, drag the desired data fields onto the Table, Matrix, Chart, or Gauge tool, as appropriate.

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft