Lesson 3: Creating a Report Model-Based Query
In this lesson, you will build a query that indicates the product sales by year and quarter. You will add six fields to the query, and then apply a filter so that only data on or after 5/1/2003 is returned. The query you are about to design is the same query that is used for the SQL Server 2008Report Builder sample report titled Product by Sales Year.
To open the Query Designer window
In Solution Explorer, right-click Reports and then select Add New Report.
The Report Wizard opens.
Click Next.
The Select the Data Source page appears.
In the Shared data source drop-down list, select RMQBdatasource.
Click Next.
On the Design the Query page, click Query Builder.
The Query Designer window opens. Note that this window looks similar to Report Builder. On the left is a list of entities and their respective fields that you can add to your query. To the right of the entities list is a drop zone. To build your query, drag the entities and fields that you want to use in your query to this area. To the right of the fields list is the results pane; you can run your query and view the returned results at any time.
To build the query
In the Entities list of the Query Builder window, select Product.
From the Fields list, select the Product Category field, and drag it to the query area.
From the Fields list, drag the Product Subcategory field to the right edge of the Product Category field.
From the Fields list, drag the Name field to the right edge of the Product Subcategory field.
In the Entities list, click Sales, and then click Sales Order.
In the Fields list, expand the Order Date field.
From the Fields list, drag the Order Year field to the right edge of the Name field.
From the Fields list, drag the Order Quarter field to the right edge of the Order Year field.
From the Fields list, drag the Sum Total Due field to the right edge of the Order Quarter field.
You have added all the fields that you need and your query should look similar to the following picture.
.gif "A report model-based query in Query Designer.")
Next, you will apply a filter to the query.
To apply a filter
On the Query Designer toolbar, click Filter.
The Filter Data dialog box opens.
In the Entities list, click Product.
In the Fields list, double-click Product Category.
In the filter clause, click equals and then select in a list.
Click no values selected, and then select Accessories, Bikes, and Clothing.
Only sales data for these products will be returned in the rendered report.
In the Entities list, select Sales Order.
In the Fields list, double-click Order Date.
In the filter clause, click Order Date and then select Prompt.
Right-click equals, and then select on or after.
In the drop-down box, type or select 5/1/2003.
Click OK.
You have successfully applied a filter to the report query so that only sales of accessories, bicycles, and clothing that occurred on or after 5/1/2003 are displayed when a report viewer is prompted to enter a specific sales date. Next, you will return to the Report Wizard.
To return to the Report Wizard
In the Query Designer, Click OK.
The Design the Query page of the Report Wizard appears again. The query you just created is in the Query string box.
Next Steps
Next, you need to design your report. In the next lesson, you will select a report structure and style, and add totals to the report. See Lesson 4: Designing the Report.
See Also