SJECCD Advanced Reporting Training Prepared by ASR Analytics, LLC
3
CONTENTS
Document Change History ........................................................................................................... ii
Contents.................................................................................................................................... 3
Introduction ............................................................................................................................... 6
Understanding Data Flow ............................................................................................................. 7
Conventions used in this document .................................................................................. 9
Report Design Patterns and Best Practices ................................................................................... 10
Simple Design Pattern ................................................................................................. 10
Separate Selection Design Pattern ................................................................................. 10
Combined Result Data Design ....................................................................................... 10
Report Design Best Practices ........................................................................................ 10
Migrating Reports to Another Universe ......................................................................................... 11
Learning Objective ...................................................................................................... 11
High-level Overview .................................................................................................... 11
Practice Steps ............................................................................................................. 11
Understanding and Working with Nested Filters and Subqueries ...................................................... 17
Query Filters .............................................................................................................. 18
Nested Query Filters .................................................................................................... 19
Nested Report Filters ................................................................................................... 21
Filtering Data Using Subqueries ..................................................................................... 22
Learning Objective ...................................................................................................... 25
High-level Overview .................................................................................................... 25
Practice Steps for Nested Query Filters ........................................................................... 25
Practice Steps for Building a Sub Query .......................................................................... 26
Creating and Using Variables ...................................................................................................... 27
Using If-Then-Else Logic in Report Variables .................................................................... 28
Building a Dynamic Title ............................................................................................... 29
Time Functions ........................................................................................................... 29
Character Functions ..................................................................................................... 30
Learning Objective ...................................................................................................... 32
High-level Overview .................................................................................................... 32
Practice Steps ............................................................................................................. 32
Calculation Contexts ................................................................................................................. 34
Using the IN Context Operator ...................................................................................... 37
Using the ForAll Context Operator.................................................................................. 39
Using the Where Conditional Context Operator ................................................................ 40
Using the In Report Context Operator ............................................................................ 41
Learning Objective ...................................................................................................... 43
High Level Overview .................................................................................................... 43
Practice Steps ............................................................................................................. 43
Advanced Formatting ................................................................................................................ 46
Formatting Tables ....................................................................................................... 46
Conditional Formatting ................................................................................................. 49
Breaks ....................................................................................................................... 50
Sections .................................................................................................................... 53
Outline (fold/unfold) .................................................................................................... 53
Hands-on Exercise Learning Objective ............................................................................ 54
High Level Overview .................................................................................................... 54