Report Builder

the report builder allows you to create custom reports using data warehouse tables think of the data warehouse as a simplified, organized set of data designed specifically for reporting instead of working with hundreds of system tables, the data warehouse provides a smaller set of structured tables that contain the most relevant information using the report builder, you can choose what data you want to report on filter results to show only what you need group and organize your data customize which columns appear in your report before creating a report, it’s important to understand how the primary tables work, since this is what everything in your report is built from understanding primary tables when you create a report, the primary table is your starting point it determines what data you’re working with, how your report is structured, and how detailed your results will be the data warehouse organizes tables into clear, purpose built datasets, so you don’t need to work with raw or complex database tables start by asking what am i trying to report on? if you want a complete snapshot of each student , use dw student overview – combines academic, financial, attendance, and program data into a single view if you’re working with students or admissions data , use dw students – core student and prospect information dw prospects – admissions pipeline data like stages and sources dw prospect stage history – tracks how prospects move through stages over time if your report is about courses, instructors, or scheduling , use dw courses – course details like schedule and status dw course instructors – instructor assignments dw instructors – instructor information dw sessions – academic terms and date ranges if you’re analyzing academic performance , use dw student academic summary – gpa, credits, and academic standing dw student enrollments – course level data including grades and attendance dw student grades – detailed grade records if you want to track student progress over time , use dw student programs – program level progress and completion dw student sessions – performance by term or session if your focus is attendance , use dw student attendance summary – totals and high level metrics dw student attendance – detailed attendance records if you need financial data , use dw student financial summary – balances and aging dw student account activity – transaction level detail creating a new report go to reports → report builder and click new report builder report to open the setup window report name (required) the name of the report as it will appear in the list use something clear and easy to recognize description optional note to explain what the report is used for data source where the report pulls data from select data warehouse for all new reports the production tables (legacy ) option is being phased out primary table the main dataset your report is built on this determines what data is available and how it is structured choose based on what you want to report on read only access roles that can view the report but cannot make changes read / update access roles that can edit the report location access locations that are allowed to access the report click add to create the report you will then be taken to the edit report builder report page, where you build and customize your report by adding fields, filters, grouping, and sorting legacy tables may still appear as a data source option temporarily, but they are being phased out existing reports that use legacy tables will continue to work, but new reports should be built using data warehouse tables details the details section contains the basic information for your report this is the same information entered when the report was first created report name the name of the report as it appears in the report builder list you can update this at any time description optional note explaining what the report is used for this is helpful for other users who may access the report primary table the main dataset the report is built on this cannot be changed after the report is created , so if you need a different table, a new report will need to be created read only access roles that can view the report but cannot make changes read / update access roles that can edit the report location access locations that are allowed to access the report click save report details after making any changes parameters parameters allow you to add user input to a report this lets users customize the results when running the report (for example, selecting a date range or filtering by a specific value) parameter name (required) the name of the parameter used in the report parameter label the label users will see when running the report this should be clear and user friendly parameter type (required) the type of input the user will provide options include boolean – true/false values date – must be in yyyy mm dd format integer / number – numeric values string – text input list – predefined list of values time – must be in hh\ mm\ ss format default value an optional value that will be pre filled when the report is run click add to create the parameter once added, parameters can be used in the report’s conditions to dynamically filter results joins joins allow you to bring in additional data from another table into your report joins are typically used for more complex reports most standard reports can be built using a single data warehouse table use a join when the data you need is not available in your primary table you need to combine information from two different datasets join type controls how data from the two tables is combined inner only shows records that exist in both tables left shows all records from your main (primary) table, plus matching data from the second table right / cross less commonly used and typically not needed for standard reports left table the primary table your report is built on this is automatically set and cannot be changed left column the field from the primary table used to match data this is usually an id or key field (for example, student id) right table the additional table you want to pull data from you can select from any available data warehouse table right column the field in the second table that matches the left column this must correspond to the same type of data (e g , student id to student id) join right table as a name for the joined table used internally in the report must be unique and contain no spaces conditions conditions allow you to filter your report results so only specific records are shown for example, you may want to show only active students show students from a specific program show records within a certain date range to add a condition, click add condition group a condition area will appear with an and / or toggle and an option to add conditions and all conditions in the group must be true or any condition in the group can be true then, click add condition to add a filter each condition is made up of the following table the table the field belongs to this is automatically set based on your report and cannot be changed column the field you want to filter on example student status, current program name, balance operator how the value is compared examples = equals != not equal > greater than < less than like contains text value the value you are filtering by enter the value you want to match parameter (optional) allows users to enter a value when running the report requires a parameter to be created in the parameters section first if no parameter exists, selecting this will show an error you can add multiple conditions within a group to further refine your report results use the and / or toggle to control how those conditions are applied group by group by allows you to organize your report results into categories, making the data easier to read and summarize for example, instead of seeing a long list of students, you can group them by program to see how many students are in each program to add a group, click add group by to open the grouping options group by table the table the field belongs to this is typically your primary table and usually does not need to be changed group by column the field you want to group your data by group by header (required) the label that will appear in your report column function (optional) applies a function to the selected column (advanced use) examples include formatting text, working with dates, or rounding numbers in most cases, this can be left as none grouping helps turn raw data into something easier to understand without it, your report will display as a simple list of records rather than organized results order the order section controls how your report results are sorted click add order to open the ordering options each order is made up of the following order table the table the column belongs to this is automatically set based on your report and cannot be changed order column name the field you want to sort by example student name, current program name, created date time order column function (optional) applies a function to the column (advanced use) in most cases, this can be left as none order direction (required) determines how the results are sorted ascending a → z, lowest → highest descending z → a, highest → lowest for example, to sort students alphabetically order column name student name order direction ascending this will display students in alphabetical order columns the columns section controls which data appears in your report and in what order to add columns, simply check the boxes next to the fields you want selected columns will appear in the selected columns area below you can then reorder columns drag and drop within the selected columns list edit a column click the edit icon (pencil) remove a column uncheck it from the list when editing a column you have selected, the following options are available column header the name that will appear at the top of the column in your report you can rename this to be more user friendly (e g , “student name” instead of student name) column function (optional) applies a function to the column (advanced use) in most cases, this can be left as none group aggregate (optional) used when your report includes grouping lets you summarize data (e g , count, sum, average) only needed for grouped or more advanced reports preview the preview section lets you test your report and review the results before saving it click preview to generate the report generated query shows the sql query being built behind the scenes this is mainly for advanced users or troubleshooting preview results displays the first 10 rows of your report lets you confirm your columns, filters, and data are correct report parameters (if applicable) if your report uses parameters, you’ll be prompted to enter them before previewing if none are set, this section will remain empty once you’re satisfied with the results, click create report to save it and make it available in the reports section create report after previewing your results, click create report to save and finalize your report this will open a form where you can configure how the report is saved and accessed report name (required) the name of your report as it will appear in the reports section description optional notes about what the report is for page orientation choose how the report is formatted when exported (portrait or landscape) output options select which formats the report can be exported as (pdf, csv, xml, txt) stretch with overflow automatically expands row height to fit content typically left enabled (disable only for csv only reports) read only access / read & update access controls which users or roles can view or modify the report can be left as default if not needed location access restricts which locations can access the report once everything is set, click create report to save it your report will now appear in the docid\ lvakeuylyu4ojzozihd7u section and can be run like any other report legacy tables the report builder is designed to use data warehouse tables legacy tables may still appear as a data source option temporarily, but they are being phased out existing reports that use legacy tables will continue to work, but new reports should be built using data warehouse tables