How to create band report based on Cognos DMR

Check list of all posts

Context
There are 4 different ways to handle bands:
1. Create band in database
2. Create band in Cognos FM model, or tableau model
3. Create band in relational based model
4. Create band in DMR based.

While it is relative simple to create band in relational based model (s. sample below), the DMR based band is difficult to build and has poor performance.

BAND
CASE
WHEN [Sales (query)].[Sales].[Revenue]  < 1000  THEN '<1000'
WHEN [Sales (query)].[Sales].[Revenue]  BETWEEN 1000 AND 2000 THEN '1000 - 2000'
ELSE 'OTHER'
END

Sample to create band report based on Cognos DMR

BAND
set([BAND1],[BAND2],[BAND3])

BAND1
member (
total ( currentMeasure within set filter ( [Sales (analysis)].[Order].[Order].[Order detail code], tuple(currentMember([Sales (analysis)].[Order].[Order]),[Revenue]) < 50000) )
, '< 50000'
, '< 50000'
, [Sales (analysis)].[Order].[Order])

BAND2
member (
total ( currentMeasure within set filter ( [Sales (analysis)].[Order].[Order].[Order detail code], tuple(currentMember([Sales (analysis)].[Order].[Order]),[Revenue]) between  50000 and 100000) )
, '50000 -100000'
, '50000 -100000'
 , [Sales (analysis)].[Order].[Order])

BAND3 
 member (
total ( currentMeasure within set filter ( [Sales (analysis)].[Order].[Order].[Order detail code], tuple(currentMember([Sales (analysis)].[Order].[Order]),[Revenue]) > 100000) )
, '> 100000'
, '> 100000'
, [Sales (analysis)].[Order].[Order])

Performance problem with  to band report based on Cognos DMR

Relational based: All process is done on database side

with Sales as (

select ORDER_DETAILS.QUANTITY*ORDER_DETAILS.UNIT_SALE_PRICE Revenue

 from GOSALES.ORDER_DETAILS ORDER_DETAILS, GOSALES.ORDER_HEADER ORDER_HEADER

 where ORDER_HEADER.ORDER_NUMBER=ORDER_DETAILS.ORDER_NUMBER) 

select 

case  when Sales.Revenue<1000 then '<1000' when Sales.Revenue between 1000 and 2000 then '1000 - 2000' else 'OTHER' end  Band

, sum(Sales.Revenue) Revenue

 from Sales

 group by case  when Sales.Revenue<1000 then '<1000' when Sales.Revenue between 1000 and 2000 then '1000 - 2000' else 'OTHER' end

DMR based: All process is done on report server. Therefore, we need to be very cautious about the data amount to be handled.

with TIME_DIMENSION3 as (
select TIME_DIMENSION.CURRENT_YEAR CURRENT_YEAR, TIME_DIMENSION.MONTH_KEY MONTH_KEY
 from GOSALES.TIME_DIMENSION TIME_DIMENSION
 group by TIME_DIMENSION.CURRENT_YEAR, TIME_DIMENSION.MONTH_KEY) 

select distinct CAST( TIME_DIMENSION3.CURRENT_YEAR AS CHAR( 4 ) ) Yearkey
 from TIME_DIMENSION3
 where CAST( TIME_DIMENSION3.CURRENT_YEAR AS CHAR( 4 ) )='2006' and mod(TIME_DIMENSION3.MONTH_KEY, 100)<>0
 order by Yearkey asc nulls last
with Sales as (
select (EXTRACT( YEAR FROM (ORDER_HEADER.ORDER_DATE) )*10000+EXTRACT( MONTH FROM (ORDER_HEADER.ORDER_DATE) )*100)+EXTRACT( DAY FROM (ORDER_HEADER.ORDER_DATE) ) Day_key__order_date_, ORDER_HEADER.ORDER_NUMBER Order_number, ORDER_DETAILS.ORDER_DETAIL_CODE Order_detail_code, ORDER_DETAILS.QUANTITY*ORDER_DETAILS.UNIT_SALE_PRICE Revenue
 from GOSALES.ORDER_DETAILS ORDER_DETAILS, GOSALES.ORDER_HEADER ORDER_HEADER
 where ORDER_HEADER.ORDER_NUMBER=ORDER_DETAILS.ORDER_NUMBER)
 , TIME_DIMENSION8 as (
select TIME_DIMENSION.DAY_KEY DAY_KEY, TIME_DIMENSION.MONTH_KEY MONTH_KEY, TIME_DIMENSION.CURRENT_YEAR CURRENT_YEAR
 from GOSALES.TIME_DIMENSION TIME_DIMENSION
 where CAST( TIME_DIMENSION.CURRENT_YEAR AS CHAR( 4 ) )='2006')
 , Sales9 as (
select (EXTRACT( YEAR FROM (ORDER_HEADER.ORDER_DATE) )*10000+EXTRACT( MONTH FROM (ORDER_HEADER.ORDER_DATE) )*100)+EXTRACT( DAY FROM (ORDER_HEADER.ORDER_DATE) ) Day_key__order_date_, ORDER_HEADER.ORDER_NUMBER Order_number, ORDER_DETAILS.ORDER_DETAIL_CODE Order_detail_code, ORDER_DETAILS.QUANTITY*ORDER_DETAILS.UNIT_SALE_PRICE Revenue
 from GOSALES.ORDER_DETAILS ORDER_DETAILS, GOSALES.ORDER_HEADER ORDER_HEADER
 where ORDER_HEADER.ORDER_NUMBER=ORDER_DETAILS.ORDER_NUMBER) 

