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.
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.
Name: Internal reference name, can be modified at any time.
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.
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.
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.
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).
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.
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.
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.
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.
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.
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.
When we click the button, the report looks like this:
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:
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.
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.
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.
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@#
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.
These user variables look like this when you run the report:
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.
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.
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.
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.
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.