Click the Criteria tabs, using the selection pane, add Channels > Channel Desc to your query criteria.

In the criteria pane, click the Filter button on the Channel Desc column to add a filter on that column.

In the Create/Edit Filter dialog box, click the All Choices link to display all values for the column.

You can also limit the list of choices by setting a match criteria using the Match drop-down menu and entering a string, then clicking the All Choices link. The Limited Choices link will limit choices to those that are consistent with any pre-existing filters.

Verify that the Operator for the filter is set to "is equal to / is in" and then click the Direct Sales value in the list of choices. Direct Sales is added as a value in the filter. Click OK to create the filter.

The filter is added to the existing saved filter you used earlier.

Click the Delete button (with and X) on the Channel Desc column to delete it from the criteria. You are filtering on the channel, but you don't need the column in your query.

Another way to achieve this result is to CTRL-click the column in the selection pane, which opens the Create/Edit Filter dialog box with the selected column, but doesn't add the column to the query criteria.

Your query criteria should look like this:

Click the Results tab to view the results of your query.

Other ways to view results are by clicking the view buttons below the tabs in the Criteria pane, or by clicking the Display Results button below the columns in your query criteria. By default, the results are displayed in the Compound Layout view, which contains two other views, a Title view and a Table view. As you will see, you can delete these default views and add other views to the Compound Layout. Later, you will add these different types of views to a dashboard.

Return to the Criteria tab and remove the Channel Desc filter you just created by clicking the X next to the filter in the Filters section.

To open the Edit Table view for your results, click the Edit view icon for the Table view in the Compound Layout.

The column controls for each column are displayed with the results. Using the view-level controls, you can also set table-wide formatting properties, import formatting from other queries, and set grand totals for the entire table.

Click the Total By icon above the Calendar Month Desc column to add subtotals by month to your results.

The measure is totalled each time the value in Calendar Month Desc changes. In this case, the default aggregation rule (SUM) is applied. The default aggregation rule is set in the Oracle BI repository metadata, but can be overridden using controls in the Edit Formula dialog box accessed through the Edit Formula icon in the Edit Table view or the Criteria tab.

Click the Grand Total button at the view level to add a grand total to your results.

In the Display drop-down menu, select Results Only to eliminate the Header Toolbars.

Scroll down to the bottom of the Results pane and click the All Pages button to navigate to the end of your results. Verify that the grand total that you set for the results is present.

In the Display drop-down menu, select Header Toolbars with Results to access the Header Toolbars.

Click the Column Properties icon above the Amount Sold (000) column. In the Column Properties dialog box, click the Data Format tab, select the Override Default Data Format option, then select Use 1000's Separator option and click OK. This will display a comma separator in the number results for the column.

In the Edit Column Format dialog box, click the Save button. If you have permissions as a Web administrator, you can save the data format as the system-wide default for the column you are working with, or for all columns with the same data type.

Since you don't want to set this format as the system-wide default, click in the dialog box to close the Save menu.

Click OK and verify that a 1000's separator is displayed in the results for the Amount Sold (000) column.

Pick Chart in the View menu. By default charts are displayed as a Vertical Bar graph.

Select Line from the Graph drop-down menu and 2D from the Type drop-down menu.

Under the Legends icon in the column area of the left side of the Chart view, select Prod Category to create a separate line in the chart for each product category. Click the Redraw button at the bottom to confirm the change.

The column area of the chart view shows the columns that are included in the request. You can select the columns to display as the different axes in the chart. You can also indicate whether measure labels appear for factual data plotted on an axis. The available axes can change depending on the type of chart selected from the Graph drop-down list.

Click the Axis Titles and Labels icon. In the Left tab of the Axis Titles & Labels dialog box, select Custom Title and change the custom title to Amount Sold. In the Bottom tab, change the custom title to Calendar Month.

Explore some of the other options in the Axis Titles & Labels dialog box. For example, you can toggle whether scale labels are displayed on the axes and set orientation guidelines for your labels. In addition, you can set overrides for the default data format on measures, similarly to the modification you made to the amount sold column in the steps above. Click OK. Your chart should look like this:

Click the General Chart Properties icon. In the General dialog box, select Custom Title, enter Category Sales Last 12 Months as the chart title, and click OK.

Click the Additional Charting Options icon. In the Additional Charting Options dialog box, click the Borders & Colors tab and set the background color to light gray. Click the color box for Background Color and, in the Color Selector dialog box, select light gray from the pallette and click OK.

.

In the Additional Charting Options dialog box, click the Grid Lines tab and set the major grid line color to white and the minor grid line color to light gray, using the same method as above. After you've set both colors, click OK to apply your changes.

Your chart should look like this:

Sort the legend by sorting the data. You want to sort by month in ascending order and then by sales in the last month in descending order:

Click the Results tab to verify that your sort has been applied to the legend in your chart. The legend is sorted to match the order of the lines in the last month.

Select Compound Layout from the View drop-down menu.

Click the Add View link and select Chart to add the Chart view to the Compound Layout view.

Scroll to the bottom of the Compound Layout view and drag the Chart view above the Table view in the Compound Layout. A yellow line will appear when you have a valid insertion point.

Click the Delete View icon in the Title view to delete it from the Compound Layout.

Click the Save Request icon. In the Choose Folder dialog box, click Shared Folders, then click the Create Folder button. In the Create Folder In Shared Folders dialog box, enter Learn and click OK.

In the Save Request dialog box, click the Learn folder. Enter Category Sales as the name and click OK.

Pick Pivot Table in the View menu. This opens the Pivot Table Layout page.

