How to achieve Cognos Time Series in BusinessObjects?
How to achieve Cognos Time Series in BusinessObjects?
I would like to blog on ‘Times Series’ which was an interesting part, we came across while implementing Cognos to Business Objects report migration. Cognos has in-built feature to categorize each time series while defining the model of Cube.
Examples of Time Series:
- Month to Date (MTD)
- Quarter to Date (QTD)
- Year to Date (YTD)
We have picked one though there are multiple ways to achieve the Time Series – to use ‘Captions’ or ‘Labels’.
Captions (pairing of label and condition in each query OR in each part of a “union all” query)<strong> </strong></p><p>Caption here is referring to dimension object defined in universe with definition of a text such as ‘YTD’ (Year to Date) with no reference to any column in any table or we can call it as dummy column. When selected in the query panel, it will repeat for each row returns from the database. </p><p>For example, when objects <Caption> <YEAR> <SALES COST> are selected in Web-i query panel, the dataset will look like:</p><p>!https://weblogs.sdn.sap.com/weblogs/images/252181350/Pic1.jpg|height=143|alt=image|width=309|src=https://weblogs.sdn.sap.com/weblogs/images/252181350/Pic1.jpg|border=0!</p><p>We can define the caption for each time series as follows and achieve the result by pairing with respective conditions</p><p>Example:
Now, we have caption – dimensions and conditions are available in the universe. Let us have a look at the possible scenario and how we can achieve it.
Scenario: How to combine YTD, MTD Sales Cost by Market on a report
Query Panel:</p><p>Select: <Caption – YTD>, <MARKET>, <SALES COST></p><p>Condition: <YTD – Year to Date condition></p><p>UNION</p><p>Select: <Caption – MTD>, <MARKET>, <SALES COST></p><p>Condition: <MTD – Year to Date condition> </p><p>Result would be as follows,</p><p>!https://weblogs.sdn.sap.com/weblogs/images/252181350/Pic3.jpg|height=135|alt=image|width=295|src=https://weblogs.sdn.sap.com/weblogs/images/252181350/Pic3.jpg|border=0!</p><p>Using this approach, we can create cross-tabs at the reports for multiple time seris as follows, </p><p>!https://weblogs.sdn.sap.com/weblogs/images/252181350/Pic4.jpg|height=79|alt=image|width=295|src=https://weblogs.sdn.sap.com/weblogs/images/252181350/Pic4.jpg|border=0!</p>