Search the Salsa Commons/Learn Salsa Basics/Reports and Statistics

Build a Custom Report

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

Overview

When to use it

The custom report builder uses visual user interface to construct a MySQL query. Don't worry: you don't have to know anything about MySQL queries to use the report builder! You will be able to use the reports tool to pull information from any section of Cosm.

How to get there

  • 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

select_reports_package.png               create_new_report.png

 

Create a custom report

The first step in the custom report builder is to set the properties of report you're building.

This is a decision point. 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 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.

uneditable.png

Name: Internal reference name, can be modified at any time.

Report Type

  1. 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.
  2. 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.

Choose objects for this report

Select an object or objects on which you wish to report via a drop-down menu. Objects = database tables. Check out this brief table summary to see a list of commonly-used objects.

Once you choose objects (or tables), only data stored on those tables is available in your report.

R5.png

Classic object chooser

The default, simpler option. 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 the related tables.

EXAMPLE:  If you want to build a report listing all donations (which also includes the name, address, and email address of the supporter making the donation), choose Donations and the supporter table is automatically selected for you.

classic_chooser.png

Advanced object chooser

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. 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 the tables you need (up to five). 

Rgif1.gif

EXAMPLE:  To build a report on subsequent donor performance of people who attended an event last year, you would use the advanced object chooser and select Supporter_Event (the table joining supporters and events), followed by Supporter , and followed by 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. 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.

mapping_table.png

The order in which you select your tables is probably the trickiest part of using the reports tool. When using the Advanced Object Chooser, you:

  • Choose an initial table and are presented with a second drop down list
  • The values of the second drop down list are now filtered. Only those tables which are related to the initial table are present
  • Additionally, the data on the secondary table is also filtered so that only records which also appear in the initial table can be reported on (see example).

EXAMPLE:  If you initially select supporter_group, then select the Supporter table, 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 guideline if you want to include as much detail as possible, select the largest table first then the more specific table.

Create a Custom Report:  Columns

In this tab, choose the fields you'll want to see in the report. Click the button as many times as you would like to add fields to the report.

Rgif2.gif

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

Label: Type whatever header you'd like to see at the top of the column. The field name will display by default.

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.

R6.pngR7.png

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. 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.

Delete: The button removes the field from the report.

EXAMPLE:

In this sample report, note that we've grouped the records by Supporter KEY. This means there will only be on row in the report for each supporter record. Since we're reporting on supporter donations data and supporters could have made more than one donation, we need to apply a function to the Amount value. Here we've chosen to Sum the donation amounts.

Sum_of_Donations_by_Supporter.png

When we click the button, the report looks like this:

the_report.png

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.

Create a Custom Report:  Conditions

There are two types of conditions:

  1. And conditions
  2. Or conditions

To add the first condition, click the button. When you add additional conditions, you can choose whether they're "and" or "or" conditions. The option to add "and" conditions isn't available until after you save the first condition.

and_vs_or_conditions.png

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. They are used to compare, evaluate, or calculate the 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.

Example:

condition_example.png

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.

R8.png

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"). For example, 90% means any value that starts with 90, regardless of any other characters after that text. The condition matches the following: 90210, 90ABC, 90, 90--++, and 90@#

wildcard.png

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

  • Constant (the default option):  This option sets the condition to be based on the value set in the ‘value’ box. When you run the report, you'll only return data that meets the constant condition(s).
  • User Variable: Enables you to create report widgets that are responsive to your inputs at run time. 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.  

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.

user_variables.png

These user variables look like this when you run the report:

user_defined_2.png

Create a Custom Report:  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.

R10.png

Create a Custom Report:  Filter

The major difference between conditions and filters:

  • Conditions limit the data selected from the database
  • Filters are applied on only those results which remain AFTER the conditions are applied.

A secondary distinction:

  • Conditions can apply to any field on the tables selected.
  • Filters can only apply to 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.
R11.png

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, just enter a value in the box.

Create a Custom Report:  Filter by Tag

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.  

R12.png

Create a Custom Report:  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.

R13.png 

Next: Schedule an Export

 

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 7, 2013, 1: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 7, 2013, 1:56 PM
User photo
Jason Cooper
KaBOOM!

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

July 1, 2013, 10:53 AM
User photo
Deanna Portero
National Organization for Rare Disorders

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, 9: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, 4: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
User photo
Paige Richardson
Byron Mallott for Governor

