Create a Report Part 2: Building the Report

This is the second in a series of articles which will walk you through building a basic Insights report. We're going to pick up right where we left off at the end of part one, so we recommend you read that first if you haven't yet.

And just a reminder that a background in relational databases and SQL is highly recommended. There are many ways to go wrong in the report building process!

At the end of part one, we had finished building our query - we had gathered the data which we needed for our report and set up some basic filters to help it run faster. We are now in the report builder, ready to create the report.

All new reports have the same default formatting, including a header, detail, and footer. These are each a band. From top to bottom we have the:

1. Top Margin
2. Report Header: This is the overall header for the report which appears on the first page. We've added a title box and a logo. You are welcome to move the title, remove our logo and add your own, change fonts, etc. Note that this is a header for the report — you may wish to have a header on each page - we'll show you how to add that later in this article.
3. Detail: The detail band is where your information will appear.
4. Page Footer: This will appear at the bottom of each page. We've included a date generated field on the left and page numbering on the right.
5. Bottom Margin

The Detail Band

Let's start with adding information to the report in the detail band.

1. In the far right navigation, click the field list icon (or the "pancake stack" as we like to call it).
2. In the Field List box, click the arrow by sqlDataSource1 to see the query we built and then click the arrow by the query to see the list of fields which we had selected.
3. Hold down your control/command key and select the fields in the order you would like them in the report from left to right. When you have finished selecting fields, you can release the control/command key. Click any of the field names (must be a name, not the sides of the highlighted area) and drag to the detail band in the report. The fields will be added in the order you clicked and as a table rather than individual cells (this will make certain formatting tasks easier).
   Note that I have chosen date rather than start_date_time and end_date_time. date is from vw_functions and thus will be the function date. The other fields are from vw_events_lite and are the event date.
   
4. At this point, let's preview the report. Be warned though — we don't have any parameters, including date parameters, on this report. If you have a large number of events or functions, the report could take a while to build. Click the preview icon to preview the report.
5. In the preview:
   • We can confirm that we chose the correct fields; we see the information we expected to see.
   • We can see that we need to format the dates and times.
   • We can see that a header for each column would be useful, and we'd like it to repeat on each page.
   • We need to sort the functions by date and time.
   Click the designer icon to return to the report designer so we can update the report.

Formatting Date and Time Fields

1. To format date and time fields, select a field (it will be oulined in blue). Click the Properties icon in the far right navigation. In the Text menu, click the three dots in the Text Format String field.
2. In the pop up, scroll through the list of formats in the Types box, or type a custom format in the box below. A preview of the format selected/entered will be shown. Click OK. Follow the same steps for each date or time field. If you would like, you can preview your report again to check those fields.

Adding a Page Header

1. To add a page header, click away from any fields so that none are selected. In the actions menu on the right, click the page header icon.
2. A page header band will be inserted between the report header band and the detail band, and we're ready to add header fields. Since we added the fields to the detail band as a single table, corresponding header fields are easily created.
3. First, we're going to copy the detail band field table as the basis for our page header. To select the table, click in the margin of the detail band and drag to a field. The entire table will be outlined in blue. Copy the table (via the icon at the top of CTRL+C). Click in the page header band and paste (via icon at the top or CTRL+V).
4. Next, we'll update the fields so they're headers rather than fields pulling in data. Select the field, then add the text for the header in the Text field. Remove any information from the Expression field. You can then repeat this for each header field.

Sorting Functions by Date and Time

1. To sort the list of functions, first click in the margin of the detail band to select it. In the right sidebar, Sort Fields will appear.
2. Click the arrow to the left of Sort Fields to open the options, then click the + three times to add three levels for sorting.
3. In the first field (labeled Sort By), select date from the drop down. In the next field select start_time and in the last select end_time.

Preview the report again to check your work, and don't forget to save!

Great work! You now have a basic report with a page header. While this report is useful as it is, there are more elements of report building which will help your reports run faster and be more useful. We have an article for each element:

• Part 3: Parameters
• Part 4: Groupings
• Part 5: Calculated Fields and Format Strings