Creating a Report Part 3: Parameters
In this article, we will be adding parameters to a report. Unlike the filters in the data source, parameters are accessible to users when they run the report. The most common parameter is a date range, but you can also create a parameter for venue, event type, staff assignment, etc.
Building a Parameter
When building a parameter, there are three steps:
- Building the parameter's user interface
- Building the parameter's filter in the data source
- Linking the two
For our sample report, we are going to add parameters for start and end date. Let's get started!
Step 1: Building the User Interface
- Make sure you don't have anything selected in the report, and then go to the Properties section of the sidebar by clicking the gear icon. Open Data > Parameters by clicking the arrows by each header.
- Since we're creating parameters for start and end date, we'll need to create two parameters. Click the + by Parameters twice.
- Click the arrow by each parameter to open it and add its information:
- Name: Add a name for the field. Since this is the field name, you cannot use spaces. For our first date parameter, we'll call it StartDate; the second we'll call EndDate.
- Description: The descripion is the label which displays for the user. In this example, we'll call the first parameter Start Date and the second End Date.
- Visibile: Leave this checked so the user is able to see the parameter.
- Multi-value: Since we are adding a date parameter, do not check this. This would be used if you were filtering by, for example, event type or venue: the user could then select multiple event types or venues.
- Type: This is the type of input people will put into the parameter. For a date parameter we'll select date.
- Value: Set a default value. For dates, the time is important! The default value for start times should always be 12:00am, and the default value for end times should always be 11:59pm. At this time, default date values cannot be conditional (such as "today" or "this month").
- Look-Up Settings: For dates, leave this setting on No Look-Up. If you are setting up a report with a different parameter (such as event type) please contact us for more information on this setting.
You have now created the user-facing side of the parameters. If you preview the report, you'll see the parameters and be able to click Submit, but the data will not be filtered until we complete steps two and three.
Step 2: Building the parameter's filter in the data source
Now we need to create a filter in the data source for the parameters we just built to hook into. We'll build that in the same place and in the same way as the filter we created back in the first article when we built the data source initially.
- Click the field list icon in the far right navigation. Click the pencil by the report's data source to open the data source wizard.
- In the Data Source Wizard, click Run Query Builder...
- Under Query Properties, click the three dots on the right of the Filter field to open the Filter Editor.
- In the Filter Editor, hover by And and click the plus. Select Add Condition in the pop up.
- Click the blue field and select the date field from vw_functions. Note that this is the function date, which is correct for this function report. For other reports, you may prefer to query by the event date, which would be pulled from vw_events_lite.
- Click the green field and select is between.
- When you select is between, start date and end date fields will appear. We want this filter to use the dates the user enters in the parameter, so click the arrow on each date and select Parameter.
- Click the field (which is now orange) and select Create new parameter.
- In each field, type the name of the new parameter (do not use spaces!). Note: We're using the same name as the parameters we created for the user interface, but these are separate filters which are not linked until we complete the third step in this process.
- Click OK to close the Filter Editor.
- Click OK to close the query builder.
We've now built the parameter UI and created the filter in the data source. Now let's link the two.
Step 3: Linking the UI to the data source
In this step, we're telling the data source filter's parameter to get its information from the user-facing parameter.
- On the Data Source Wizard, click Next.
- The parameters you created in the data source in step 2 are listed. Click the arrow by each one to access its settings.
- Set the type to Expression. A Result Type field appears and defaults to Date - do not change that.
- Click the three dots at the right of the Value field to open the Expression Editor.
- Delete the default expression which appears in the editor. Select Fields and click the arrow by Parameters. Double click the appropriate parameter to add it to the editor.
- Click OK to save the expression. Repeat steps two through six for the end date parameter.
- Click Finish.
Onwards! The next sections discuss grouping and formatting the data.