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 - 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 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
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. 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.
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.
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 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. 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. 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.
In this tab, you can 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:This is the name that will identify the column in the report itself. By default we will enter the field name in this spot if it is not filled in.
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.
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.
Step 4: Conditions
To add a condition click the button, if you want to add and conditions save the first condition and the option becomes available.
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:
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.
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.
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.
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.