When developing reports in Nektar, sometimes we can be faced with situations where Report performance starts to degrade and reports take a long time to load. When reports are based on a Nektar data set (from data set manager) there are some strategies you can use to help speed things up. One of these strategies is by using filters.

When a data set is built in Data Set Manager it has no filters by default. What this means is that when the data set is run, its going to return all data (for all time) for the columns that we have added to our data set tables. 99% of the time this is more data than we need for our reports, unless the report is an "all time" report. In the case when its not, we can use a date filter to reduce the amount of data that is returned.
​
For instance, if we have a report that only needs data from the last month, we could just build a data set with no filters, and then filter the data once its in the report. The problem with this approach is that every time you run the report you are loading ALL data for ALL time, and then filtering once it is already loaded into memory. This is not a good tactic for performance. Instead, we should add a filter (or multiple) filters to the data set so that only the data we require for the report is loaded.

We do this by going into the data set manager, clicking on the triple dot icon on the top right of our data set table(s) and clicking Filter. Next, choose a date field from our data set (in our example we will use Recorded Date since our dataset contains task data). Then, in our example, we will use a relative date comparison and choose to get data from 30 days back. This will auto adjust itself to the last 30 days every time the data set (and report using it) is run. In this example, we brought our data set from 200,000 rows of data down to 600 rows. And since this is all the data our specific report requires, load times shortened dramatically.
​

The concept of filters also applies to non date filters as well. In the example above, if we only need time cards where the foreman was "John Smith", we could again run with no filters, load everything into the report, then filter there. But as we know, this is not a performance friendly way of doing things. Instead, we can prefilter the data set in Data Set Manager so that we are only returning data that is relevant.

So in Example 1 we limited our data returned by specifying that we only wanted the last 30 days of data. But what if our situation requires a report where the user gets to specify what date range they wanted, and thus, the date filters for the data set would be unknown? You might think we would need to leave our data set unfiltered so that our report has access to all data for all time so that the user can then filter it within the report. However, there IS a better option - we can use Variable Filters.

In our report, we already have two variables: FromDate and ToDate. In it's current form, the report loads ALL data (since the data set is unfiltered) and then filters that data based on the user's choices for the FromDate and ToDate variables. This actually leads to the report loading all the data on first launch, then the user selects their variables and it loads all the data again - doubling our wait time.

To solve this, we can use Data Set variable filters. First we open up the data set and add two filters. Again, sticking with the RecordedDate column for this example, we set up the filters as below, but instead of choosing a relative date value, or a specific date, we are going to click the triple dot button and choose Use a Variable. Then, we put our variable names from the report. This has now caused the dataset to be filtered by the values that are selected in the report.

Now, when we load the report, it will attempt to load the data source. However, because no values have been selected for the FromDate and ToDate variables by the user yet, the report tries to load the data set but it is blank and therefore the initial load of the report is very quick. Then, once the user selects the FromDate and ToDate that they desire, the dataset is once again queried, but with filtered dates, and only returns the data necessary to satisfy the users request. This leads to a drastic increase in performance!