A common use case for Parameters is to restrict a result set to a specified time frame. It is recommended that, before using the more obvious “Start Date” and “End Date” in their dataset query definition, a Nathean Analytics Developer consider making use of Date Ranges.
As Nathean Analytics views are saved with the parameters entered when they were last saved, a view of a dataset designed to use a start and end date will always return the source data for those specified dates. A date range parameter, however, allows a view to be saved with a dynamic date range (e.g. “Last Month”) which will automatically update the Start and End date the query returns.
This provides the ability for a Nathean Analytics Developer to produce Dashboards that feature a number of views saved with the same date range parameter e.g. a “Sales Year to Date” dashboard without the need to maintain the Start and End Dates of the views on that Dashboard; or to schedule reports to be run for “Last Week” or “Today” without any need for user interaction to specify the start and end dates of that time period.
Defining a Lookup dataset for use by the Date Range Parameter
The first step in configuring Date Ranges is to choose the intervals required and define them in a Lookup Dataset. The SQL behind such a dataset might look like the below if set to run against a Data Connector configured to look at the Nathean Analytics DataMart (SQL Server):
SELECT 'Today' AS DATE_RANGE UNION ALL SELECT 'Yesterday' UNION ALL SELECT 'This Week' UNION ALL SELECT 'Last Week' UNION ALL SELECT 'This Month' UNION ALL SELECT 'Last Month' UNION ALL SELECT 'This Year' UNION ALL SELECT 'Last Year' UNION ALL SELECT 'Year to Date' UNION ALL SELECT 'This Quarter' UNION ALL SELECT 'Custom Range'
Approaches to using Date Range Parameters
Once the date ranges to use have been determined, and a LOOKUP dataset developed to provide them to end users in a drop-down, the Nathean Analytics developer needs to decide how to use these date ranges within their Dataset queries. While each Dataset query could be individually developed to translate the date range into Start and End Dates this may result in having a large amount of code to maintain should any alterations to the Date Ranges be required.
The following examples detail two systematic approaches to adding Date Range functionality to a Nathean Analytics implementation:
- If the majority of Dataset queries are running against a single source database, it can often be easiest to create a stored procedure within that source database which converts these date ranges into Start and End Dates. Execute permissions for the stored procedure must be granted to the database login used by the Nathean Analytics Data Connector for that database in order for this approach to work. See example 1 for a sample solution.
- If the query is running against a Nathean Analytics Datamart, a translation table kept up to date by another Dataset could be used instead. (see example 2)