select
CAST( Order_.Order_number AS CHAR( 6 ) ) Order_numberkey
, CAST( Order_.Order_detail_code AS VARCHAR( 8 ) ) Order_detail_codekey
, CAST( TIME_DIMENSION8.CURRENT_YEAR AS CHAR( 4 ) ) Yearkey
, sum(Sales9.Revenue) Revenue
from Sales Order_, TIME_DIMENSION8, Sales9
where mod(TIME_DIMENSION8.MONTH_KEY, 100)<>0
and Sales9.Order_detail_code=Order_.Order_detail_code
and Sales9.Day_key__order_date_=TIME_DIMENSION8.DAY_KEY
group by CAST( Order_.Order_number AS CHAR( 6 ) )
, CAST( Order_.Order_detail_code AS VARCHAR( 8 ) )
, CAST( TIME_DIMENSION8.CURRENT_YEAR AS CHAR( 4 ) )
order by Yearkey asc nulls last

how to debug a complicated DMR based report

Check list of all posts

This document will have walk through to demonstrate how to debug a complicated DMR report.
1. Problem
Below is Discontinuous Crosstab reports with problem in the marked area. However, when checking data with Cognos workspace advanced, we get correct data, and therefore we consider it as report problem.

This report above is Discontinuous crosstab, which has many measures with calculation on X side, and many different dimensions on Y side 

2. Analysis
it is very difficult to pin down the problem. We get correct result when removing most of measures and all other dimensions.

 This report above is Discontinuous crosstab, which has many measures with calculation on X side, and many different dimensions on Y side . the only solution is to remove measures and dimensions one by one, respectively.

3. Debug
Step 1. Remove measure 1, 2 , 3, to save report as X1, X2, X3, ….

Step 2. Define a new job and run all these reports and then identify until which measure get this problem

Step 3. Remove this identified measure(s), but the result still NOT correct. in this sample, it is almost impossible to pin down the issue, as there are two many combinations.

Step 4. However, when looking into generated SQL between report with problem and report without problem, we find the generated SQL is very different, from JOIN to UNION. but it is very difficult to see the real problem.

Step 5. Remove dimension 1, 2 , 3, to save report as Y1, Y2, Y3, ….

Step 6. Define a new job and run all these reports and then identify until which dimension get this problem

Step 7. Define a new job and run all these reports and then identify until which dimension get this problem

Step 8. Remove this identified dimension, check report and confirm that the generated report is correct. then we can be sure that this dimension has problem.

Step 9. Generate two SQLs below and compare them
- SQL generated with correct result, aka GOOD SQL
- SQL generated with wrong result, aka  BAD SQL
For this case, it is INNER JOIN with this identified dimension.  There are some dimension items that exist in FACT table, but does NOT exist in this dimension, therefore remove some data from FACT table as long as this dimension is used.

How to use tuple without measure member in Crosstab with Cognos DMR model

Check list of all posts

A tuple is a combination of members from different dimensions. Normally, each tuple is a data cell in a result set, therefore a measure must be located in a tuple. However, a tuple may not be needed in Cognos DMR. This capability will present a very powerful solution for many cross table developments, as long as there is a measure in either column or row, regardless of whether it is nested or not. Furthermore,  we don’t even need a measure in either row or column if there is only one default measure used.

The following sample is intended to give an idea how this concept can be applied in development. 

The Cognos sample package great_outdoors_warehouse is used in this sample; the crosstab is to figure out the difference between planned revenue and revenue and between Telephone and Fax. Two not normal usages are used:
1) No measure is used in both tuples
 a.tuple([Telephone],[Camping Equipment])
 b.tuple([Fax],[Camping Equipment])
