A common challenge that we see with all of our many IBM Cognos 8 clients is dealing with date logic. The most common forms for this challenge are around relative dates:
- How can I filter this report to only show the last x months of data?
- Can we display only the closed financial periods?
- I want to display the name of the current month in the header of my report
Unfortunately the most common approach to solving these sorts of issues is to either make the person running the report respond to a prompt to determine the needed filtering values or to put very complex date logic into functions in the report. Making the user provide the information is annoying to the user (why can’t this report just know what the last x months are?) and prone to misinterpretation at runtime (e.g. I thought I was supposed to pick the invoice dates that I wanted – not the ship dates!) Putting complex date logic in the reports is difficult to maintain, poorly performing and runs the risk that each report developer may take a slightly different and inconsistent approach to delivering a solution.
Our recommended approach is to have the Extraction, Transformation and Loading (ETL) process do the heavy lifting of date calculations and then leverage a powerful IBM Cognos feature called Parameter Maps to easily expose those calculations for use in reports.
The ETL process is crucial because it runs on a regular time interval (often on a nightly basis) so it can easily deliver calculations that involve relative dates – dates that are relative to now. We create a SQL table in our data mart that is very simple like this:
CREATE TABLE [dbo].[pcon_ctl_status](
[status_key] [int] IDENTITY(1,1) NOT NULL,
[status_parameter_name] [varchar](50) NOT NULL,
[status_parameter_description] [varchar](100) NULL,
[status_int_value] [int] NULL,
[status_char_value] [varchar](100) NULL,
[status_smalldatetime_value] [smalldatetime] NULL,
[status_float_value] [float] NULL,
CONSTRAINT [pcon_ctl_status_PK] PRIMARY KEY CLUSTERED
(
[status_key] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
The idea is that we populate this table with one row for every useful scalar value that we can think of – some examples include:
- The last time our ETL process was executed (so we know how current our data is)
- The date 13 months before the current date (if we do a lot of rolling 13 month reporting.)
- Any sort of calculated time period factors (what percentage of the working days in the current month have already elapsed?)
So the data in the resulting table looks something like this:
Every night the ETL process runs and updates the values in the status_xxx_value columns of this table. These are the four columns to the far right in the screen shot above. The most common way for the ETL process to do this is via a simple SQL script that gets executed at the end of the ETL process.
The screen shot above shows some of the update statements executing from within the IBM Cognos DecisionStream ETL tool but we also commonly do this from within Microsoft SQL Server SSIS jobs.
The next post in this series will explain how we now use this data inside our IBM Cognos 8 reporting environment.
Subscribe