Basic Reporting Part IIIa: Creating Custom Reports Using the Express View Interface
EXPRESS VIEW INTERFACE WEBINAR RECORDING (AUGUST 2021)
EXPRESS VIEW INTERFACE WRITTEN TUTORIAL
- Express View (different from an Express Report) is a slimmed down “drag and drop” user interface that makes the creation of basic reports fast and simple. The user interface is simple and direct and all functions are on one screen.
- About 80% of reports can be created using the Express View Interface. For some types of reports, they can only be created using an Advanced Search or by using the Advanced Reports interface. See this chart comparing reporting options to determine which tool is the best one for the job before beginning a new report from scratch.
- It is always best, before writing a custom report, to check the default reports to see if there is a report that already does what you want. At the very least, there may be a default report that you can copy and then edit in order to save yourself some time.
- To create a new custom report from scratch using the Express View user interface, from the Main Menu, select Reports.
- From the Reports Index page, click the “+” button to create a new report and then select Express View.
- From the list of fields on the right, select the fields you wish to add to the report by clicking on them and dragging them onto the report. For example, if you wish to add Contact Name to the report, click the triangle to the left of the Contact Tables to see a list of tables, click the triangle to the left of the Contact table to see a list of fields in that table, click on the Contact Name field and drag it to the canvas on the right or double click on it to add it to the report as a viewable/output field.
- Continuing add fields until you have all the fields you wish to see on the report.
- You can reorder the fields/columns, simply by dragging and dropping the fields in the report canvas area to a new position or click on the three dots on each field and to see the movement and more options.
- If you wish to apply filters or criteria to the report, click the three dots button /icon in the upper left corner of the field you wish to filter on and select the Filter By This in the options OR select the funnel in the upper right corner of the screen. Please Note: In the Express View interface, ALL OF YOUR DATA (in your database) is in your report until you filter it out. Adding fields to the canvas (fields you will see on your report) does NOT filter out any data. So, for example, in the report pictured below, since we have not applied any filters to this report yet, when we run this report we will see ALL contacts in our database and any donations they have made. Simply showing donation fields does not mean the report is limited to donors/contacts who have made donations. To limit the report in that way, we'd have to add filters telling the report we only want to see contacts whose contact type is donor or whose donation amount is greater than $0, etc. (when using Express View, think of your report as a block of marble and you are using filters to chip away the excess marble to leave behind just the stuff you want).
- The Filters menu will open on the right and you can add your filters here.
You can add formulas to the report by clicking on Add Formula in the Fields pane on the left.
- In the upper portion of the Filters window, you can add “AND” criteria (results equal to criteria #1 AND criteria #2 AND criteria #3 etc.). In our example below, we’ve added criteria to show us donations that were made during 2020 AND that are greater than $500.
- In the lower portion of the Filters window, you can add “OR” criteria (results equal to criteria #1 OR criteria #2 OR criteria #3 etc.). In our example below, we’ve added criteria to show us donations that were made in 2017 OR in 2015.
- You combine “And” and “Or” filters by including criteria in both the upper and lower portions of the Filters window. In this example, we have asked to be shown all donations greater than $500 made to our General Fund made in 2017 or in 2020.
- You can also have the report show you the top X number of values that meet your criteria or the bottom X number of values that meet your criteria (such as “show me the top 5 donation amounts made in 2020” or “show me the five youngest donors in my database” by using the Top/Bottom tab.
This will add a blank column to the report with a formula editing panel. You can either a) type a formula directly into the editing pane or b) use the Formula selector on the right to add a formula.
You can use the formula field to, for example, subtract one column from another by selecting the fields from the fields list on the left and double clicking or dragging them to the formula panel to add them and manually add/type a minus sign between them. In the example pictured below, we've subtracted the Transaction Paid from the Transaction Amount to find the unpaid balance remaining.
To select functions from the Formula pane on the right, click the triangle to the left of the formula category (the functions are grouped together by type of function to make finding them easier) to expand/expose the function options in that category. Then click and drag the function to the Formula box or double click on it to add it.
You will then need to tell the system what field to apply the formula to. In this example, we are using the Get Contact types function, which will roll all smart tags on a record into one cell divided by commas. We need to tell this function what field to apply this function to - in this case, we need to add the ContactID field (e.g. "roll up the tags per person") by dragging it from the panel on the left to the "Argument" section of the Formula panel.
To adding Sorting to your report, click on the Three dots Icon in the upper left of any field on the report (to sort by that field) and select to Sort Ascending or Sort Descending or click the Sorts tab in the upper right.
You can sort by multiple fields by dragging fields from the field list on the left to the Sorts window, reorder the sort order by dragging the fields listed in the Sorts window to a new position, and change the sort order of any field from ascending (clicking on the asc button) to descending (clicking on the desc button).
To adding Grouping to your report, click on the Three dots Icon in the upper left of any field on the report (to sort by that field) and click the Group button OR Drag the field you want group by in the Add Group Field.
You can group by multiple fields, by selecting the Grouping option on multiple fields/columns. The group headers will be stacked and indented to indicate the hierarchy/grouping order.
You can change the grouping order by dragging the grouped field to the desired position.
To remove a grouping, simply click the Three Dots Icon and select Ungroup.
You can hide the details of that section of the report by unchecking the Details Row checkbox above the report layout. In this example, we have grouped the report by fund and subgroup by campaign, the donor name and other details will be hidden if the Details Row is unchecked. All we see when we run the report is the Fund, Campaign and the total donation amount.
You can check./uncheck these report view options in the edit mode or even in the run version of the report.
You can add summary data (totals, averages, and counts) to the report by checking the Report Totals and/or Group Total check boxes in the report view options.
You can change the theme of the report and the font style by clicking on Canvas on the upper left.
Select a field in the Section Panel on the right will show you other formatting options where you can edit the display name, text alignment, and font style.
Select the drop down next to Data Format to change the format of data in the selected cell (to format data as a number, text, currency, etc.)
To Run the report, click the RUN button in the upper center.
To Save the report, click the Save (disk) icon in the upper center of the screen.
To Export the report to Excel, PDF, RTF (MS Word), or CSV (Excel with formatting removed), click the Export beside the Save button.
To Update the report settings, including report name, description The drop down arrow beside the save button. To update the export options, click on the drop down arrow beside export button.
You can convert an Express View to an Advanced (Standard) report, by clicking on the Export drop down button, click on Advanced Report.
You can easily add a chart to an Express View by selecting the Visualizations button from the upper right hand menu or the graph/chart button in the upper right hand corner OR click on the Charts on the upper center of screen.
You can choose whether to show the chart above the data table, below the data table, or hide the data table and just show the chart by selecting from the icons in the lower left of the chart.
You can change the chart formatting using the Type, Data, and Appearance tabs in the Visualization panel on the right.