Query Developer User Group

1. Query Developer User Group May 3, 2006

2. QM Form Options in Excel • What are the options in QM? • How do you duplicate those options in Excel?

3. QM Form Options • Column Formatting • Usage • Width • Edit • Heading

4. QM Form Options • Page Formatting • Page Header • Page Footer • Break Text • Final Text

5. QM Form Options • Column Formatting options in QM • Usage (frequently used) • Break • Average • Count • Sum

6. QM Form Options • Column Formatting in QM • Usage (infrequently used) • Omit • First • Last • Minimum • Maximum

7. QM Form Options Example • Find the count of all current students having an overall GPA of 3.0 or better. Subtotal by department within college.

8. QM Form Options Example • The SQL: SELECT COLL_CODE, MAJ1_DEPT, Count(*) FROM DATACOLL.STDT WHERE CURR_STDT='Y' AND COMB_UG_GPA>=3.0 GROUP BY COLL_CODE, MAJ1_DEPT ORDER BY COLL_CODE, MAJ1_DEPT

9. QM Form Options Example • The QM Form: Column Heading Usage Edit Seq Indent Width COLL_CODE COLL_CODE BREAK1 1 2 4 MAJ1_DEPT MAJ1_DEPT 2 25 4 SEL1 COUNT_OF_STUDENTS SUM 3 2 18

10. QM Form Options Example • The QM Report (partial listing) COUNT COLL MAJ1 OF CODE DEPT STUDENTS ---- ---- ------------------ AL AR 297 CM 176 EN 306 FL 76 MJ 189 MU 209 TD 136 ------------------ count of students in college AL 1,389

11. QM Form Options in Excel • The results in Excel:

12. QM Form Options in Excel • To change the column heading • Click the cell, press F2, and edit • Click the cell, click the formula bar, and edit • Double click the cell and edit • If you want stacked column headings, you have to format the alignment of the cell • Right-click the cell, select “Format cells”, click on “Alignment” tab, and check “Wrap text”

13. QM Form Options in Excel • You’ll have to (probably) change the width of the column to make it come out correctly. • Move the cursor over the column identifiers until it changes to a vertical bar with 2 arrows coming from it. • Click and drag to the proper width OR • Double click and Excel will automatically adjust the column width to the widest column value

14. QM Form Options in Excel • To set the break level: • Data -> Subtotals

15. QM Form Options in Excel

16. QM Form Options in Excel • To add more than 1 break level • Add the outermost level first • Add the next level • Uncheck Replace current subtotals

17. QM Form Options in Excel • First – Ctrl + Home • Last – Ctrl + End • Page Formatting • View -> Header and Footer • The pulldown has pre-defined values • Click Custom Header or Footer to set your own • Final Text – go to the end and enter your final text

18. QM Form Options in Excel • Omit – Click the column identifier and select Hide from the menu • To view, move the cursor over the column identifier until it changes to 2 vertical bars with 2 arrows. Right click and select Unhide

19. QM Form Options in Excel • COUNT – double click the cell you want and enter =COUNTA(1st cell:last cell) • SUM – double click the cell you want and enter =SUM(1st cell:last cell) or • Click below the last data value for the column and click the Autosum button ( Σ ) • AVG – double click the cell you want and enter =AVERAGE(1st cell:last cell)