This series of articles will walk you through building a basic Insights report. This will allow you to see the usual flow of building reports, but this is not meant to be a comprehensive guide.
A background in relational databases and SQL is highly recommended. There are many ways to go wrong in the report building process!
In order to build Insights reports, you must have the Insights Admin role, which is added by our client success team. Please have the Sys Admin on your account contact us to have it added to your user account.
Reports are built in the Insights app. To access the Insights app, either:
- Go to the reports page and click the settings settings icon....
- ...or click your name in upper left corner and select Insights Admin.
In this tutorial, we'll be creating an Event with Functions report. To create a new report:
- Click the add plus icon in the upper right corner of the Managing Reports page.
- The SQL Data Source Wizard will load. Hover over Queries and click the plus to open the Query Builder.
- On the right hand side of the Query Builder, the Available Tables and Views section will be open, and you will see a list of the views which have been created for Insights. Each view contains a collection of fields available for any report you may build. Part of learning to build reports will be learning where the information you wish to report on is located within these views.
- To add a view to your report, click it in the list and drag it to the main workspace to the left and drop it. You'll now be able to see the fields in that view. You may add as many views as you need to the report, but the more views in the report the slower it will run. Some basic fields may be in multiple views, so you may not always need more than one.
- If you've added a view and realize you don't need it, select it (it will have a blue border) and click the trash can in the upper left corner.
- Some of our most commonly-used views are:
- vw_events
- vw_events_lite: This view is similar to vw_events but does not have room names, the venue name, or the number of event days. Because of this, it will run faster than vw_events so you'll want to use it whenever possible!
- vw_booked spaces
- vw_functions
- Since we are using more than one view, we will need to connect them so that the information in the different views is correctly associated — in our sample report, we need to make sure that the functions are associated with their events.
If you are using more than two views, you will need to create more than one join. Be aware, though: the more joins, the slower the report will run.We connect the views by creating a join between matching fields in the views. We'll be joining event_id in vw_events_lite on the left with event_id in vw_functions on the right.Both fields need to be visible on the screen in order to join them - the window won't scroll. You may need to drag a view up or down in order to get both fields visible.
- To create the join, click the field on the left and drag the line which appears to the field on the right.
The field you drag from will be your Left Operand and the field you drag to will be your Right Operand. Because of that, it's less confusing to drag from left to right whenever you can — you can always rearrange your views to get them in the right place. We'll discuss the importance of Left and Right Operands when we set the join type in step 11.
- If you need to remove the join for some reason, click on the join to select it (it will turn blue), then click the trash can in the upper left corner (or hit delete).
- Once your join is created, you'll need to set the type of join. Click on the join to select it (it will turn blue) - the Relation Properties menu will now appear.
- Notice that the view which you dragged from is listed in the Left Operand box and the view which you dragged to is listed in the Right Operand box below it.
Below the operand fields is a Join Type drop down with two options:
- Inner Join: With an Inner Join, there has to be data in both views for the data to appear in our report. In our sample report we have the events_lite view and the functions view. If we create an inner join, any events without functions will not appear in the report since there would be no data for functions.
- Left Outer Join: With a Left Outer Join, any information found in the Left Operand will be listed. If there's associated information in the Right Operand, it will be listed as well. Thus in our sample report, we'll get a list of all events. If they have functions, the function information will be listed. When in doubt, use a left outer join.
- Select your join type. For our sample report, we're going to select Left Outer Join. Note that when we selected this type, an arrow appeared on the join showing the flow from left to right.
- Next, we need to set any filters we would like for this report, such as only active events, certain event statuses, or certain venues or rooms. Create filters at this level as much as possible so that the report won't have to read every record in the database - it will run much faster.
If there are filters you would like the end user to be able to set as they run the report, you'll set those up as parameters (a common parameter is event dates). Filters (which we are creating now) are not accessible when the report is being run.Click Query Properties to open the section, and then click the three dots in the Filter field to open the filter editor.
- The options available allow you to create fairly complex filters; we are not going to dig into all these options in this article. We're going to add a single filter: only active events (not cancelled or lost). In the filter, this will be
is active equals true.
Hover over And and click the plus which appears. In the pop up menu, select Add Condition.
- Click the blue square. This will open a drop down with the views and available fields. We'll select is_active from the events_lite view.
- For our filter, we'll leave the green square on Equals. Clicking the green square will give you other options.
- The next box defaults to value. Click in the box (not on the arrow!) to focus the field and then again to open the value drop down. Select Yes. Depending on the field you selected, you may need to type values in this field.
If a field has multiple values or if those values can be edited by the sys admin (such as event types, task types, business classifications, staff assignments, etc), we recommend filtering using a parameter. A filter will have to be manually updated, but if the parameter is set up correctly the field values will automatically update if they are changed.
- You are able to add additional filters. Once your filters have been added, click OK.
- The final step in building our query (but not the final step in this article!) is to select the fields we would like to use — check a field to select it. Only select the fields you'll need in your report! All the fields you check will be included in the query even if you don't put them in the report, and they'll slow your report down.
If different views have a field of the same name, the second field checked will update its name to include the view name. See name in the functions view below - it's now vw_functions_name.
- Once you have added views, created joins, created filters, and selected fields, click OK in the lower right corner to save the query. You are able to return to this query builder to make updates at any time!
- You will be returned to the Data Source Wizard.
Notice that your query has been named after your left operand view. Don't worry, though, it does contain information from all views. If you'd like to rename the query, click the pencil at the right of its row.Click Finish.
- We are now ready to start building the report! Because this article is so long, we'll discuss that in part 2. Before we continue, though, save your work by clicking the menu in the upper left and selecting Save. This reporting tool does NOT have any kind of autosave, so you'll want to get used to doing this often!
