Instead of dynamically determining date range in all reports, it is the best solution to use query based parameter maps to pre-calculate all date related parameters. This approach can dramatically improve performance as the query sent to database is with “static” value as where clause. This solution is applicable for both relational and DMR.
Use dynamic parameter maps for relational mode
The following sample is used to demonstrate this idea. Please see report below
The main challenge for this sample is to find the right week number when the week is cross year end. Can we build such report in report studio? The answer is YES, but it will result in a very big effort to make query very complicated and have very bad performance. The detail implementation is listed below
Step 1. Build query to get relative time. Note that all detail query result should be converted as consistent type.
select convert(varchar(50),'CY_CurrentWeek_SD') as PKey, convert(int,DAY_KEY) as Value from gosales.TIME_DIMENSION where DAY_DATE = '2007-07-15'
select convert(varchar(50),'CY_CurrentWeek_ED') as PKey, convert(int,year(DATEADD(day, +6,DAY_DATE))*10000 + month(DATEADD(day, +6,DAY_DATE))*100 + day(DATEADD(day, +6,DAY_DATE))) as Value from gosales.TIME_DIMENSION where DAY_DATE in (select DAY_DATE from gosales.TIME_DIMENSION where DAY_DATE = '2007-07-15')
Step 2. Load query above into database layer, SQL setting is native. This setting will give you flexibility to get complicated logic.
Step 3. Create parameter map based on query Relative time
Step 4. Create all calculations based on parameter map
Step 5. Define query
Step 5. Define report
Use dynamic parameter maps for DMR mode
Another sample is to use DMR data model. The following step uses Break Aggregate table to demonstrate the solution.
Step 1 Create query subject
Step 2 Define parameter map based on query subject
Step 3 Define Calculation in dimension view
Step 4. Move it to presentation view
Step 5 Publish the calculation, along with Query subject (Hide column)
6. Use Current report date in daily break report
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
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.