SQL Report Builder

CREATING A BIKESTORES COLUMN 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_Sales_CTE (BikeName, BikeSales, BikesSold)

AS(

SELECT product_name,

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

FROM Sales.Order_Items SOI1

WHERE SOI1.product_id = PR.product_id) AS TotalSales,

(SELECT SUM(SOI2.quantity)

FROM Sales.Order_Items SOI2

WHERE SOI2.product_id = PR.product_id) AS BikesSold

FROM Production.Products PR

)

SELECT TOP 5 BikeName, BikeSales

FROM BikeStores_Sales_CTE

ORDER BY BikeSales DESC;

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

of 5 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 Column option and then

click on the NEXT button.

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

BikeName 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. Click on the RUN button in the upper left

corner of the window to test out the report.

[14] 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.

[15] 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.