This is the continuation of Introduction to Report Builder 3.0 Part 1. This article will show you how to create your first report (SalesByFiscalYear) using Report Builder 3.0, and how to pulish the report to your Report Server. This article assumes the following:
- You have installed SQL Server 2008 and Report Builder 3.0 on the computer you are running this example
- You have created a shared data source and pulished the report server (optional). This article will create a data source using an embedded connection, however.
- You have the sample AdventureWorks database installed. You can get the sample database here.
Open Report Builder
1) From you desktop select Start >> All Programs >> Microsoft SQL Server 2008 Report Builder 3.0 >> Report Builder 3.0
You should see the following:
Configure Report Builder
1) From the upper left corner of Report Builder, click the Red Ribbon and then the Options from the lower left corner.
2) Select the Settings option from the left pane.
3) Enter your default Report Server location and set the other property values as desired. For example: http://localhost/ReportServer Click OK to save your settings and close the dialog box.
Creating a Data Source
1) From the Report Data pane, click New and select Data Source
the data source dialog will open
Note: You may choose an existing shared data source, if you have previously created one. For this example, we will be using an embedded connection.
2) Provide a name for your data source. We will use the default value provided in this example.
3) Select "Use a connection embedded in my report" option
4) Select your Connection Type from the drop down. For this example we are using Microsoft SQL Sever.
5) Click the Build button from the right of the "Connection string:" textbox. You will see the Connection Properties dialog below.
6) Enter the same values in the property fields, found in the image above.
- Test the connection when you are done.
- If test is successfull, click OK.
- Click OK to close the Connection Properties window.
- Click OK to close the Data Source Properties window.
- You should now see your data source name on the left Report Data pane.
Note: the value in Server Name property is a period "."
1) From the middle pane of the report designer click on the Table or Matix icon.
The New Tabe or Matrix window will open.
2) From the Data Source Connection pane, select the data source you created in the previous section Creating a Data Source. Click Next from the lower right corner when you are done.
3) You should see the Design Query view, similar to below. Expand the Sales node and then expand the Views folder. Place a check mark in the vSalesPersonSalesByFiscalYear.
4) From the top toolbar of the Query Designer, click Run Query to test the view. You lower Query Results pane should render results from the view.
5) Click Next from the lower right corner of the Query Designer window.
6) Arrange Fields the following way:
- add SalesTerritory and FullName to the Row groups, respectively.
- add ID2002, ID2003 and ID2004 to the ∑ Values
7) Click Next when you are done assigning fields.
8) Keep the next page default values and click Next.
9) Click Finish to complete the wizard.
This section we will apply a currency format to our sales field values. After the formatting of the currency values we will give the report a Title and run the report.
1) Place your cursor in the first [SUM(ID2002)] field. Right click on the field and select Text Box Properties. The Text Box Property window will open.
2) From the left pane of the Text Box Properties window, select Number.
3) Under the Category pane, select Currency. Adjust settings as desired.
4) Click OK.
5) Repeat this steps 1-4 for all the SUM fields.
Note: There is an easier way to do this; press and holding down the Ctrl key and select all the SUM fields with your mouse. From properties pane on the right, find the Format property and pass '$'0.00;('$'0.00) for the value.
6) Place your cursor in the "Click to add title" field.
7) Enter "Sales by Fiscal Year".
8) From the top left part of the Ribbon, click Run.
You should see a similar result.
This section will show you how to publish your report to the Report Server.
1) From the upper right corner of Report Builder 3.0, click on the Save icon.
2) The Save As window will open. Select the folder you want to save the report to.
3) Enter SalesByFiscalYear.rdl for the name of the report.
4) Click Save from the lower left of the dialog.
Your report has been saved and published to the Rerport Server. Take the time to review the report in desing mode and explore the properties for each of the fields. This will help you gain a better understanding on how to format reports in the future.
Next post will discuss using parameters in Report Builder 3.0.