Using a Report Builder

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.