Examine the default pivot table that's created and displayed below the layout controls. Measure labels for the measures in your query appear in blue as columns in the pivot table. Row headings for the dimensional attributes in your query are displayed in grey as rows by default. The measures are displayed at the intersection of the rows and columns.

Deselect the Display Results option. This option displays the results of any layout modifications you make as you work in the Pivot Table layout. To speed performance, you will only view your results periodically.

Drag the Amount Sold (000) Current Month column from the Measures area to the Excluded area in your layout controls. When you see a blue line appear around the control, you have a valid insertion point and can drop the column.

Drag the Calendar Month Desc column below the Measure Labels in the Columns area in your layout controls. When you see a blue line appear, you have a valid insertion point and can drop the column.

Drag the Calendar Month Name column below the Calendar Month Description in the Columns area in your layout controls. Click the Disply Results link to verify your changes in the pivot table.

Your pivot table should look like this:

Deselect the Display Results link.

You need to add a measure to your pivot table to display the percentage of total monthly sales for each product category. Duplicate the Amount Sold measure. Click the More Options icon in the Amount Sold (000) measure and select Duplicate Layer.

Click the More Options icon in the duplicated measure and select Format Headings.

In the Edit Format dialog box, change the caption to % of Period and click OK.

Also note the other formatting options available in the dialog box.

Click the More Options icon in the duplicated measure and select Show Data As > Percent of > Column.

This setting means that the measure will be displayed as a percentage of the column, which is Month. Note the other options that are available; you can present a measure as a percentage of the total amount for any dimension present in the pivot table layout, for example a row or a section. You can also set alternate aggregation rules for the measure using the Aggregation Rule option (the default is Sum).

To total the values of the rows in each column, click the Total icon in the Rows layout control and select After.

Note that you have formatting options available for the total row labels as well as values.

To add totals for each column in each row, click the Total icon in the Columns layout control and select After.

Click the click the More Options icon in the Calendar Month Desc column and select Hidden.

To show both measures together within each calendar month, drag Measure Labels under Calendar Month Name in the Columns layout control.

Click the Display Results link. Your pivot table should look like this:

Note that there are section and page levels of the pivot table that we have not used in this example. Effective use of these would require additional attributes. Experiment, if you want, by temporarily dragging Prod Category to section and then page.

Save the request by clicking the Save Request icon.

In the criteria pane, click the Filter button on the % Chg Amount Sold MAgo column to add a filter on that column.

In the Create/Edit Filter dialog box, select the is less than operator, and enter 0 as the value, then click OK.

The filter is added to the existing saved filter you used earlier. Your filters should look like this:

Click the Display Results button to view the results of your query.

Your results should look like this:

Select Narrative from the View drop-down menu.

In the Narrative view workspace, enter the following: @1 declined @2% this month compared with last month in the Narrative field.

The narrative is a combination of text and query column values. In this example, @1 refers to the first column in the query, Prod Category, and @2 refers to the second column, % Chg Amount Sold MAgo. Note that you can control the number of row values returned in the Narrative view by setting the Rows to display value. By default, all queried rows are displayed.

To highlight the column values in the narrative, select @1 in the narrative and click the Bold button. Also add bold tags to @2%.

Your results should look like this:

Select No Results from the View drop-down menu. You need to create a No Results view to appear when there are no results for the query.

In the No Results view workspace, enter No categories declined this month! as the headline. Enter All categories had positive sales results compared with the previous month as text.

Your results should look like this:

Click the Save Request icon. In the Choose Folder dialog box,select the Learn folder and save the request as Narrative and No Results Views.

Click the Answers link and open the first query you saved. In the selection pane, click Shared Folders > Learn > Category Sales.

Click the Modify button.

Click the Results tab and select Column Selector from the View drop-down menu.

Select the Include Selector option in Column 3, currently Prod Category.

In the selection pane, click the following columns to make them available in the Column Selector: Customers.Country Region, Customers.Country, and Channels.Channel Desc.

Your Column Selector in the results pane should look like this:

Select Compound Layout from the View drop-down menu.

Click the Add View link and select Column Selector to add the Column Selector view to the Compound Layout view.

Notice that views that you have built for the active request appear in the View and Add View drop-down menus with green circle icons next to them.

Scroll to the bottom of the Compound Layout and drag the Column Selector view above both Table and Chart views.

In the Column Selector, select Country.

Your Compound Layout view should look like this:

Note that the third column in the table now reflects values for the Country column, as does the chart. Switch the column back to Channel Desc. Save the request.

Duplicate the existing chart:

Select View Selector from the View drop-down menu.

In the View Selector design workspace, select Chart, Chart:2, and Pivot Table in the Available Views field, using CTRL + Click to select multiple views.

Click the Move Right icon to add them to the Views Included field.

Select Chart in the Views Included field, click the Rename button, and, in the Rename dialog box, rename it 2D Chart. Click OK.

Rename Chart:2 as 3D Chart and Pivot Table as Trend Data. Use the Move buttons to arrange the views in the list as follows: 2D Chart, 3D Chart, Trend Data.

Use the View drop-down menu to navigate to the Compound Layout view. Delete the Chart view from the Compound Layout.

Add the View Selector view using the Add View button and drag it above the Table view.

Select Trend Data from the View Selector.

Your chart should now change to the Pivot Table view:

Click the Settings link and select Administration.

In the Oracle BI Presentation Services Administration window, click the Manage Interactive Dashboards link.

The Oracle BI Presentation Services Administration window offers access to many administrative features, including session monitoring and management of user and group privileges across the Oracle BI Presentation Catalog.

In the Manage Dashboards window, click Create Dashboard.

</