2) No measure is used in calcualtion for two tuples
[Telephone, Camping Equipment] - [Fax, Camping Equipment], which is same as
tuple([Telephone],[Camping Equipment]) - tuple([Fax],[Camping Equipment])


Add one more practical sample below for reference:

1. if ([UPLC] is null) then (0) else ([UPLC])
+ if ([UPL]  is null) then (0) else ([UPL])
+ if ([CIBC PCA Overdraft] is null) then (0) else ([CIBC PCA Overdraft])
+ if ([PCF Unsecured PLC (LCBA)] is null) then (0) else ([PCF Unsecured PLC (LCBA)])
+ if ([PCF Unsecured PL (APL & RRSP)] is null) then (0) else ([PCF Unsecured PL (APL & RRSP)])
+ if ([PCF Approved Overdraft] is null) then (0) else ([PCF Approved Overdraft])
+ if ([PCF Operation Overdraft] is null) then (0) else ([PCF Operation Overdraft])
2. (tuple([NCL], [0-FYTD]) / tuple( [O/S Balance], [0])) * 10000
3. (tuple([NCL], [1-FYTD]) / tuple( [O/S Balance], [12])) * 10000
4.[Consolidated Cube].[Actual Or LE].[Actual Or LE].[Actual Or LE]->[all].[Actual]
5.[Consolidated Cube].[Actual Or LE].[Actual Or LE].[Actual Or LE]->[all].[Forecast]
6.([Forecast] - [Actual]) / [Forecast]

How to author Cognos complicated crosstab with multiple measures and different calculation based on DMR

Check list of all posts

1         Problem

This report below illustrates some special requests:

1)     There are different measures located on rows and based on two group. The first row is total, while all others is a sub group;

2)     different columns are specified with three different granularities: a. two months, b) Month difference and c) all values are based on total, not sub group, which is different from two months.

3)     With respect to Month difference, there are different calculation, such as simple A-B and (A-B)/B.

4)     Some obvious override apply for the format for crosstab.

2         Analysis

This crosstab is not easy to build, as the granularity are different in both directions, columns and rows. One of solutions is to use crosstab override, which is approved very difficult. Another solution is to use singleton, but the problem is that each singleton in general need to a separated query. As it is so many cells to override, we need to have many queries to create. This solution is not practical either. The goal is look into a solution with one query and gets all data.

3         Solution

According to the goal with clear structure, we need to break crosstab into two different sub crosstabs, and break crosstab with more columns as needed, and then STITCH them together.

Layout:

Single Query:

A few practical ideas are derived below:

1)     Use crosstab node is resolve different granularities for columns, such as break down and crosstab space as total

2)     Use different sub crosstabs to make crosstab handle different  granularities.

3)     Use different columns to calculate different granularities and then put it together with show/hidden to avoid crosstab override.

4)     As each data container (list, crosstab and chart) generates different SQLs, we can use the same query to perform multiple data sets. Please note that singleton is different from list, crosstab and chart, as singleton requests a separated Cognos Query.

How to handle "All Others" in dimensional reporting

Check list of all posts

Sample

While it is relative easy to build “All others” report for relational package, it is not easy to build it for dimensional package. This document is intended to provide a sample as reference when such requirements occur.

In order to explain the concept, the following report is used. Check the report specification for reference ( version 10.2) sample package Go Sales (Analysis) is used.

Query

All crosstabs in report shows the same query

Year

[Sales (analysis)].[Time].[Time].[Year]->[Time].[2013]

All Set

[Sales (analysis)].[Products].[Products].[Product type]

Main Set

topCount ([Sales (analysis)].[Products].[Products].[Product type],2,[Revenue])

All Others Set

except ([Sales (analysis)].[Products].[Products].[Product type],[Main Set])

All Others Aggregate

Aggregate(currentMeasure within set [All Others Set])

Union Set

union ([Main Set],[All Others Aggregate])

Revenue

[Sales (analysis)].[Sales].[Revenue]

All Others Member 1

member ([All Others Aggregate],'All Others','All Others',[Sales (analysis)].[Products].[Products])

All Others Set 1

set ([All Others Member 1])

Union Set1

union ([Main Set],[All Others Set 1])

Case 1

Case 2

Case 3

This is to show All others in detail.

Case 4

This is the solution option 1:  we can turn the detail data into aggregated one row with Aggregate(currentMeasure within set [All Others Set]).

This solution physically separates main set and all other data, therefore we can handle main set differently from the whole set.

Case 5

This is the solution option 2:  we can turn the detail data into aggregated one row with Aggregate(currentMeasure within set [All Others Set]), and then merge it together with union ([Main Set],[All Others Aggregate]).  This solution doesn’t separate main set and all other data, therefore we can sort the whole set with the same criteria.

