Tutorial

You can modify the query by performing the following tasks and actions in the Query pane

Add data items:

Select a data item in the Available items pane, and then click  .

Specify where you want the new data item to be added by selecting one of the following:

to Rows: adds the data item to the vertical axis.

to Columns: adds the data item to the horizontal axis

Note: To be able to have access to the option Percent of Total in the Edit Options pane, you need to select the available item Total at the beginning of your query and to add it to the columns.

Reorder selected data items:

Select a data item in the Selected Items pane, and then click to move the data item up, or to move the data item down.

Remove selected data items:

Select a data item in the Selected Items pane, and then click .

When you are finished making changes to the query, click OK to apply the query to your current view.

To cancel any query changes that have not been applied yet, click Cancel

Note: For a query to be valid, the rows and the columns must each contain at least one data item.

4. Filter Option

You can subset the data by Using the Filters Option

Note: you can only filter on the selected data items.

To subset data using filters, follow these steps from the filter pane

• Click a data item, and then select Edit.

Select a condition for the Filter from this pane:

Is equal to specifies that a matching value must match the filter value exactly. You can select one or more values.

Is not equal to specifies that a matching value must not match the filter value exactly.  You can select one or more values.

Click OK to apply the filter to your data view. The new filter will appear in the Filter Expression viewer.

5. Totals Option

To have subtotals and total for rows and columns, select the Totals option

6. Percent of Total Option

Note: To be able to have access to the option Percent of Total in the edit options pane, you need to select the available item "Total" at the beginning of your query and to add it to the columns option.

To add the percents to your table, select the Percent of Total option

From the Percent of Total pane, you can add percents to your table by performing the following tasks and actions.

It is recommended that before you select this option, select the Totals option to add the totals to rows and columns

From the Show percent of option, you can select :

• Grand Total
• Column Total
• Row Total
• Staffing Activities Subtotal
• Fiscal Year Subtotal

The Label option allows you to give a name to the column or row that you created, e.g.

• % of Grand Total          
• % of Column Total

Click Add.

Your percent will be added to the bottom pane.

To activate it, you highlight the label created in the pane. 

Click OK.

You can also remove the percent by highlighting the label that you created and click Remove.

You can create more than one percent

7. Scrolling Option

8. Exporting Your Table as an Excel File

To  save a table in  Microsoft Excel format,, you need to click on the Export To tab

Output type:

• Excel - Tab Separated Values (.tsv)
  • Creates a plain text file with tab characters that separate each value.
  • Note:The file extension is .xls.
• Excel - Comma Separated Values (.csv)
  • Creates a plain text file with commas that separate each value.
• Excel - HTML (.html)
  • Creates an HTML file. This option preserves the style information (colors and fonts).
  • Note:The file extension is .xls.

Include:

• Selects the rows to include. To include all rows, select All rows. To select a subset of rows, select Rows, and then type the beginning and end row numbers for your selection in the from and to fields, respectively.

Format:

• Specifies whether to export formatting information with the data:
• To export formatting information with the data, select Formatted Data.

Options:

• Specifies whether to repeat row labels.
• To repeat row labels, select Repeat row labels if expanded.
• To include row labels only once, clear Repeat row labels if expanded.
• When you have finished choosing options, click the OK button. A dialog box appears which enables you to select where to save the file.

Note:

• In Microsoft Excel, if a column it is too narrow to show the data, the value is displayed as ###. To see the values, increase the width of the column.

9. Sort rows data

Click on the row heading (Staffing Activities) to obtain options

Select the Sort Rows Data option

10. Sorts Columns Data

Click on the column heading (Fiscal Year) to obtain options

Select the Sort Columns Data option

11. Rotate table - ROWS

Click on the row heading (Staffing Activities) to obtain options

Select the Rotate Table Option

Rotate table - Columns

Click on the column heading (Fiscal Year) to obtain options 

Select the Rotate Table Option

12. Other options and tips

View (side bar):

• This option generates a bar chart or a pie chart from the customized report that was created by the user.

Printer Friendly (side bar):

• Reports can be printed by clicking on Printer Friendly.

Percent of Total Option:

• To be able to have access to the option Percent of Total in the edit options pane, you need to select in the Available items pane: Total at the beginning of your Query and to add it to the Selected items pane under Columns.

Fiscal Year:

• If in your Query you opt not to include Fiscal Year as a selected item, the result of your query will have five Fiscal Years of Staffing Activities grouped together in one column.