Calculating Prior period in Cognos BI
Have you ever needed prior periods or last period within a report? For financial reporting, these calculations are a must and also a headache for report writers. This is the first issue you have to contend with when calculating last and prior is current. I know that line seems strange, but in order to calculate the prior period or the last period, you have to know that current period. Now, you can take the easy way out and ask the user to tell you the current period via a prompt, but what happens when the report has to be run on a schedule and you can’t rely upon a user? See … you need to calculate the value.
Step one – Calculate the current date
Date prompting is pretty straight forward in IBM Cognos Report Studio and there are a bunch of functions available to manipulate a date field. If you want to get the current date, you will use the current date function. It looks weird when you create data items based on the current data function because it creates what looks like a hardcoded date. However, it is not hardcoded but will change each day the report is run. More importantly, if you try to type in the exact same thing into the data item and expect the date to update when the report runs…get ready to stare at the screen, because it won’t work. I know it looks the same, but when you bring over the current data function, it does something in the background that you cannot replicate with a hardcoded date.
Step two – Get Year Part
Now that you have the current date, you may need to get the year portion of the date in order to calculate the fiscal year. This is really important when fiscal calendars do not match with the Gregorian calendar. If you have ever tried to extract the year part of a date within IBM Cognos, you know that it can’t really be done with a standard function. Before my dba’s get all worked up and correct me, let me just say that I am aware of a little trick … don’t steal my thunder I am getting there. What I was saying is that IBM Cognos functions for a Sql database does not have year as a function built in, however; you can still use it in an expression. To extract the year part out of a date field, use year(date value) and that will return the year part. If you need to add to the year, you can nest the add_year function as using a variation of this calculation add_year(-1,year(date value))
Putting it all together
Once you have the current date and can extract the various components of a date such as year, month, day; you can calculate last and prior very easily for your financial reporting. Of course, experienced dba’s will point out that we could build a flag within framework manager that would identify the current period, week, month, and year instead asking report writers to calculate it individually. In many ways, the dba is right and these values should be created in framework manager and available to report writers, but I am a realist and know what sometimes the right thing to do is not always the easiest or common thing done within a business.
For more information on this or other topics, email us at Sales@LodestarSolutions.com.