Case 6

This is the solution option 3:  we can turn the detail data into aggregated one row with Aggregate(currentMeasure within set [All Others Set]), and turn it to member with member ([All Others Aggregate],'All Others','All Others',[Sales (analysis)].[Products].[Products]), and then make it to set with set ([All Others Member 1]) ]), and then merge it to an new union set union ([Main Set],[All Others Set 1])

Report should have the same behavior as Case 5

How to create optional slicers

Check list of all posts

Optional slicers are requested in many business situations. 4 different cases are listed below.

1. Single select prompt without default

This case is simplest case; we can simply use #prompt with MUN with rootMember. As rootMember represents the “All” member of the hierarchy, use the “All” Member in place of this. In OLAP, partial tuples are resolved into complete tuple by the OLAP engine when the query is executed. For all dimensions not explicitly referenced in the partial tuple, the “All” member is used. In this case we have to slice by something, or report will generate error, so we are explicitly telling the cube to use the “All” member in place of a member or member set passed in the parameter. This results in the same resolved tuple that the OLAP engine would have generated on its own.

#prompt ('Region1','memberuniquename','rootMember ([Sales (analysis)].[Retailers].[Retailers])')#

2. Single select prompt with default

In this case, we do have a default value. In order to make consistent between prompt page and report, we need to define default values in prompt

The slicer itself in query is

#prompt ('Region2','memberuniquename','[Sales (analysis)].[Retailers].[Retailers].[Region]->[Retailers].[710]')#

3. Multi select prompt without default

The multi select slicer is similar as the one of the single select prompt, by changing from prompt to promptmany.

#promptmany ('Region3','memberuniquename','rootMember ([Sales (analysis)].[Retailers].[Retailers])','set(','',')')#

Please note that we should add the 4th parameter as display above. If we don’t define as above, we will experience errors when select some regions as below, somehow, Cognos interprets values with semicolon.

4. Multi select prompt with default

In this case, we do have a default values. In order to make consistent between prompt page and report, we need to define default values in prompt

The slicer itself in query is

#promptmany ('Region4','memberuniquename','set([Sales (analysis)].[Retailers].[Retailers].[Region]->[Retailers].[710],[Sales (analysis)].[Retailers].[Retailers].[Region]->[Retailers].[740])','set(','',')')#

How to make different periods on chart x-axis (Chart=Crosstab)

Check list of all posts

Context
It could a challenge task when creating a complicated chart. One of the best approaches is to consider chart as crosstab, which can really help you to build complicated chart. As known, crosstab is based on crosstab node member, while chart is based on chart node member; the data behavior is exactly same between crosstab and chart. The following sample demonstrated an idea to make chart and crosstab share the same query. Please download report specification (10.1.1) for detail reference.

Sample

The DMR sales package is used

1. Create a query, where definition can be found in old post

2. Create a crosstab

3. Now create a chart based on crosstab and use the same query

The key to focus is that you can add multiple periods easily with different period. Please be noted that these period can be considered as set union.

4. Run report

5. Chart and table is synchronized by drilling up and down, as they share the same query

How to calculate simple and weighted average of ratio on cube based report

Check list of all posts

This document is intended to show a few approach to get average calculation based on cube. There is difference between simple average and weighted average.

Define query as below


Create a crosstab, where average query is clearly defined below


Run report

How to find impacted reports and data lineage

Check list of all posts

Context
It is imperative to know the report impact and data lineage, when changing data mart. Cognos provides both tools, or framework manager and data lineage to rescue, which is very useful for all users, including business analyst, architect, developers and project manager.

Impacted reports

It is very useful to figure out what query subjects are used in what report. Cognos FM can not only provide you at query subject level, but also at individual column level. For example, you want to find what reports use Revenue.






If you want to want to get the meta data into excel, or more formatted fashion, you can save it into xml file, then create a xslt file as parser like below

<?xml version="1.0" encoding="ISO-8859-1"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

<xsl:template match="/">
 <html>
 <body>
 <table border="1">
   <tr bgcolor="#9acd32">
     <th>Path</th>
     <th>Report</th>
     <th>Query</th>
     <th>QueryItem</th>
   </tr>
   <xsl:for-each select="ImpactRequest/Results/CMObject/Item">
   <tr>
     <td><xsl:value-of select="../@Path"/></td>
     <td><xsl:value-of select="../@Name"/></td>
     <td><xsl:value-of select="@QueryName"/></td>
     <td><xsl:value-of select="."/></td>
   </tr>
   </xsl:for-each>
 </table>
 </body>
 </html>
