This is the second in a series of articles for the Ironside Cognos Report Studio Cookbook. The first article can be viewed here. These are tips and tricks used by our professional report developers to meet the unique needs and requirements of our clients. While some of these techniques may be touched upon in a typical Report Studio training class, we will take a deep dive into these concepts to uncover how our professionals actually use them in the field every day. Read more
This is the first in a series of articles for the Ironside Cognos Report Studio Cookbook. These are tips and tricks used by our professional report developers to meet the unique needs and requirements of our clients. While some of these techniques may be touched upon in a typical Report Studio training class, we will take a deep dive into these concepts to uncover how our professionals actually use them in the field every day. This month’s article will focus on how to make concise list reports by unlocking the report format. Read more
During the course of an IBM Cognos environment’s lifespan, there may be times when you will need to configure IBM Cognos to allow users to connect to more than one third party authentication source from the same Cognos portal. This can happen in several scenarios. Read more
IBM Finance Forum
Finance Forum is a global series of complimentary events designed specifically for youâ€”the finance professional. IBM Finance Forum showcasing the latest IBM solutions for performance management, financial governance, risk management and incentive compensation management. Read more
When faced with the challenge of creating and securing custom views in Transformer models (and their resultant PowerPlay cubes), one can easily perform the necessary steps via the Transformer GUI. However, when the number of custom views to be created and secured is large, it often makes sense to perform this task by using Model Definition Language (MDL) scripting – to both accelerate the time to deployment and reduce the errors that can occur with such a manual process. Read more
In the investment management or financial services accounting world, one of the most common calculations you encounter in is the time-weighted rate of return (TWROR). The time-weighted rate of return (sometimes called the geometric mean return) is a method of measuring historical performance of an asset or portfolio when external cash flows are present in the equation.
For example, if in your very own 401k, you make a transfer of funds from your money market account to purchase shares of a mutual fund, you would not want to count that influx of capital towards the overall rate of return on that fund (or your entire portfolio) as it would be inaccurately skewing the results in a positive manner. Conversely if you sold off a holding, or made a withdrawal, that could artificially skew portfolio performance in a negative direction. For more information on the definition and derivation of the time-weighted rate of return, please refer to this Wikipedia entry.
Understanding the TWROR Formula
For the purpose of this exercise, we will be using the following formula for calculating TWROR:
- is the time-weighted return for the portfolio
- is the initial portfolio value
- is the portfolio value at the end of sub-period
- is the net inflow which occurs in sub-period
- is the number of sub-periods
What you will notice here is that this formula is a geometric series, otherwise known as a product or compounded calculation. Essentially we will calculate each sub-period’s rate of return (in this case a month) and then take the product of all of those sub period returns to yield the overall true time-weighted Rate of Return for the series of sub periods (in our example a year).
Example: Manually Calculating a Quarterly Portfolio TWROR
|Period||Beginning MV||Ending MV||Net Flow||Period RoR|
Given the above data set, we would like to manually calculate a quarterly TWROR to demonstrate how the formula is applied.
- First we calculate our sub-period rate of return using the following formula:
- Then we chain all of our sub-period rates of return together to come up with the final time-weighted result for the quarter:
If we were to calculate this number in Excel, we would simply use the PRODUCT function to multiply all of the rows of values in a given column together to give us the final TWROR. Because there is no equivalent aggregate function in Report Studio (or SQL for that matter) we need to use a little mathematical trickery to come to the same solution. This is actually were most report developers throw their hands up and ask their users to finish their analysis in Excel. We are going to show you how to do 100% of the work in report studio.
Calculating the TWROR in IBM Cognos Report Studio
We will be using IBM Cognos 10.2 Report Studio for this example, but this approach will work for any version of IBM Cognos.
Start by creating a simple list report in report studio that has the following fields from your investment data mart:
- Sub Period (Year Month Number)
- Period Beginning Market Value
- Period Ending Market Value
- Net Capital Flow (Deposits – Withdrawals)
- Filter your list report by a single year, and/or portfolio
- Create a data item called Periodic Rate of Return with the following calculation:((-)/)-1
- Test your report and confirm that your periodic ROR calculation is accurately representing returns, net of any capital movement.
- Select the Query Explorer in Report Studio and drag a new query from the toolbox and rename it to Portfolio Total.
- Select the query from your list report (which we renamed to Periodic Holdings) and drag it to the right of your Portfolio Total query until a black bar appears, at which point release the mouse. Your new Portfolio Total query now references the Periodic Holdings query. We do this to force report studio to keep our sub-period ROR calculations at the correct level of detail.
- Double click your Portfolio Total query and drag a new data item from the toolbox into the data items list. Enter the following calculation and give the data item the name TWROR:exp(total(ln(1+.)))-1
- This is the mathematical trickery we were referring to before. The geometric series or product function can be replicated by taking the exponent of the sum of the natural logarithm of those values. This technique can be used for any geometric calculation you need to reproduce in SQL or Report Studio.
- Switch back to the page explorer, and drop a new list or singleton in your report that references the Portfolio Total query, and make sure the new TWROR item is present.
- Export the contents of your report to Excel and validate your numbers using the PRODUCT function.
We used two queries in this example to achieve the result. We could have done it all with a single more complex query, but that can sometimes require local report processing depending on the type of database you are using. A report specification for IBM Cognos 10.2 Report Studio is attached for your reference. Hopefully this technique allows you to simplify the lives of your portfolio analysts by arriving at answers faster without having to use multiple tools.
If you’re embarking on an initiative to provide better business analytics for your organization in the areas of investment management, institutional or private equity asset management, and hedge funds or other related financial services sectors, we encourage you to contact us for more information. Our financial services business analytics experts are familiar the unique challenges of the industry and deeply attuned to the needs of your analyst community. Our unique combination of industry experience and IBM Cognos domain expertise enables us to rapidly arrive at high impact solutions that are tailor made for your financial services organization.
The worlds of financial and operational analytics have always diverged, creating a disconnected analytics environment that segregates decision-making. That’s why The Ironside Group is thrilled with IBM’s recent release of IBM Cognos Disclosure Management (CDM), a solution that brings together the power of finance and operational analytics, with the ability to easily annotate your results when you need more than just numbers alone.
IBM Cognos Disclosure Management is a reporting and process automation solution designed to merge financial and operational analytics with narrative analysis in a controlled, auditable environment. You may have experienced the need to produce “board books”, or narrative reports, within Cognos, but had no way to accomplish the job other than custom, one-off reports. This has the potential to be a powerful solution for the Office of Finance.
Easily Combine and Narrate Your Most Accurate BI & PM Data
Cognos Disclosure Management marries financial, operational, and statistical data with narrative analysis in a fluid and dynamic environment. Finance professionals can tie-in Cognos business intelligence with their financial data and value-add analysis, and make it available to others in a controlled, secure collaboration to strengthen analysis and decision-making.
This includes the capability to link report data directly to enterprise data sources. Users can create dynamic reports that update automatically when the numbers in underlying data sources changeâ€”ensuring a single, consistent version of the truth throughout the office of finance.
Intelligent Process Design and Automation
Disclosure Management also provides intelligent process design and automation, transforming previously manual processes and enhancing controls well beyond the corporate financial reporting process and across other corporate finance processes. Disclosure Management includes workflow and task management capabilities allowing finance professionals to gain insight into critical performance management and reporting processes through dashboards and alerts. It also optimizes controls and auditability with standard review and validation procedures and ensures they are performed at each level of the organizationâ€”from department to entity to corporate headquarters.
Familiar Authoring Environment
Lastly, Cognos Disclosure Management automates the creation of management and executive PowerPoint presentations. Users can take advantage of their expertise with the calculation capabilities of Microsoft Excel, and their knowledge of formatting in Microsoft PowerPoint and Microsoft Word, while utilizing the additional functionality and capabilities of an enterprise database driven solution
Key Features at a Glance
- Easily combine business intelligence and performance management information in a single secure environment.
- Finally create those time consuming, manually annotated reports in a centralized, governed and automated environment with 100% accurate supporting data.
- Simple user interface based in Microsoft Office
- Direct Microsoft PowerPoint integration
- Easy to use wizard for adding queries from your live and accurate BI and PM data sources
- Simple management of reports, workflows and user based security
Supports Popular Data Sources
- OLAP (TM1/Controller (FAP), Essbase, MSAS)
- External Excel File
- Relational (Oracle, DB2, SQL)
- Cognos Go Office, Cognos CafÃ© (limited – no dynamic refresh)
- TM1 Perspective Excel Add-in (limited – no dynamic refresh)
- Controller Excel add-in (limited – no dynamic refresh)
- Supports Microsoft Office 2003, 2007 and 2010
If you’d like to learn more about Cognos Disclosure Management, view a live demonstration or request a quote, please contact us on the web or call 1-855-4IRONSIDE for more information.
Reporting should start with a “single source of truth” and end with a “single version of truth.” Delivery of accurate reporting information is paramount in the day-to-day operations of every organization. Some of the methods for maintaining data Integrity (within the data warehouse) would be to apply referential integrity and maintain primary keys on tables. In addition, associated ETL processes can utilize checksums, or record counts, when loading the warehouse. Once the data is in the warehouse, the next step would be to model the data for exposure to the end-users. Read more
For an updated version of this blog post using IBM Cognos Analytics 11.1.7, click here.
For users of IBM Cognos Workspace Advanced (formerly Business Insight Advanced), a popular request is to have the ability to highlight every other row in a list report. This technique has been demonstrated with IBM Cognos Report Studio using the running-count() and mod() functions. The issue is that IBM Cognos Workspace Advanced does not expose all functions that can be used in query calculations, so users think they are not available.
This article will demonstrate how to highlight every other row using IBM Cognos Workspace Advanced by entering a â€˜non-exposed’ function in the expression editor (the twist!). Read more
A request we often get is to build more dynamic dashboards and reports. This can be accomplished by a combination of Report Studio prompt reports, portlets, drill-up/drill-down and filtering. The key to this interactivity is communication between IBM Cognos portlets and channels. Utilizing channels allows us to create dynamic filtering and drilling capability. This article will explain the basics of using portlets and channels and provide a sample of how to create a dynamic dashboard. Read more