In theory, time calculations are simple; however the DMR (dimensional model relational) data I was working with last week was presenting a challenge to create Cognos BI PY calculation. DMR is a modeling technique in Framework Manager that identifies predefined hierarchies in relational data; hence providing a pseudo dimensional view of the data. This allows end users to drill-up and down through aggregate levels of data. However, do not mistake DMR for an OLAP environment. In an OLAP environment, you point the system to an intersection by selecting multiple dimensions. DMR in Framework Manager uses filters and aggregations to build hierarchies in relational data.

The second data nuance in this particular data set was that versions and measures were combined as a single fact. The facts I had available were actual period dollars, actual YTD dollars, period budget dollars and budget YTD dollars. This did not create any issues. It just needed to be taken into consideration while developing the reporting strategy.

Since DMR is relational data, end users must select an associated reporting period and fiscal period. This particular data set had 7 years’ worth or data; meaning that if I wanted to report on fiscal period May, there are 7 potentially different reporting periods (“May’s”) to choose from. To eliminate the error of mixing and matching unassociated years and periods, a tree prompt was used with instructions to the end user to select a reporting period. This forced the end users to select a period from the fiscal year on which they wanted to report.

Before the tree prompt was built, a current year parameter was created using a data item in the tool box. This is a crucial step if you are going to use a prompt in parallel period or a cousin function.

Once you have your current year prompt established, select a calculated member from the tool box to build your Cognos BI PY calculation and follow the calculation listed below.

Cognos BI PY calculation

• tuple (parallelPeriod ([PNLCube].[Fiscal Year and Periods].[FiscalYear].[Fiscal Year],1,([SelectedMonth])),[PeriodDollars])
• ([PNLCube].[Fiscal Year and Periods].[FiscalYear].[Fiscal Year] = used level in CY prompt
• [SelectedMonth] = data item parameter
• [PeriodDollars] = fact query item

Copy and adjusting the fact query item for each version needed in the report.

Hopefully you learned something valuable in this blog about creating Cognos BI PY calculation.  If so, check out this blog on BI admins now being able to use SDK by clicking here.