</xsl:template>
</xsl:stylesheet>

Then add two lines below into saved xml file

<?xml version="1.0" encoding="ISO-8859-1"?>
<?xml-stylesheet type="text/xsl" href="parser.xslt"?>

Finally, open xml file and get your spreadsheet.

Data lineage

When developing report, we often ask “how did we get that number”. The quickest way to get the answer is to use Data Lineage, which provides the answer to this question: firstly in business terms, then also as a technical expression that identifies specific sources, calculations and rules.  There are two ways to get detail data lineage

From package



From report (or other studios)

Utilities

Check list of all posts

Date related functions

Make timestamp from date key

Parameters map is a good solution to get current date; however, it present challenges to directly use date type, instead, data key as integer is easily to handle. In some cases, it is requested to turn it into real date in report. Code below can help
 
_make_timestamp (  
cast(#$CurrentDateKey{'Fact TableX'}#  /10000 ,integer),
cast(#$CurrentDateKey{'Fact TableX'}#  /100, integer) - ( cast(#$CurrentDateKey{'Fact TableX'}#  /10000, integer))*100,
#$CurrentDateKey{'Fact TableX'}# - cast((#$CurrentDateKey{'Fact TableX'}#  /100),integer)*100 )

Get last complete month date

Date + 1 Day - 1 Month, then get last day of month.

_last_of_month (_add_months (_add_days ([Fact].[Dim Time].[Current Date],1),-1))

Last Complete Month in the format of STRING:
cast(extract(year,  _add_months(current_date,-1)),varchar(4))||substring (cast(100+extract(month, _add_months(current_date,-1)),varchar(3)),2,2)

Last Complete Month in the format of NUMBER:
extract(year,  _add_months(current_date,-1))*100+extract(month, _add_months(current_date,-1))

Make week start day

For report with weeks, it is requested to display the first day of each week. The data item can get first day of each week.

case month(minimum([Dim Time].[Date] for [Dim Time].[Week Id]))
WHEN 1 THEN 'Jan'
WHEN 2 THEN 'Feb'
WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr'
WHEN 5 THEN 'May'
WHEN 6 THEN 'Jun'
WHEN 7 THEN 'Jul'
WHEN 8 THEN 'Aug'
WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct'
WHEN 11 THEN 'Nov'
WHEN 12 THEN 'Dec'
END
+
' ' + right('0' + cast(day(minimum([Dim Time].[Date] for [Dim Time].[Week Id])), varchar(2)), 2)

Half to date  current year, prior year

When it is requested to get Half to date, get reference date first as data item,  then get date started. CYTD- Current year to date; PYTD- Prior year to date; CHTD-Current half year to date; PHTD-Prior half year to date

[Dim Time].[Date] between  [DateFrom] and [DateTo]

[DateFrom]

case when ([Current Month Number] >6 ) and (?TimePeriodGrp?='CHTD' or ?TimePeriodGrp?='PHTD') then  _make_timestamp (year([DateTo]),07,01)

else  _make_timestamp (year([DateTo]),01,01)

End

[DateTo]

case

when (?TimePeriodGrp?='CYTD') then ([Dim Time (Activity)].[ Current Date])

when (?TimePeriodGrp?='PYTD') then ( _add_years ([Dim Time (Activity)].[ Current Date], -1))

End  

{ it also can be done by directly using filter
( ([Fact].[Dim Time].[Current Month Number] <7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) ) OR ( ([Fact].[Dim Time].[Current Month Number] >=7) and ([Fact].[Dim Time].[Year] = [Fact].[Dim Time].[Current Year] ) and [Fact].[Dim Time].[Month Number] >=7)
}

7d, MTD, YTD, PYTD  

case when ?Relative Date?='7d'
then ( [Date] between _add_days(2007-05-17,-7) and (2007-05-17))
when ?Relative Date? ='MTD'
then( [Date] between _first_of_month(2007-05-17) and (2007-05-17))
when ?Relative Date? ='YTD'
then( [Date] between _add_days(2007-05-17,((_day_of_year(2007-05-17)-1)*-1))
 and (2007-05-17))

Current YTD Sales:
if ( SalesDate between _make_timestamp(extract(year,current_date), 1, 1) and current_date )
then (SalesAmount)
else (0)

Prior YTD Sales:
if ( SalesDate between _make_timestamp((extract(year,current_date)-1), 1, 1) and _add_years(current_date,-1) )
then (SalesAmount)
else (0)

Get Last month of data available 

assume available date is [AvailableDate] and time dimension is [DimTime]: (year(_add_months (_add_days ([Namespace].[DimTime].[ AvailableDate],1),-1)) * 100) + month(_add_months (_add_days ([Namespace].[ DimTime].[ AvailableDate],1),-1))

Get start date and end date based on last available month

Get year start date

1)
-_day_of_year ([End Date])
-366

2)
_add_days ([End Date], -_day_of_year ([End Date])+1)
1-Jan-12 12:00:00 AM



Get last complete Month date

1)
[Fact].[Dim Time].Current Date]
6-Jan-13 12:00:00 AM

