Lesson 3: Creating a Report Model-Based Query

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.

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.

Next, you will apply a filter to the query.

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/2007.

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.