The following is a step-by-step tour, through the functions of BrioQuery. The purpose of this tour is to demonstrate many of the basic and advanced features of the query process and the on-line data analysis available. The steps include creating a query, working with the results set, working with pivot tables, and working with charts. The steps can also be used as a refresher on how-to use one or more of the functions. If the user continues having difficulty with BrioQuery and these features, attending the BrioQuery training class again, as a refresher, is strongly recommended.
The example uses the FAS database, but another database and fields could be substituted by the user.
If you find an error in the steps or do not understand a step, please let us know by email below. Any suggestions for improvements are also welcome.
Creating a Query
Working with the Results Set
Working with Pivot Tables
Working with Charts
Start the application, select the database, and select the view to query
1. Start BrioQuery
2. Select the DataModel/Table Catalog option from the menu bar.
3. The OCE determines which database(s) is available to the query. The standard OCE’s are listed and defined in the ACS Data Extract web pages. Select the OCE for the database to query (Fas.oce); OK.
4. Enter the Sybase user ID and password; OK.
5. Select the view (slamt_vue) from the Table Catalog panel; double click.
Select the data fields to include in the query
6. Drag the selected fields from the view to the Request line (ouc, acct, obj, auth_bud, ytd, bal_avl).
Modify a selected field to include the sum of two fields
7. Highlight the field (ytd) in the Request line and select Query/Modify from the menu bar.
8. In the definition box add a plus sign (+) after the displayed field.
9. Select the Reference Button; select the Topic (slamt_vue); and then select the Item (curnt_mm), which is the other field to be added; OK.
10. The modify panel now shows the formula for the addition of the two fields and looks like this: (slamt_vue.ytd+slamt_vue.curnt_mm); OK.If all detail levels are not included in the query, the results may need to be combined and summarized (not required in this example because all detail records are distinct)
11. The database may include records at a detail level which are not included in the query (i.e. if account was NOT included in this example). To avoid multiple detail records, the results should be summarized in the query.
12. Highlight the data column(s) on the Request which may not be distinct (auth_bud, ytd, bal_avl) and select the Query/Modify option from the menu bar.
13. In the Modify panel, select the Sum radio button; OK. Repeat for each data column to be summarized.Limit the query selection
14. Drag the conditional limit field (ouc) to the Limit line.
15. In the Limit panel, select the limit condition (Begins With) from the drop down menu and enter the default condition value (99); OK.
16. Query limits reduce the amount of data transferred from the server and over the network to the local PC. It will also speed analysis later.To require entry of a conditional value every time the query is run
17. Click on the triangle mark on the Limit line to expand the options.
18. Highlight the limit field (ouc) and click the Var option.Add a sort to the query
19. Drag the field (ouc) to sort on, from the Request line to the Sort line.
20. Highlight the sort field on the Sort line and then select the Query/Sort Order option from the menu bar. Select either Ascending or Descending order.Add a new data field from another view
21. Select the new view (ouc_vue) from the Table Catalog and drag the view to the work area next to the first view.
22. Determine a common field between the two views to join on and drag the join field (ouc) from the first view to the corresponding field (ouc) in the second view.
23. Drag the new field (descrpt) from the second view to the Request line and put it next to the ouc field.Process the Query
24. Select the Query/Process option from the menu bar.
25. Enter the required condition variable (any valid OUC school number the user is authorized to in the data).
26. The query will run and will display the results under the Results Tab.
27. To stop a query in the middle of processing hold down the Alt and End keys. Then select cancel from the message panel.
Once the query is finished and the Results Set displayed, all subsequent processing is done locally. This smaller local data set is used to refine the Results Set, manipulate the Pivot Tables, create the Detail Reports, and build the Charts. This allows faster analysis and does not require continued connection to the Sybase server.
Review the data to verify the query returned the desired results
1. Confirm that the results are complete and the data is as expected. The Row count which was returned from the query is displayed on the bottom status line.
Resize the columns to fit and display all the data
2. Select an individual column, by clicking in the column, or select the Edit/Select All option from the menu bar, to select all the columns.
3. Select the Format/Squeeze option from the menu bar to automatically resize the selected column(s).Refine the Results Set by applying additional selection limits
4. Drag the column (obj) in which additional conditional limits are to be set, to the Limit line.
5. In the Limit panel, select the limit condition (Begins With) from the drop down menu and enter the conditional value (0). Also select the Not check box (to exclude all receipt object codes); OK.
6. These local limits do not change the Results Set, but change only what is displayed.
Note: Query limits require processing of the query, but local limits, under the Results Tab, are applied only to the Results Set.Enhance the appearance of the data
7. Select the column of data (i.e. numbers, dates, etc.) to be changed, by clicking in the column.
8. Select the Format/Number option from the menu bar.
9. Select the new format from the one’s listed or select Other... and add the format needed.Extend the data available for analysis by creating new computed columns
Included are many functions such as combining first and last name from two fields, capitalizing first letters in words, mathematical calculations, reformatting data with substrings, if-then-else logic, data types, and other functions which may not be available with SQL and Sybase.
Create a new column of summarized data
10. Select the Results/Computed Column option from the menu bar.
11. In the Computed Column panel, select the SubStr (substring) function; double click.
12. In the Definition box, the syntax for the substring function is displayed.
13. In the Definition box replace the variables (s, n, m) with the field name (obj), the start position (1), and the field length (1); the results should be: SubStr(obj,1,1). This will display only the first position of the object code field.
14. To display the one digit object code with a fixed extension (i.e.1xxx), add a plus sign and the extension characters, between quotation marks, to the formula from above. The results should be: SubStr(obj,1,1)+’xxx’.
15. Change the column Name to Obj Summary; OK.Create a new column of data based on conditional values.
16. Select the Results/Computed Column option from the menu bar.
17. In the Computed Column panel, enter in the Definition box the following if-then-else statement to highlight those rows which have a negative balance available:
if bal_avl < 0 then ‘Negative’ else ‘OK’.
18. In the column Name field, enter a new column name (Status); OK.Sort the Results Set
19. Drag the field (Status) to sort on, from the Request line to the Sort line.
20. Highlight the sort field on the Sort line and then select the Results/Sort Order option from the menu bar.
21. Select the Sort Now button on the Sort line.Add grand totals to the columns
22. Highlight the amount columns to be totaled (auth_bud, ytd, bal_avl) and select the Results/Subtotal Formula option from the menu bar. Select the Sum option.
Note: Counts and averages are also available.Add subtotals to the columns
23. Highlight the column (Status) to be used for the subtotal break. For correct results, this column should already be sorted.
24. Select the Results/Subtotal On Break option from the menu bar.Save the Results to an Excel spreadsheet
25. Select the File/Export Sheet option from the menu bar.
26. Enter the save file name and destination in the Export File panel.
Note: Different file types are also available.Save the Results Set for processing off-line at a later time
27. Select the Results/Retain Results option from the menu bar.
28. Select the File/Save As option from the menu bar.
29. In the Save File panel, enter the file name and destination for saving the query to run at a later time.
Create a pivot table
1. Select the Pivot1 Tab.
2. Select the View/Outliner option from the menu bar.Add amounts to the pivot table
3. Drag the amount fields (auth_bud, ytd, bal_avl) from the request line to the data items box in the Outliner.
Resize the fields to fit the data
4. Click above the column label, until a line appears on the right or select the Edit/Select All option from the menu bar to resize all the columns. Select the Format/Squeeze option to automatically resize the column.
Add labels to the pivot table
5. Drag the label fields (acct, Obj Summary) from the Request line to the side label box in the Outliner.
Swap the order for the major and minor columns
6. From the ‘dog ear’ at the bottom of the column, drag the first column (major) to the right of the second column (minor). The major and minor are now reversed (Obj Summary within acct is now acct within Obj Summary).
Move a label from the side to the top
7. Drag the ‘dog ear’ at the bottom of the first column (Obj Summary) to the top right side of the screen. This will pivots the column presentation from the vertical along the left side, to the horizontal along the top.
Move the amount labels from the top to the side
8. Select the Pivot/Preferences option from the menu bar. Select the Data Labels Down Side option.
Use the Outliner to rearrange the presentation
9. Drag the label (Obj Summary) from the top box in the Outliner to the left side box.
10. Highlight data fields (auth_bud, ytd) in the Outliner and then use the right mouse button to select the Remove option to delete an item from the Outliner.Start a new Pivot Table
11. Select the Window/New Pivot option from the menu bar. The Tab is now labeled Pivot2.
12. To rename the tab, double click on the tab and enter the new name in the Label field (Budget)
13. Drag the amount field (bal_avl) and the label fields (Status, Obj Summary) to the Outliner. Resize the fields to fit the data.Add subtotals to a column
14. Click above the column (Obj Summary) to be subtotaled and highlight the column.
15. Select Pivot/Add Total from the menu bar. The subtotal is now included.Hide / exclude selected data from display and totals
16. Click on individual records (6xxx, 7xxx, 8xxx, 9xxx) in a label column; hold down the Ctrl key to select more than one. When all items, which are to be hidden/excluded, have been highlighted, select Pivot/Hide Items from the menu bar. These records are now excluded from the pivot table and from the totals.
Focus on selected data
17. Click on individual record(s) (Negative) in a label column; hold down the Ctrl key to select more than one. When all which are to be focused on have been highlighted, select Pivot/Focus On Items from the menu bar. Only these records are now included in the pivot table and totals.
Restore all records
18. Select Pivot/Restore All Items from the menu bar to restore all hidden records.
Drill down into the details
19. Highlight the data label (3xxx) to drill into the details.
20. Press the right mouse button and select the Drilldown Into option.
21. Select the next detail level (obj) to display.Drill down into the details again
22. Highlight the data label (3000) to drill into the details.
23. Press the right mouse button and select the Drilldown Into option.
24. Select the next detail level (acct) to display.Restore all records
25. Select Pivot/Restore All Items from the menu bar to restore all hidden records.
Change the label descriptions
26. Highlight the label (1xxx) to be changed and double click.
27. In the Set Label panel, enter the new label description (Personnel Services); OK.
Repeat for: 2xxx (Supplies and Materials), 3xxx (Current Services), 4xxx (Fixed Charges), and 5xxx (Capital Outlay)
28. Click on the label and move it around to the desired order.
29. Double click on the column label (bal_avl) and change the label to Budget Balance.Group labels together
30. Hold down the control key and highlight at the same time all the labels (6xxx, 7xxx, 8xxx, 9xxx) to be grouped together.
31. Select the Pivot/Group Items option from the menu bar.
32. Double click on the combined label (*6xxx) and change the label to (Other Items).
33. Resize the columns to fit the new labels.Save the Results to an Excel spreadsheet
34. Select the File/Export Sheet option from the menu bar.
35. Enter the save file name and destination in the Export File panel. Different file types are also available.
Create a chart
1. Select the Chart1 Tab.
2. Drag the amount field (ytd) spending to the Y Values box in the Outliner.
3. Drag the label field (Obj Summary) to the x Labels box in the Outliner.
4. Drag the amount field (auth_bud) spending to the Y Values box in the Outliner.Change the chart type
5. Select the Chart/Chart Type from the menu bar. Select the Cluster Bar option.
Drill down into the details
6. Highlight the data label (3xxx) below the graph bar to drill into the details.
7. Press the right mouse button and select the Drilldown Into option.
8. Select the next detail level (obj) to display.
RETURN TO DATA EXTRACTS - OVERVIEW