2)
_add_days ([Fact].[Dim Time].Current Date],1)
7-Jan-13 12:00:00 AM

3)
_add_months (_add_days ([Fact].[Dim Time].Current Date],1),-1)
7-Dec-12 12:00:00 AM

4)
_last_of_month (_add_months (_add_days ([Fact].[Dim Time].Current Date],1),-1))
31-Dec-12 12:00:00 AM

Get last 13 Months

[Fact Table].[Dim Time].[Month Id] >=
(year(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-13)) * 100) +
month(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-13))and 
[Fact Table].[Dim Time].[Month Id] <= 
(year(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-1)) * 100) +
month(_add_months (_add_days ([Fact Table].[Dim Time].[Current Date],1),-1))

Show date in crosstab

cast(cast (extract (year,[Returned items (query)].[Time dimension].[Date]), Char
(4))+substring (cast(cast(_round (((extract (month,[Returned items (query)].[Time
dimension].[Date])/100)+1)*100,0),integer),Char(3)),2,2)+substring (cast(cast
(_round (((extract (day,[Returned items (query)].[Time dimension].[Date])/100)+1)
*100,0),integer),Char(3)),2,2),integer)

string2date (
substring (number2string ([Query1].[Date]),1,4)
+'-'+
substring (number2string ([Query1].[Date]),5,2)
+'-'+
substring (number2string ([Query1].[Date]),7,2)
)

For IBM DB2 the calculation should look like this:

cast_integer(cast_char (Year([Returned items (query)].[Time dimension].[Date]) ,4)

||

right( replace ('0'||cast_char (Month([Returned items (query)].[Time dimension].

[Date]) ,2),' ',''),2)

||

right(replace ('0'||cast_char (day([Returned items (query)].[Time dimension].[Date]) ,

2),' ',''),2))

For Microsoft SQLServer the calculation should look like this:

cast_integer(convert({varchar},[Returned items (query)].[Time dimension].[Date],112))

For Oracle:

to_number(To_Char([Returned items (query)].[Time dimension].[Date], 'YYYYMMDD'))

Date related dimensional functions

Please load report specification for detail

Find Current Period using closingPeriod Function

closingPeriod ([sales_and_marketing_cs].[Time].[Time].[Month])

Find Current Period by Filtering on Measure Data

item(tail(filter(members([sales_and_marketing].[Time].[Time].[Month]),  tuple([Revenue], currentMember([sales_and_marketing].[Time].[Time]))  is not null), 1), 0)

Check link for detail explanation http://www.ibm.com/developerworks/data/library/cognos/reporting/dimensional_queries/page561.html

Current Month/Quarter/YTD/ Trailing Twelve Months

Often use a “base” month current period to build everything else off of:
 [Month Current] =  closingPeriod( [goc].[Years].[Years].[Month] )

Quarter
parent( [Month Current] )

YTD
total( currentMeasure within set
periodsToDate( [goc].[Years].[Years].[Year], [Month Current]  )  )

Trailing Twelve Months
total( currentMeasure within set
lastPeriods( 12, [Month Current]  )  )

Month % Change Prior Year
( [Month Current] - [Month Prior Year] ) / [Month Prior Year]
Where [Month Prior Year]  = lag( [Month Current] , 12  )

Note: currentMeasure is in the context of the crosstab

Period to date

[monthClosingPeriod] =
closingPeriod( [GOC].[Years].[Years].[Month] )

[periodsToDateForClosingPeriodMonth] =
PeriodsToDate( [GOC].[Years].[Years].[Year] ,   [monthClosingPeriod] )

Show real date from power cube

how to turn current day to real date, but dynamically displayed.


Current Day_Date
descendants ([Current Day],1)

Yesterday_Date
descendants ([Yesterday],1)

Oracle date functions

Get the last day of the week select trunc(sysdate, 'DAY')+6 from dual;

Get first day of year SELECT TRUNC(TO_DATE('17-DEC-2001'),'YEAR') "First Day" FROM Dual; Get last Day of Current Month
SELECT add_months(trunc(sysdate) - (to_number(to_char(sysdate,'DD')) - 1), 1) -1 FROM dual

