BASIC REPORTING PART II: Report Layout (REQUIRED)

BASIC REPORTING PART II: Report Layout (REQUIRED)

1. After determining the purpose or intended use of your report, the next step is to determine the layout of your report. It may seem counter-intuitive to be thinking about the report layout before you've even written the report, but it's actually the second step in report creation because the different layout tools are siloed in the reporting area as different report creation interfaces. Additionally, throughout Fundly CRM there are additional reporting tools that might make more sense to use in certain circumstances than using the reports module (such as Advanced Search, an out-of-the-box export, or a Smart Dashboard). You have to know how you want to lay out your report up front so that you pick the right reporting tool to achieve your desired goal.

   For example, you might want a matrix-style report that allows you to compare the number of donations raised each year per fundraising campaign. This information is very easy to lay out in a Cross-Tab report (top two images below), where you simply specify the two items you want to cross-tabulate (in this example, campaign and donation date) and what you want to tabulate at the intersection of those data points (donation amount). If, instead, you opt to start creating the report in the Express View interface, it will be impossible to get the report laid out like this. Similarly, if you create the report in the Advanced Search interface, it will be difficult, but possible - see the third screen shot below - but you will need to know how to use formulas/if-then statements to do it (which requires more advanced knowledge than just using the Cross-Tab interface). As you can see from the third screen shot, to get this type of matrix-layout in an Advanced Report, we need to use If/Then formulas to segment the donations by year per row/campaign. So much easier to do using the Cross-Tab interface where we just need to specify the row header, the column header, and what we want to tabulate! So it's critical to think about the report layout up front, so that you can pick the right tool for the job.

   

   The Cross-Tab Report Interface

   

   Creating a cross-tab or matrix-style report using the Advanced Reports interface

   

2. It's also important to think about the layout in terms of the intended purpose of the report. If you want to use the report as the data file for a mail merge, you don't want a report that has a lot of formatting, sub-totals, headers, etc., such as the screen shot below (this report would be wholly unsuited for such a purpose!).

   

3. Your instinct might be to take a default report that is formatting heavy but has the data that you need, export it to Excel, and spend time stripping out the formatting manually. This will *always* be more time and labor intensive than simply creating an unformatted report from scratch inside Fundly CRM. If you are handling report layout in Excel, then you will have to do all of that work every time you need to refresh the data, because you will have to rerun the report inside Fundly CRM, export to Excel, and repeat all the formatting steps. If, instead, you set up the report you need in the format that you need inside Fundly CRM, then the report will be laid out correctly from the get-go, and you won't have to spend the time reformatting, resorting, deleting duplicates, etc. every time you need the report. Similarly, if you need a report to calculate sub-totals, percent of total, remaining balance, etc. it's faster and easier to set the report up inside Fundly CRM to do that, rather than exporting raw data to excel and adding formulas every time.
4. Within custom reports, there are endless formatting possibilities that go far beyond what you can do in Excel in terms of making your reports suit their intended purposes. For example, here is the same report (an event profit or loss report), laid out two different ways. In the top screen shot, the data is laid out horizontally. It's easy to see that this layout makes cross comparison across events easier. In the second, the data is laid out vertically. This orientation makes it easier to digest the information about a single event. It would be difficult to use this format to cross-compare across events, but it would be good to use this format to present a summary statement of one particular event's revenue and expenses at a post-event review meeting or board meeting. The top layout can be achieved in both Express View and Advanced Reports. The bottom layout (vertical lay out of fields) can only be achieved in Advanced Reports. So, as we can see, knowing the purpose - and the format that will achieve that purpose - is important from the beginning when creating a report.

   

   

5. Generally, speaking, if you want a plain, unformatted data-focused report (facts and figures) that you will use for a mail merge, your best option is to use the Event Attendee Export, Financials Export, or the Search Results Export. In particular, the Search Results export is useful because it presents a non-duplicated list of contacts and the resulting excel file can be sorted by Household ID (so that all of the people who live in the same household are listed together). You can also export summary and drill down data from Fundly CRM Smart Dashboards. There are also some default reports within the system that are specifically designed to be mailing list reports. If you need to create a custom report to be used for a mailing list, use the Advanced Reports interface/tool (and NOT the Express View interface - there is no way to achieve one row per contact in Express View).

   

