SQL Report Builder

CREATING A BIKESTORES LINE 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 button 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_Monthly_Bike_Category_Sales_CTE (BikeCategory,

SalesMonth, SalesYear, SalesAmount) AS

(

SELECT category_name AS BikeCategory, MONTH(order_date) AS SalesMonth,

YEAR(order_date) AS SalesYear, SUM(OI.quantity*OI.list_price*(1-

OI.discount)) AS SalesAmount

FROM Production.Categories CT INNER JOIN Production.Products PR

ON CT.category_id = PR.category_id

INNER JOIN Sales.Order_Items OI

ON OI.product_id = PR.product_id

INNER JOIN Sales.Orders OD

ON OD.order_id = OI.order_id

GROUP BY category_name, MONTH(order_date), YEAR(order_date)

)

SELECT BikeCategory, SalesMonth, SalesAmount

FROM BikeStores_Monthly_Bike_Category_Sales_CTE

WHERE SalesYear = 2016

ORDER BY BikeCategory, SalesMonth;

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

of 71 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 Line option and then

click on the NEXT button.

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

BikeCategory item with your mouse and drag it to the Series area. Left-click on the

SalesMonth item with your mouse and drag it to the Categories area. Left-click on the

SalesAmount item with your mouse and drag it to the Values area. The item placement

should look like what is presented below. 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.

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.