Dates serve one of the most important functions in many reports by enabling users to control what ranges of time the reports will be run for. Cosmos makes it easy to set date ranges when running reports, which is described in detail in this article.
Also, by leveraging the Cosmos Date table, users will find it easy and intuitive to filter on a wide variety of date scenarios without having to write code for date logic.
Here is a list of topics in this article:
Basic Date Filtering
When running a report, users will typically want to set a date range to limit the results being returned in the report. One of the most common ways to set this up in a Cosmos report is to create a Report Option in the report and allow the users to select which way they would like to filter the dates. The Report Option field, for example, could use the standard Date field in the Date table which represents all the individual dates that have been specified for the Data Model.
Below is an example of a Report Option where the user has set the Operator to Between and entered 4/1/2022 through 4/30/2022 as the date range. When the report is run, Cosmos will determine all the dates within that date range and apply this filter to any functions that are linked to the Report Option.
A full list of supported filters is located here: Working with Filters
The Cosmos Date Table
The Date table that is included in the Cosmos Data Model has a wide variety of fields that can be leveraged by users for easily filtering on the data that is needed.
The fields that are included as part of the Date table, with examples in parentheses, are:
- Date (4/15/2022)
- Day of Month (15)
- Day Name (Friday)
- Day of Week (5)
- Day of Year (105)
- Weekend (No)
- Week Number (15)
- First Day of Week (4/11/2022)
- Last Day of Week (4/17/2022)
- Week of Month (3)
- Month Number (4)
- Month Name (April)
- Month Year (April 2022)
- First Day of Month (4/1/2022)
- Last Day of Month (4/30/2022)
- Quarter (2)
- Quarter Year (Q2 2022)
- First Day of Quarter (4/1/2022)
- Last Day of Quarter (6/30/2022)
- Year (2022)
- First Day of Year (1/1/2022)
- Last Day of Year (12/31/2022)
Using the Date Table in Filters
Whereas the transactions that post in a database will always take place on a single day, users oftentimes want to be able to easily apply filters to ranges of dates, such as a particular month or a quarter.
By leveraging the Date table in Cosmos, users can intuitively apply filters based on what they want the report to return.
In the example below, a report has been built has a report option for the Month Year field in the Date table (e.g. April 2022) that will list out all customers that had sales within the specified date range and sum up the sales for each customer.
When the user goes to preview the report, the user can enter "April 2022" into the filter selection and run the report. Cosmos will then determine all the individual dates in April 2022 (e.g. 4/1/2022 through 4/30/2022) and will apply these filters to the sales transactions when running the report.
Another example of leveraging the Date table could be creating a Report Option for the Year field in the Date table (e.g.: 2022). By typing in 2022, Cosmos would then look up all the dates in that year and pass them as filters to the linked functions when the report was run.
Supported Date Formats
Currently, when filtering on specific dates, the following date formats are supported in Cosmos. In addition, dashes and slashes are interchangeable, so you can use either 1/1/2022 or 1-1-2022, for example.
- MM/DD/YY (or YYYY) (e.g. 01/01/22 or 01/01/2022)
- M/D/YY (or YYYY) (e.g. 1/1/22 or 1/1/2022)
- YYYY/MM/DD (e.g. 2022/01/01)
- YYYY/M/D (e.g. 2022/1/1)