SQL server date functions

Get the first day of month DATEADD(dd,-(DAY(DT.DAY_DT)-1),DT.DAY_DT) Get date key select cast(convert(varchar(8), getdate(), 112) as integer) Get last Sunday
Select cast(convert(varchar(8), dateadd(day,-datepart(weekday,dateadd(WEEK,-1,GETDATE()))+1,dateadd(WEEK,-1,GETDATE())), 112) as integer)

Useful Query items

1. Get total reminding to go for product Category XYZ, set 0 if the reminding to go as 0

if (([Product Category] = 'XYZ') and (total ([Forecast] for [Province Id], [Product Category] ) - total([Sales] for [Province Id], [Product Category]) > 0)) then ( total ([Forecast] for [Province Id], [Product Category] ) - total([Sales] for [Province Id], [Product Category] )) else (0)


2. TYD, QTD and HTD based day ( Assume [Fact Current Date],[Fact Current Quarter Number] and [Fact Current Quarter Number] )
#/* YTD at the begining of year*/#
_add_days ([End Date], -_day_of_year ([Fact Table].[Dim Time].[Fact Current Date])+1)

#/* QTD */#
if ([Fact Table].[Dim Time].[Quarter Id] = [Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
then ([Fact Table].[Fact Table].[Measure]) 
else (0)

#/* HTD */#
If ( [Fact Table].[Dim Time].[Fact Current Quarter Number] > 2 ) Then
    ( If ( [Fact Table].[Dim Time].[Quarter Number] > 2) Then
( If  ([Fact Table].[Dim Time].[Quarter Id] <= [Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
   Then [Fact Table].[Fact Table].[Measure])
   else (0) )
      Else ( 0)  )
Else    ( 
      If ([Fact Table].[Dim Time].[Quarter Id]<=[Fact Table].[Dim Time].[Fact Current Year]*10+[Fact Table].[Dim Time].[Fact Current Quarter Number])
      then ([Fact Table].[Fact Table].[Measure])
      else (0) )

Time filter

filter(
[Time Dimension].[Time Hierarchy].[Date],
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) >= #sq(prompt('From', 'Date'))# and 
roleValue('_businessKey', currentMember([Time Dimension].[Time Hierarchy])) <= #sq(prompt('To', 'Date'))#
)

Other useful tips and tricks

Display Report Run Times
layout calculation:   Now() - AsOfNow()

Promote an individual report
Open the file ReportX.txt in a text editor such as Notepad and do the following:
a.Click Edit | Select All to highlight all the text in the file.
b.Click Edit | Copy to copy all the text in the file to the clipboard.
In Report Studio, click Tools | Open Report from Clipboard.

Extract Level
SUBSTR(
'/Level1/Level2/Level3/Level4/Level5/'||'/'
,INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,1)+1
,(INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,2)-INSTR ('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,1)-1)
)
Level2
SUBSTR(
'/Level1/Level2/Level3/Level4/Level5/'||'/'
,INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,2)+1
,(INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,3)-INSTR ('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,2)-1)
)
Level3
SUBSTR(
'/Level1/Level2/Level3/Level4/Level5/'||'/'
,INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,3)+1
,(INSTR('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,4)-INSTR ('/Level1/Level2/Level3/Level4/Level5/'||'/','/',1,3)-1)
)

Transform year quarter to date

to_date(decode(?Quarter?, 'Q1', '03/31/', 'Q2', '06/30/', 'Q3', '09/30/', 'Q4', '12/31/')||?Year?, 'MM/DD/YYYY')

Set up 'Current Month' and avoid prompt pop-up from query items in FM

case when #PROMPT('p_selected', 'string', '''CURRENT''')# = 'CURRENT'  then add_months(current_date, -2) else (add_months(#PROMPT('Select a Month End Date','DATE''01-JAN-2000''','CURRENT_DATE')#,-1))

WHERE  (

              #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# ='CURRENT'

       AND    CALENDAR_YEAR_STR=CAST(date_part('year',add_months(CURRENT_DATE,-1)) AS   VARCHAR(20))

       AND    CALENDAR_MONTH_STR=CAST(date_part('month',add_months(CURRENT_DATE,-1)) AS VARCHAR(20)) )

OR     (

              #PROMPT('PLEASE SELECT AS OF MONTH IN YYYYMM FORMAT','STRING','''CURRENT''')# =CAST( CAST(CALENDAR_YEAR_STR AS INTEGER)*100+CAST( CALENDAR_MONTH_STR AS INTEGER) AS VARCHAR(20)) )

Commonly used netezza date functions and how to do data manipulations?
--to select the current date and timestamp use:
select current_date
select current_timestamp
--to add months and convert date to integer use:
select to_number(to_char(add_months(current_date, -1),'YYYYMMDD'),'99999999') --to get date id
select to_number(to_char(add_months(current_date, -1),'YYYYMM'),'9999999') --to get month id
select date(current_date -  cast('1 month' as interval))
--to convert timestamp to yyyy-mm-dd format:
select TO_CHAR(TO_DATE('2011-11-24 22:42:28','YYYY-MM-DD HH24:MI:SS'),'YYYY-MM-DD')
select TO_CHAR(current_timestamp,'YYYY-MM-DD')
--to extract year part from the current date
select extract (year from current_date)
select date_trunc('year', current_date)
to_number(to_char(current_date,'YYYY'),'9999')  --integer format
--to extract day and month from the current date
select extract (day from current_date)
--to get first and last day of the year:
select to_date((extract (year from current_date)) || '0101','YYYYMMDD')
select to_date((extract (year from current_date)) || '1231','YYYYMMDD')
--to get first of the month given a date
select date_trunc('month', current_timestamp)
--to get last month end date
select date_trunc('month', current_timestamp)-1
--to add 7 days to current date
select date(current_date +  cast('7 days' as interval))
--to get timestamp in a specific format
select to_timestamp( current_timestamp, 'YYYY-MM-DD HH24:MI:SS')
select to_timestamp( '2014-12-01 23:12:12', 'YYYY-MM-DD HH24:MI:SS')
--to add hours to a date
select TO_CHAR(date((current_date +  cast('1 hour' as interval))),'YYYY-MM-DD')
--To calculate difference in days , weeks between two dates
select datediff(week,'2012-01-01','2015-12-31') as numweeks;
select datediff(day,'2009-01-01','2015-12-31') as numdays;


Commonly used oracle date functions
SELECT ADD_MONTHS( DATE '2016-02-29', 1 ) FROM dual;
SELECT CURRENT_DATE FROM dual;
SELECT CURRENT_TIMESTAMP FROM dual;
SELECT EXTRACT( MONTH FROM TO_DATE( '31-Dec-1999 15:30:20 ', 'DD-Mon-YYYY HH24:MI:SS' ) ) MONTH FROM dual;
SELECT LAST_DAY(CURRENT_DATE) FROM dual;
SELECT MONTHS_BETWEEN( DATE '2017-07-01', DATE '2017-01-01' ) MONTH_DIFF FROM dual;
SELECT NEXT_DAY(CURRENT_DATE) FROM dual;
SELECT TO_CHAR(ROUND( TO_DATE( '20-Jul-2017 16:30:15', 'DD-Mon-YYYY HH24:MI:SS' ) ), 'DD-Mon-YYYY HH24:MI:SS' ) rounded_result FROM dual;
SELECT TO_CHAR( CURRENT_DATE, 'YYYY-MM-DD' ) FROM dual;
SELECT TO_CHAR( CURRENT_DATE, 'DL' )FROM dual;
SELECT TO_CHAR(12345.67, '99999.9') FROM dual;
SELECT TO_CHAR(12345, '00000000') FROM dual;
SELECT TO_CHAR(CURRENT_DATE, 'YYYY_MM_DD') FROM dual;
SELECT TO_CHAR(CURRENT_DATE, 'Month') FROM dual;
SELECT TO_DATE('2003/07/09', 'yyyy/mm/dd') FROM dual;
SELECT TO_DATE('070903', 'MMDDYY') FROM dual;
SELECT TO_DATE('20020315', 'yyyymmdd') FROM dual;
SELECT TO_DATE('2015/05/15 8:30:25', 'YYYY/MM/DD HH:MI:SS') FROM dual;

How to placing a carriage return in a string
make a simple carriage return without using CHAR(10) or CHAR(13) or /r/n
[BI_TITLE]  || '


' || [BI_DESCRIPTION]  || '


' ||  'Root cause: ' || [PRIMARYROOTCAUSE]






Useful links:
https://www.ibm.com/support/pages/output-wraps-multiple-rows-excel-2007: Output wraps into multiple rows in Excel 2007
Cognos Time and Date Functions  from temple university
Using the periodsToDate function within IBM Cognos 10 Report Studio to Calculate an OLAP Running-Total
IBM Cognos 8 Framework Manager - Dimensional Modeling for Time Period Analysis
Disallow a user to send reports by e-mail
Automatically refreshing a report
What are the differences between DQM and CQE mode in IBM Cognos 10?
Bridge Query Subjects (only supported using DQM)
Working with Dynamic Filtering
How To Reduce Your Framework Manager Package