How to handle relative time (relational model focused) - Update 01

Check list of all posts

Context

Five different methods are discussed in my old post How to handle relative time (relational model focused). Listed below:

1 - TURN RELATIVE TIME TO MEASURE
2 – USE PARAMETER MAP TO RESOLVE COMPLICATED RELATIVE TIME ISSUE
3 – USE JOINED QUERY AT REPORT LEVEL TO ADDRESS ROLLING PERIOD TO DATE ISSUE
4 – USE MOVING AVERAGE / TOTAL
5 – MAKE AND SORT A CROSSTAB REPORT WITH DIFFERENT TIME PERIODS

This document is intended to enhance the idea of item 2 and item 4

USE PARAMETER MAP TO RESOLVE COMPLICATED RELATIVE TIME ISSUE

In some cases, the logic to find the relative time periods is too complicated to use simple sql statement. Instead, you need to write complicated logic with complicated T-SQL.  Such as screenshot below

{

SQL code to get last Saturday date key:

select cast(convert(varchar(8), dateadd(day,-datepart(weekday,dateadd(WEEK,-1,GETDATE()))+1,dateadd(WEEK,-1,GETDATE())), 112) as integer)

}




In this case, you can’t use standard Cognos SQL, You can use Pass-Through as indicated below

This pass-through doesn’t cause any problems with local processing, as it this data set is independent from SQL query generated for report.

USE JOINED QUERY AT REPORT LEVEL TO ADDRESS ROLLING PERIOD TO DATE ISSUE

A generic approach is explored to resolve advanced report issue, which is to use time offset query in report. It is different from turning relative time to measure, as that approach needs to change framework manager every time. This idea is to build a time offset on the fly, and then generate rolling period data on the fly as well.

Please see following report as sample

Time offset query, this query can be extended many columns as needed

Join condition:

( [qS1_YTDTrend_Detail_Daily].[Date Key] between  [qS1_TimeOffset].[CY_YTD_StartDateKey] and [qS1_TimeOffset].[CY_YTD_EndDateKey] )
OR
([qS1_YTDTrend_Detail_Daily].[Date Key] between [qS1_TimeOffset].[PY_YTD_StartDateKey] and [qS1_TimeOffset].[PY_YTD_EndDateKey])

Query item Current Year  [CY Amount]

if ([qS1_YTDTrend_Detail_Daily].[Date Key] between [qS1_TimeOffset].[CY_YTD_StartDateKey] and [qS1_TimeOffset].[CY_YTD_EndDateKey])  then ([qS1_YTDTrend_Detail_Daily].[ Amount]) else (0)

Query item Prior Year [PY Amount]

if ([qS1_YTDTrend_Detail_Daily].[Date Key] between [qS1_TimeOffset].[PY_YTD_StartDateKey] and [qS1_TimeOffset].[PY_YTD_EndDateKey])  then ([qS1_YTDTrend_Detail_Daily].[ Amount]) else (0)

YTD growth
[CY Amount]/[PY Amount] – 1

Result