6. If you create a custom report and want to ensure you have only one row per person, you will want to use the Advanced Reports interface and GROUP the report by Contact ID. Keep in mind, that the Advanced Search produces, by its very nature, a non-duplicated list of contacts and that you can dump search results into a custom report in order to customize the output of the report (rather than using the advanced search export, which is a fixed template/fixed output and can't be customized). If you want a list that is GROUPED by household so that there is only one row per household, then you should use the Advanced Report Interface and group by the Household ID field (from the Household Summary table in the Contact category).

   

   

7. And, to be clear, a report that contains just facts and figures (a "date-focused" report) doesn't have to be plain and unformatted. You can, of course, make the report as formatting heavy as you like, such as this example. When creating a report that is meant to be read/absorbed or printed, there are endless formatting options to help make the report easier to read - shading, alternate row shading, row numbers, borders, sub-totals, the ability to add static text, and more.

   

8. If you are looking to create a comparison report, such as a report that compares the amount raised per campaign per year, you can do so in any of the custom reporting interfaces (Express View, Advanced Reports, Cross-Tab Reports, and Dashboard Reports). You can also get comparison data from our Smart Dashboards and from a variety of default reports.

   

9. Keep in mind that comparison reports can contain drill down data (such as the list of people who donated to your organization in 2016 and the list of people who donated in 2017, presented side by side), just summary data (such as the total amount raised from individual donors in 2016 compared to 2017), or both. When thinking about your report's purpose and layout, it's important to know which level of detail you want - all the nitty gritty individual pieces of data or just the totals/summary or both?

   

10. If you want a report that helps you understand WHY things happened (for example, WHY you raised more money in 2017 than 2018) (perhaps because you asked more people for donations, perhaps because you better utilized social media and online giving in 2017, perhaps you aimed to raise less money/had a lower goal in 2018, etc.), these reports can best be achieved via Advanced Reports, Linked Reports, and the Dashboard Reports interface (pictured below) because these interfaces give you the ability to cross-compare different reports and completely different data points and to even add manual data points (for instance, there isn't any place in Fundly CRM to put the goal # of new members you are targeting each year, but on an Advanced Report, you could add that as a static (number) data field and then use that field to calculate your % of goal number of new members you had each month).

    

11. Reports can be laid out, generally speaking, however you like. The data fields can go horizontally across the page, as illustrated in these screen shots (of two different reports). This can be useful for mailing list reports and comparing across items (across contacts, events, campaigns, etc.).

    

    

12. Or the fields can be placed to go vertically down the page as we have done here with these two different reports (in the 1st and 2nd screen shots below). Generally, going vertically will make a report more useful for taking in the details of one record (one contact, one event, etc.) and less useful for comparing across items (across contacts, events, campaigns, etc.). Express View reports cannot be oriented vertically. The fields of an Express View can only be placed side-by-side/horizontally. Advanced Reports and Dashboard Reports can very easily be oriented vertically/allow placement of fields vertically. In the second screen shot below, we are showing edit mode of the 360 Report (which is shown in the first screen shot) to illustrate how going vertically is achieved - multiple rows can be added to the header, details, and footer sections of the report to achieve the effect.

    

    

    

13. Sometimes, a matrix style layout (similar to a Pivot Table in excel) is the most handy for what you want to know as in the example given in Step #1 (total amount raised per fundraising campaign per year). That is most easily accomplished using a Cross-Tab Report, but can be done in an Advanced Report as well.

    

14. Sometimes you might want to see multiple reports or data points displayed side by side/on the same screen. This can be done using a Dashboard Report.

    

    

15. When thinking about report layout, you also need to know if you want detailed data (the nitty gritty drill down into each individual discrete piece of data that makes up the total, such as this donation report that shows each individual donation that goes into each sub-total) or just a summary report (the second screen shot). All of our out-of-the-box export options (financial export, event attendee, and search results export) provide detailed data. They, however, cannot be used as summary reports (without exporting to Excel and doing a lot of reformatting and sub-totaling work). Our Smart Dashboards provide both summary and detail data. Express View and Advanced Reports can both display either detailed and/or summary data. Cross-Tab Reports display only summary data. Here we have presented screen shots of the same report - Donations per Campaign. In each pair (one taken of the report in Express View, one taken of the report in Advanced Reports), in the first screen shot, the details are exposed (along side the sub-total/summary for each campaign). In the second, the details have been hidden.

    

    

    

16. Please Note: in the second screen shot above you can see why a mailing list report cannot be done in the Express View interface. Even when the details are hidden, we still end up with three rows per group. There is no way to hide the header and sub-total rows so that you only get one row per group (even if the group is contact name or contact id or household id). So that is why Express View should not be used for mailing list reports.
17. If might be that you want to toggle back and forth between detailed and summary data - you might want to see a summary for ease of comparison but also see the drill down details of how the sub-totals are being arrived at; in this case, you can hide & unhide the report details to toggle back and forth between the two modes in both Express View and Advanced Reports. However, it is MUCH easier to do this in Express Reports - there is the ability to show/hide the details while viewing the finished report as well as in edit mode (top screen shot below of the toggle button). In Advanced Reports, you can only show/hide the details by going back into edit mode, hiding the details (second screen shot), and then re-running the report (not difficult, but it takes several steps and waiting for the report to rerun).

    

    

18. Alternatively, you could also create a a drilldownable report in which two reports - the summary report and the detailed report - are linked together as a clickable drill down. Clicking anywhere on the summary report will allow you to drill down into the details via a pop up box that displays the underlying details. Our Smart Dashboards have built-in drill down capacity; simply click on the three dots in the upper right hand corner of any dashboard chart or table and select Export Data to see the details (top screen shot below). You can also create Linked Reports using the Advanced Reports interface (the 2nd and 3rd screen shots below. In this example, clicking on any event name in the summary report will bring up the list of registrants for that event and their details; we clicked on Arts Day Event 2018 event to bring up the attendee details for that event as a pop up box that floats above the summary report). The option for creating a Linked Report in the Advanced Reports is show in the 4th screen shot below.
    

    

    

    

    

19. Another option for looking at summary and detail data together is to add a chart to a detail level report. You might want to add a chart of summary data to the top of a details report so that you have both pieces of information together. It's easiest to add a chart to an Express View, but they can also be added to Advanced Reports and Dashboards. Our Smart Dashboards and Basic Dashboard also display data in chart/graph format.

    

20. Once you have determined which reporting tool is going to give you the type of report layout that you want, it is much easier to get the results that you want because you will be picking the correct tool for the job. We have provided a chart of the various reporting tools inside the system and the various features of each to make picking the right tool easier.
21. If you determine that you need to create a new custom report, the next few tutorials in this section will provide an overview of the steps for creating a report from scratch via each of the various reporting tools.