Basic Reporting Part II: Creating Custom Reports Using the Express View Interface
- 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 (duplicate) 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.
- If you'd like more room while you work, you can close the report navigation (Browse Reports) pane by clicking the X in the upper right of the pane. You can also re-open the navigation/browse reports window by clicking the Page icon on the left.
- 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 category 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.
- If you wish to apply filters or criteria to the report, click the “O” radial menu button in the upper left corner of the field you wish to filter on and select the funnel icon on the right of the radial menu 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 2017 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 Complete Fund made in 2017 or in 2015.
- 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 2017” 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 Smart Tags 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 radial button in the upper left of any field on the report (to sort by that field) and click the AZ button 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 radial button in the upper left of any field on the report (to sort by that field) and click the Group button.
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 clicking on the radial menu to the left of the topmost group header and select the Move Group to Innermost Level option or by clicking on the radial menu of a lower level group header and selecting the Move Group Up One Level
To remove a grouping, simply click the radial menu button and select Ungroup.
You can click on any group header to hide the details of that section. In this example, we have clicked on the Donation Date group header to hide the details of the various donations (donor name, birth date, and donation amount) made on each date. All we see when we run the report is the date and the summary date (# of donations made on the date).
Clicking on any header will collapse the data for just that one instance of the header (in this case, we clicked on the header for the first date, so we only hid the details of that particular date). To hide the details for all records in that group, click on the Group Expand/Collapse button on the upper left hand menu and select Hide All Group Content. Now the details are hidden for all dates.
To reveal hidden/suppressed date, simply click on the group headers again or click the Group Expand/Collapse button on the upper left hand menu and select Show All Group Content.
You can add summary date (totals, averages, and counts) to the report by clicking in the Report Totals field (for report totals) and/or on any group footer (for group totals).
You can add formatting to the report by selecting the Formatting and Style button from the menu in the upper right.
Select the drop down next to Theme to select from pre-set color schemes.
Select the drop down next to Style to change the report’s font, font color, background color, borders, cell alignment, and text wrapping.
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.)
Select the drop down next to Row Shading to apply shading (including alternate row shading) to the report.
Select the drop down next to Group Colors to change the color of the radial menu buttons and footer bar associated with each grouping.
To Run the report, click the Live Data button in the upper left hand corner.
To Save the report, click the Save (disk) icon in the upper left hand corner.
To Export the report to Excel, PDF, RTF (MS Word), or CSV (Excel with formatting removed), click the Export button in the upper left-hand corner.
To Update the report settings, including report name, description and export settings/options, click on the Report Settings button in the upper right-hand corner.
You can convert an Express View to an Advanced (Standard) report, by clicking on the Report Settings button in the upper right-hand corner and clicking the drop down next to the Create Advanced Report
You can easily add a chart to an Express View by selecting the Show Visualization button from the upper left hand menu or the graph/chart button in the upper right hand corner.
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.