Cognos Tutorial - Date Functions

Transcript

1. Cognos Tutorial: Date Functions 3/12/2013
2. 1 http://imgs.xkcd.com/comics/iso_8601.png
3. 2 Date Filters  Format: YYYY-MM-DD  Single Date Filter – Use ‘=‘ – Example: [DATE] = 2012-03-01  Date Range Filters – Use ‘between’ or ‘in_range’ – Example 1: [DATE] between 2013-03-04 and 2013-03-08 – Example 2: [DATE] in_range {2013-03-04:2013-03-08}  Relative Date Range Filters – You can integrate date functions into filters to enable relative date formats. – Use Case: Report of all circulation activity for all libraries for the last completed month. If you run a report with a filter like this today (3/12/2013), the filter will calculate the current month, figure out what the last complete month was, and adjust the filter accordingly. – Example: [DATE] between _first_of_month (_add_months (current_date,-1)) and _last_of_month (_add_months (current_date,-1)) Note: Sample footnote
4. 3 The display format of the date is driven by the default setup in the reporting model. You cannot change this default, but you can modify a date field if you do not like the default format. Date Formatting Tips 1. Select the date in the report and go to the ‘Properties’ box. 2. Select ‘Date’ in the ‘Data Format’ area. 3. Use the properties menu to modify the date.
5. 4 Date functions expand the filter and display options in reports. Date Functions: • _first_of_month ([date]) - calculates the first day of the month • _last_of_month ([date]) – calculates the last day of the month • _day_of_week([date],1) – calculates the day of the week; 1=Monday, 2=Tuesday, etc. • _add_days ([date],#) – calculates the date based on the #; example: _add_days ([3/11/2013],7) = 3/18/2013 Basic Date Functions
6. 5 Use the _first_of_month ([date]) function to organize a summary report by month. Monthly Summary Report – Tips & Tricks 1. Add a ‘Query Calculation’ to the report (Insertable Objects > Toolbox) 2. Add the _first_of_month function to the ‘Expression Definition’ area.
7. 6 Use the _first_of_month ([date]) function to organize a summary report by month. Monthly Summary Report – Tips & Tricks cont. 3. Go to Query Explorer > Query 1; in the ‘Properties’ section make sure to set ‘Autogroup and Summarize’ to ‘Yes’. 4. In the ‘Properties’ section make sure to set ‘Autogroup & Summarize’ property to ‘Yes’.
8. 7 Monthly Summary – Results Report studio view Results
9. 8 There are functions available that can enable report authors to setup custom date groups, such as Quarter and Fiscal Year. Use case statements to group dates. Advanced Date Functions – Setting Up Custom Groups Case Statement – Quarter Case Statement – Fiscal Year CASE WHEN to_char ([Data for Circulation Reports].[Circulation Events].[DATE_EVENT],'mm') between 01 and 03 THEN 'Q3' WHEN to_char ([Data for Circulation Reports].[Circulation Events].[DATE_EVENT],'mm') between 04 and 06 THEN 'Q4' WHEN to_char ([Data for Circulation Reports].[Circulation Events].[DATE_EVENT],'mm') between 07 and 09 THEN 'Q1' WHEN to_char ([Data for Circulation Reports].[Circulation Events].[DATE_EVENT],'mm') between 10 and 12 THEN 'Q2' END CASE WHEN [Data for Circulation Reports].[Circulation Events].[DATE_EVENT] between 2009-07-01 and 2010-06-30 THEN 'FY2010' WHEN [Data for Circulation Reports].[Circulation Events].[DATE_EVENT] between 2010-07-01 and 2011-06-30 THEN 'FY2011' WHEN [Data for Circulation Reports].[Circulation Events].[DATE_EVENT] between 2011-07-01 and 2012-06-30 THEN 'FY2012' WHEN [Data for Circulation Reports].[Circulation Events].[DATE_EVENT] between 2012-07-01 and 2013-06-30 THEN 'FY2013' WHEN [Data for Circulation Reports].[Circulation Events].[DATE_EVENT] between 2013-07-01 and 2014-06-30 THEN 'FY2014' ELSE '?' END
10. 9 Report with Quarter Calculation Report with Fiscal Year Calculation
11. 10 A relative date filter lets you define a range of dates that updates based on the date and time you run the report. For example, on the first of every month, you’d like an emailed report of all circulation activity in the previous month. You can use advanced date functions in filters to setup these types of reports. Date Filter: [Data for Circulation Reports].[Circulation Events].[DATE_EVENT] between _first_of_month (_add_months (current_date,-1)) and _last_of_month (_add_months (current_date,-1)) Function Breakdown: • Current_date: function calculates the date on the day the report is run • _add_months (current_date,-1): calculates the month, then subtracts the month by 1, giving you last month • _first_of_month / _last_of_month: calculates the first and last days of last month Relative Date Filters
12. 11 All the reports discussed in this tutorial are available here: Public Folders > Training Reports > User Group Sessions > Date Filters and Functions– 2013-03-12 • 1-Basic Date Filter (Single Date) – Total Holdings Cataloged – H08 Status and Date • 2-Basic Date Filter (Date Range) – Total Holdings Cataloged – H08 Status and Date • 3-Date Formats and Functions • 4a-Sample Circulation Report – Monthly Summary • 4b-Sample Circulation Report – Monthly Summary (Finished Version) • 5a-Sample Circulation Report – Quarter, Month • 5b-Sample Circulation Report – FY, Quarter, Month • 6-Circulation Statistics – Previous Month’s Activity User Group Reports List