I'm trying to get our contributors check # to export in a custom report format.  Any help would be great!

Paige

July 17, 2014, 8:00 PM
User photo
Zak Fowler
Salsa Labs

Hi, Paige -

If check number information was added to Salsa manually (through the "Enter Donations" screen in Salsa for example), that check number information is stored in the Order_Info field.

July 18, 2014, 1:09 PM
User photo
Jenni Choi
National Senior Citizens Law Center

How do you include groups and tags in a report? I'm trying to run a report on donors who have a certain tag. I want the report to list their names, addresses, donation history, tags, and groups.

September 17, 2014, 9:41 PM
User photo
Rebecca Wyatt
Salsa Labs

Jenni,

The report you're describing is pretty complicated (it has to pull in data from a lot of different tables).  If you need to see a lot of different types of data in the fewest number of reports possible, submit a support ticket and let our experts help you.

September 18, 2014, 9:16 AM
User photo
Ashley Downend
Story-Based Strategy

Hi! Where do I find custom reports on the new interface? Thanks!

November 6, 2014, 6:38 PM
User photo
Rebecca Wyatt
Salsa Labs

Here you go!

reports.png

November 17, 2014, 5:36 PM
User photo
Ashley Downend
Story-Based Strategy

Hi Rebecca,

My Dashboard stops at Supporter. There is no Chapter or Reports on my toolbar. I can access the reports through the old interface still. Thoughts?

November 18, 2014, 12:42 PM
User photo
Rebecca Wyatt
Salsa Labs

Hey Ashley,

The Reports & Statistics package wasn't enabled on your account.  I added it - you should be good!

November 18, 2014, 2:55 PM
User photo
Hannah Smith
Pennsylvania Farmers Union

Is it possible to print out an event report that includes the number of guests?

December 1, 2014, 1:18 PM
User photo
Rebecca Wyatt
Salsa Labs

HI Hannah,

You could create a report for this, but the simplest thing to do would be to export an attendee list.  To do this:

  • Navigate to the Attendees tab of your event
  • Pick the file type you'd like (usually Excel)
  • Click on the Export Attendees button

export_attendee_list.png

December 1, 2014, 3:34 PM
User photo
Amelia Hays
Bronx Defenders

Regarding setting conditions, I have run into a recurring problem where I will set a transaction date as less than or equal to for donations that I am trying to capture in a report, and the report that runs will not reflect those conditions. For example, for Transaction Date, I'll set it as less than or equal to 2015-03-26, to include donations on and before March 26th. However, it doesn't usually capture donations on March 26th unless I up the condition to less than or equal to 2015-03-27, a day after. This also happened for a date that was even less than the greatest date. I just entered in a cash donation, and put the transaction date as yesterday, 3-25-2015. On conditions, the Transaction Date is still set at less than or equal to 2015-03-27, and yet it's not capturing this new donation I just entered with the transaction date of 3-25-2015 when I go to re-save and run the custom report to include this newly added donation. Any idea why this is happening?

March 26, 2015, 4:02 PM
User photo
Rebecca Wyatt
Salsa Labs

Hi Amelia:

Here's the crux of the problem. When you enter 2015-03-26 in the less than or equal to field:

  • You get all values less than or equal to 2015-03-26 00:00:00
  • From your comment, it looks like you're expecting all values less than or equal to 2015-03-26 23:59:59

The solution? When entering dates in your user defined fields, enter one date less than you'd expect in your less than field (and also one day after the end date you're looking for).

March 26, 2015, 4:18 PM
User photo
Warren Gill
Religious Coalition for Reproductive Choice

I'm trying to create a report from email blasts that has the following columns: 

Email Blast Reference | Date sent  | Number of emails Sent  | Number of emails open  | Donation Amount

I want this for every email blast.  The only part I can't figure out how to add is the donation amount. I can only seem to figure out how to make a report for one email. 

I want it to look like this:

End of the Year Rqst  |  2014-12-31  |  10,304  | 1,601  | $1,200

#GivingTuesday  |  2014-12-3  |  10,354  | 1,211  | $490

Halloween Rqst  |  2014-10-31  |  10,439  | 1,525  | $660

How do I make that happen? 

April 20, 2015, 6:08 PM