SQL Report Builder

CREATING A BIKESTORES TABULAR 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

Table or Matrix Wizard option.

[3] You’ll be presented with the New Table or Matrix 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_Store_Performance_CTE (BikeCategory, SalesYear,

SalesAmount) AS

(

SELECT category_name, YEAR(OD.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 PR.product_id = OI.product_id

INNER JOIN Sales.Orders OD

ON OD.order_id = OI.order_id

GROUP BY category_name, YEAR(OD.order_date)

)

SELECT BikeCategory, SalesYear, SalesAmount

FROM BikeStores_Store_Performance_CTE

ORDER BY BikeCategory, SalesYear;

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

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

button to continue.

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

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

the SalesYear item with your mouse and drag it to the Column Groups 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.

[11] You’ll then see the “Choose the layout” screen. Enter a checkmark for the “Show

subtotals and grand totals” option. 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 Building window.

You can add a title as specified on the screen. Right-click on the cell containing

Sum(SalesAmount) and select the Placeholder Properties item from the right-click

menu.

[14] In Placeholders Properties window, access the Number area and select the Currency

option in the Category section. Enter a checkmark for the Use 1000 Separator (,) item.

Click on the OK button to apply the change.

[15] Repeat Steps 13 and 14 for the other report cells that start with the term Sum.

[16] Click on the RUN button in the upper left corner of the window to test out the report.

[17] The generated report should 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.