Search the Salsa Commons/Learn/Reports and Statistics

Build a Custom Report

Jake Patoski
posted this on December 04, 2012 11:10 AM

Building Custom Reports

Custom reports are a powerful tool enabling you to build reports connecting all the data that Salsa gathers.

The custom report builder uses visual user interface to construct a MySQL query -- you can even see the query at the bottom of the report as it builds.  Don't worry: you don't have to know anything about MySQL queries to use the report builder!  Users that arecomfortable with MySQL, however, may find that reference point helpful.  We've listed the MySQL clauses constructed by the different tabs here for that reason.

Step 1: Select Reports Package

  • Select the "Reports & Statistics" Package on your Salsa interface.
  • To create a new report, click "Create a custom report"
  • To edit a pre-existing report, click "List your custom reports"

 

Step 2: Properties

The first step in the custom report builder is to set the properties of report you're building. This is a decision point - the selections on this screen will define, and in some cases limit, the choices available to you for building your report. Once chosen, these settings cannot be altered for a particular report: you can only change them by starting a new report from scratch.  Once you click "save" on this step, the only editable fields in this tab are the name of the report and the type of report – but your 'object' selections are locked in.

The purpose of this initial choice is to balance simplicity and ease of use against the custom report builder's tremendous flexibility.  What you'll be selecting here is how you choose to organize your results (the "type of report" option), and the data you choose to report on (“Object chooser").

Name

Give your report a name for later retrieval (this name will display on the list of custom reports, and be the title of the report if you place it on a dashboard). The report can be renamed later.

Select the type of report you would like to create

Arranging data in a manner that's meaningful can be a challenge. Sometimes all you need is to simply sort your data. Often, you need more -- you need groups and functions to analyze and summarize your data.

You have two choices here:

Standard Report

For making complete lists of data. The Standard Report displays all records (within the confines of any limiting criteria). Use this report when you want a list of all entries for detailed micro-level reports: every individual supporter, every individual donation, every individual event registration, etc.

Aggregate Report

Summarizes data by reducing multiple records that have the same value for a particular field into a single record. It can then sum, count, or apply other aggregate functions to the details of the group to provide a more summarized data. Use this report when you want to see more macro-level analysis such as supporters registered by date, donations by tracking code, online actions by chapter, etc. 

Aggregate reports have some options not available in standard reports (mostly in the Columns tab, covered in Step 3); however, all standard report options are also available in aggregate reports.

Most of the tab-by-tab options will be the same for both the standard and the aggregate report. For those columns where there is a difference, a separate "aggregate report builder" section describes the added functions available with an aggregate report.

Choose objects for this report

You will select an object or objects on which you wish to report via a drop-down menu. Objects are the same as tables in our database – if you're familiar with importing data into particular tables, the objects correspond to those tables.  If, for example, you want to return groups information with your supporter information, that data is stored in separate ”tables” or “objects” in our database. Check out this brief table summary to see a list of commonly-used objects. We'll go into a good amount of detail below, but basically, "choose objects" will determine what data you have available in your report.

Regardless of your choice between a standard and an aggregate report, you'll be reporting on different tables in Salsa. You have two choices here:

Classic object chooser

The default option.  If you are new to custom reports, we definitely suggest beginning here. For most routine types of reports you will want to build this is the best choice. The classic object chooser gives you a list of options in familiar terms to choose the data you want to report on.  It then automatically chooses and relates the tables connected with the selected option. For instance, if you choose Donations you will not only have donation information but supporter information (the donation table links to the supporter table using a shared supporter_KEY value), and you can build a wide variety of reports incorporating those two objects. It is important to note that the options provided are not the only objects you can report on in Salsa or the only manner that these tables could be related.

Advanced object chooser

Use this option if you have some comfort with relational database tables. You're welcome to experiment - the worst-case scenario is a report that doesn't work - but choosing objects here is much more complex because of the relationships between the tables.  
The advanced object chooser will be necessary if you want to access data from a combination of tables that is not available in the Classic Object Chooser. This brief table summary outlines tables associated with each package. 

With the advanced object chooser, you start by selecting a single table. Salsa dynamically generates all the tables linked to the table you’ve selected - and offers you a second menu with all those tables, and then a third, and a fourth, and so forth, until you either have chosen all of your tables or select five objects. 

As an example, you might be interested in the subsequent donor performance of people who attended an event last year. Although events can connect to donations, that wouldn't be the case for these donations - they're not payments for registration, but are related only by virtue of having the same supporter affiliated with each. 

To generate this report, you would use the advanced object chooser and select Supporter_Event (the table joining supporters and events - essentially, instances of event registrations), followed by Supporter , and followed by Donation ... or else, the same sequence in reverse, starting with Donation .

Let's get Technical

As a general rule of thumb, every page and record you create in Salsa has its own table having the same name as the entity. For example, if you create a group called ‘Activists’, it will be saved in a table called Group. If you create a campaign it will be saved in a table called Campaign, and so forth. Because almost all features and activities in Salsa depend in some part on supporter activities, almost all tables are connected to the Supporter table. The connection between the supporter activity (such as group membership or action history) and the supporter record itself is done through a third table named “Supporter ”. For example, Supporter Group or Supporter Campaign.

The order in which you select your tables is probably the trickiest part of using the reports tool. Even though you are selecting the same tables, the order in which you select them could greatly affect the pool of data that will be available to you. This point requires you to be mindful and consider the details you want to include in your end result – there's a fine balance between having too many objects selected and muddling your results, and having too few objects selected and not receiving the data you want.

For example, if you select Supporter Group then Supporter, you can only report on supporters that belong to at least one group. All supporters that are not affiliated with any group are filtered out at this point. But if you had selected Supporter then Supporter Group, it would be a different story: you could report on both supporters that don’t belong to a group and also those who do. As a guide line if you want to include as much detail as possible, select the largest table first then the more specific table.

 

Step 3: Columns

 


In this tab, you can choose the fields you'll want to see in the report. The report itself will display a grid with one row per instance (for instance, every different donation) and the values you select here in columns (for instance, transaction date and amount).

Field

This is the actual database field, selected from a pull-down menu based on the objects chosen for the report.

Label

This is the name that will identify the column in the report itself.  These selections are optional.  If not explicitly named, each will default to a tablename.fieldname value, but more human-intelligible or context-specific labels (for instance, "Gift to Capital Fund" instead of "donation.amount" for a report on a certain class of donations) can make the report more readable.

Function

This menu offers a number of functions that could be applied to the results of the field in question.  If the report is a Standard Report, most of these functions are about formatting numerical values, especially date values.  If the report is an Aggregate Report the list of options is longer.  In addition to the functions available for a standard report, this menu in the aggregate report builder has a number of aggregating choices clustered under the "Group Functions" heading. Although it is not required that you make use of them, the existence of these functions is the raison d'etre of the aggregate report builder, allowing you to take one or more fields and gather totals or other calculated figures. When a "group functions" aggregator is chosen, you must also designate at least one field in the report to "group by" (below). ( Exception: The "Sum of Amount Minus Transaction Fee" function is a "Group Function.")

Group by

The "group by" box only appears if the report is an Aggregate Report. If box is checked for a particular field, it means it’s going to aggregate records which have the same value in that particular field in to one record. For instance, one might group counts of various things by dates - transactions by month, list signups by date, e-advocacy actions by date. To do this, add the relevant date field in the columns tab and check on "group by".  When multiple "group by" options are selected, it creates a sort of hierarchy of how the results will be organized defined by the sequence of the fields. That is to say, first records will be grouped by the top column in the list, and then each group will be grouped by the second column creating subgroup and so forth. 

Order

Use the up and down arrows under "order" to re-order the sequence of fields on the report.  The column listed first (at the top) will appear first (at the left) among the columns on the report.

Delete

The "delete" button removes the field from the report.

Tips

To list all the records with in a group, make sure include the unique identifier of the records to the group by list. For instance, to list all supporters within a group, first group by Group Key then by Supporter Key. If you don’t, your report will show one supporter record per group.

To summarize the data within a group, make sure the only column you have under the data fields section is the column you are going to apply the aggregate functions on. For instance  to report on the number of supporters in each group, you first group by Group Key, then group by Group Name (If you want to  include Group Name in your report) then on Supporter Key select ‘Count’ in the options listed under Function.

To create a donation report that includes transaction fees, add a column with field Amount and Function [one of the Transaction Fee functions].  The "Transaction Fee" and "Amount Minus Transaction Fee" functions are exactly what they sound like.  The "Sum of Amount Minus Transaction Fee" function is actually a "Group Function" -- think of it as a "Sum" function that's run on the values generated by the "Amount Minus Transaction Fee" function.

 

Step 4: Conditions

 


You obviously want to limit the amount of data that you want to review after you finish setting up the report because too much data will lead to too little focus. This is the point where you can set conditions to specify which data could be included or excluded in the report.  

Field

All the fields in the tables you have selected are available to you to choose from at this point even if they are not included in the column list of the report. Simply select the field you want to base your criteria on.

Operator

Operators are the mathematical and equality symbols, in more user-friendly terms, used to compare, evaluate, or calculate value of the field selected to the value specified in the next step, "Value".  It defines which subset of results the report will return.  This variable tells the report to select only fields with one of the following relationships to the value you enter.

Function

The "Function" menu offers a selection of choices much like the one available under "Columns", but perhaps of a more limited utility.  The functions will apply on the column selected during the search process itself, as opposed to the formatting of the information on the final report. There are two types of functions:

Functions that format the the data field to compare with the the value specified in ‘Value’. Meaning, the specified value under ‘value’ will be compared with the data entries as altered by the function. Function (Column_ Data_For_Record) operator Value.  Functions materially affect the results of a report especially when used on date fields. For instance in the condition, Date_Created less than or equal to 2009-05-01, ‘2009-05-01’ actually include hours, minutes and seconds (00:00:00 )implicitly, even if they're not enumerated.  And each record in the date_created field also includes a time-stamp of the time each record was created. The condition will result all records created on and before 2009-05-01 00:00:00, meaning it will not include records created after 2009-05-01 00:00:01 and after. If the "formatted date" function is applied, the time of day is removed from consideration: therefore, all the records created on 2009-05-01 and after will be returned.

Functions that compare the data field to an implicit value. Most of these functions work with date fields. Regardless of the value specified under ‘Value’, these functions compare the date data field to the current date. The results are always relative to the date the report was run. Function (Date_Field) operator Function(NOW)

Value

Paired with "Field" and "Operator", above, the value defines the condition -the cutoff line or search term that will delimit the report.

Tip—’ %’ is a "wildcard" character that can be used to search for partial matches in your database when used with the operator "like" (or "not like"). Within a search string, ’ %’  means match any number of occurrences of any character. For example the search pattern '90%' means match any value that starts with 90, regardless of any characters after that text. The condition would match the following: 90210, 90ABC, 90, 90--++, and 90@##.  On the other hand, '90%' would not match the following: 19015, A90125, and $$90. Although it might seem that’ %’ matches anything, there is one exception NULL.

Value Type

A value can be of two types, Constant or Variable.

  • User Variable: enables you to create report widgets that are responsive to your inputs at run time -- very useful for custom reports you will be running frequently with slightly different values as opposed to one-off jobs. 

    With one or more User Variables in place, the report when run (either by clicking "Run" in this report screen, or by placing the report on a Salsa dashboard) will display entry boxes for you to enter the value(s) to be used.  Once those variables are entered, the report will run based on the inputs.  If you're using this option, select a field, an operator, a variable label-- but don't set a value in the box above.   

    For example, the format of a report that returns records created within any given date range will be the same every time, but the variables -- in this case, a date range -- will be different.  If the condition on the Date_created field was set as the following; Date_Created, greater or equal to, formatted date (), and set the variable type to ‘User Variable’ and a second AND condition with the same settings with a Less than equal to operator.  During runtime, the report will require the user to set any date range and the results will be queried accordingly.

  • Constant (the default option):  this option sets the condition to be based on the value set in the ‘value’ box. 

Variable Label

When the value type is User Variable, the variable label "labels" the entry field with your own description during run time. You might want to use this as a prompt to help cue users (e.g., "Enter amount here, with no $" or "Show only donations since ...") or simply to make the report entry screen easier on the eye.

 

Step 5: Sort by

 


At this point, you pretty much have decided what data you want from the database, what is left is managing the results. More often than not, your data needs a little order. You can build a hierarchical sequence of up to three columns organize your results in alphabetic or numeric order.

 

Step 6: Filter

 

Filters are somewhat similar to conditions with one stark difference.  Conditions limit the data selected from the database where are filters are applied on the results. It might seem like the results might be the same if either one is used, but it is useful to note that they are not interchangeable.

One difference in application important to keep in mind is that conditions can apply to any field on the tables selected, while filters can only apply on fields selected under the columns tab. Here is a good guideline to follow when you are in doubt in which one to use: Use conditions to select records; use Filters to filter results based on the results of aggregate functions used with the groups by option. For example, to display only events with a certain number of supporters or more, set up a filter using the column for the Count field. The filter for Count >100 will only display Events that have over 100 supporters.

 

With Rollup

If the report is grouped by at least one column and a function is applied on at least one of the data fields, then you have the option to produce another row that shows the grand total of the values. The grand total super-aggregate line is identified by the value (empty) in the column the report is grouped. When the report is grouped by multiple columns, the report produces a grand total for each subgroup a except for the last group by column.

When you use Rollup, you cannot have any columns selected under the Sort By tab. In other words, Rollup and Sort are mutually exclusive.

Only bring back

You can restrict the results to a limited number of lines -- a useful tool (in conjunction with a Sort By term) for reports looking to identify the "top" or "bottom" performers in a set. Mind you, using roll-up with this option may produce results that are more difficult to interpret, because you have fewer contexts for understanding the super-aggregate rows.

Organizations with large databases and/or complex reports might also want to use Filters where they have a choice, since Filters generally run on a smaller data set than Conditions and are therefore generally a little faster.

 

Step 7: Filter by Tag

 

Because tags live on their own separate database table, a tag filter adds several lengthy terms.  Don't worry: The clean visual interface works like a charm.

Filtering by tag limits a report's results by one or more tags, applied to any table the report is searching.  Simply select the table, and select a tag.  Remember that because tags can relate to any object in the database, there might be many cases where the table selection is vital.

Multiple tags can be applied simply by additional clicks on the "add a tag filter" button. When multiple tag filters are implemented, they are conjoined by "and" -- that is, only data meeting every tag filter will be returned.  

 

Step 8: Graphs

 

The default "Normal Report" option provides the data set in a spreadsheet layout of rows and columns.

However, Salsa also offers a built-in graphical report generator that can turn those data sets into an attractive line graph, bar graph, or pie chart. 

This can be an especially useful tool for reports that are intended for a Salsa dashboard.  Simply select one of these options with the radio button, and the report will generate in graphical form in the future. (The graph itself also includes a button allowing the viewer to see the data grid.)

 

Custom Reports: Export Options

 


After running your report, there's a blue export link towards the bottom right of the report results. You can click this to export the results immediately, or you can schedule an export if there are particular exports you want to run regularly and automatically.  To schedule an export, click on the small blue "Export Options" link under the "Export to a file..." heading.


Change the settings of this form to:



    • Run and export this report > On a schedule

    • Start at this time (will pop up once schedule option is selected): Enter your date like 2008-04-30 and the time should be in military time. If you want the report to process and send to you right away and you have your report scheduled for "weekly," you'll need to enter your date as the date from which your report should first be run, so a week prior to the current date.

    • File header: (Normal should be sufficient, though you can change this)

    • Run the report: Select your frequency

  • Send an email: If you want the report to send to multiple emails, you can comma separate the addresses entered
 

Comments

User photo
Jan Nichols
NC Justice Center

The type on these pages is way too small.  Makes it really hard to read and get the value out of the info.

March 07, 2013 01:52 PM
User photo
Jake Patoski
Salsa Labs

Hi Jan,

You can bump up the size of your text in most browsers by holding down CTRL (for PC's) or Command (for Macs) on your keyboard, then while still holding it, press the + key until the size is a bit more manageable. Let me know if you need anything else.

 

Jake Patoski, Training & Learning Manager

March 07, 2013 01:56 PM
User photo
Jason Cooper
KaBOOM!

In order to turn off notifications, do I need to delete my report?

July 01, 2013 10:53 AM
User photo
Deanna Portero

A little broader than Jason's question, are there ways to edit export schedules (recipients, time, compressed status, etc)? Or see the export schedule, i.e. open a report and see who receives it?

September 11, 2013 09:48 AM
User photo
Jake Patoski
Salsa Labs

Hi Deanna:
Supporter Management package -> Manage Exports -> then click Details on any particular report. ("Active" status means it's a recurring / exporting report.)

Enlarge.

Jason - you don't have to delete the report; you can go to the same "Manage Exports" screen as above, then set the status of each export to "Inactive".

September 13, 2013 11:53 AM
User photo
Jane Suskin
Jewish Voice for Peace

RHi,

egarding setting Conditions, I'd like to be able to use some logic like if (a and b and c and k and l and m) or if (d and e and f and k and l and m) or if (g and h and i and k and l and m) then give results.

Do I have to list those all out over and over again, or is there a way to in an abbreviated way get

if  (k and l and m) and if ((a and b and c) or (d and e and f) or (g and h and i))  -?

 

Sorry, I am not a programmer, so I don't know the terms of art.

thanks

Jane

 

September 18, 2013 04:56 PM
User photo
Jake Patoski
Salsa Labs

Jane,

Unfortunately there's not a way to repeat - you'll have to enter the conditions for each set.

September 24, 2013 11:08 AM