SQL Report Builder

CREATING A BIKESTORES PIE CHART REPORT

[1] To start the Microsoft Report Builder application, access the Windows Start menu and

select the Microsoft Report Builder folder.

[2] You’ll then see the Getting Started screen in Microsoft Report Builder. Click on the

Chart Wizard option.

[3] You’ll be presented with the New Chart screen. Click on the “Create a dataset” option

and click the NEXT button to continue.

[4] You will then see a screen asking for you the “Choose a connection to a data source.”

Click on the NEW button.

[5] You will then see the Data Source Properties screen. You can leave the Name value as

is or provide a preferred name. In the Connection String field, enter the following value:

Data Source=localhost\SQLEXPRESS

Click on the TEST CONNECTION to verify the connection string. You should receive

a “Connection created successfully” message. Click on the OK button close out the

prompt. Then click on the OK button on the Data Source Properties screen to complete

the data source specification.

[6] You’ll be returned to the “Choose a connection to a data source screen.” Make sure the

new data source entry is highlighted and then click on the NEXT button to continue.

[7] You’ll then see the “Design a query” screen. Click on the “Edit as Text” option in the

top left portion of the screen.

[8] In the provided text field area, enter the following SQL statements:

USE BikeStores;

WITH BikeStores_Bike_Category_Sales_CTE (BikeCategoryName, BikeSales,

BikesSold) AS

(

SELECT category_name,

(SELECT SUM(SOI1.quantity*SOI1.list_price)

FROM Sales.Order_Items SOI1 INNER JOIN Production.Products PR1

ON SOI1.product_id = PR1.product_id

WHERE PR1.category_id = CT.category_id) AS TotalSales,

(SELECT SUM(SOI2.quantity)

FROM Sales.Order_Items SOI2 INNER JOIN Production.Products PR2

ON SOI2.product_id = PR2.product_id

WHERE PR2.category_id = CT.category_id) AS BikesSold

FROM Production.Categories CT

)

SELECT BikeCategoryName, BikeSales

FROM BikeStores_Bike_Category_Sales_CTE

ORDER BY BikeCategoryName;

[9] Click on the exclamation point ( ) to execute and test the entered SQL code. A total

of 7 records should be returned from the SQL statements. Then click on the NEXT

button to continue.

[10] You will then see the “Choose a chart type” screen. Select the Pie option and then click

on the NEXT button.

[11] You will then be presented with the “Arrange fields” screen. Double-click on the

BikeCategoryName and BikeSales entries in the “Available fields” list. The items will

automatically populate the “Categories” and “Values” areas. Click on the NEXT button

to continue.

[12] You’ll then see the “Preview” screen. Click on the FINISH button.

[13] The configured report screen will then be presented in the main Report Builder window.

You can add a title as specified on the screen. Right-click on the pie item in the chart and

select the Show Data Labels item in the right-click menu.

[14] Right-click on one of the data labels contained within the pie chart. Select the Series

Label Properties item from the right-click menu.

DATA LABELS

[15] In the General section of the Series Label Properties window, enter #PERCENT into

the Label Data text field. Then click on the OK button to apply the change.

[16] In the main Report Builder screen, click on the RUN button in the upper left corner of the

window to test out the report.

[17] The generated report should look similar to what is provided below. Take a screenshot of

your generated report as proof of report completion for the assignment. Incorporate the

screenshot into your assignment document. Then click on the DESIGN button in the

upper left corner of the window to return to the main Report Builder screen.

[18] Save the Report Builder file via FILE-->SAVE AS from the top menu. Provide an

applicable file name for the Report Builder file. You can then exit from the Report

Builder application.