Converting YYYY-MM-DD to YYYYMMDD in Cognos Report Studio

How to convert standard SQL date format to YYYYMMDD

right(year([Gym].[Member Details].[DOB]),4)+
right(month([Gym].[Member Details].[DOB])+100,2)+
right(day([Gym].[Member Details].[DOB])+100,2)

Resizing value prompt boxes in Cognos Report Studio

Thanks to the following site for the follwoing.

To resize a value prompt insert a snippet of html before it with the following


Then another html snippet afterwards with the code below


var e=document.getElementById('selectSearch');
var myselect = e.getElementsByTagName('select')[0];

This will then resize the element automatically within the page (just don’t ask me how)

Automatically Reporting on Previous Calendar Month

This little snippet is used in a filter and automatically looks at the previous calendar month.

cast([Sales Team].[Client Contacts].[Start Date],date)

If the date is left as a timestamp then the last of the month is 2013-04-30 00:00 which means that a client contact that occurs at 9am would not be picked up. Casting it as a date ensures that it gets picked up as expected.

Another option is to add 1 day onto the _last_of_month, but that’s a hack.

The _add_months function can accept a negative number, which turns _add_months into _substract_months.

Formatting an ID Number

This snippet takes an ID number and displays it in the format of 3-3-4 (e.g. 123 456 7890)

substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),1,3)+' '+
substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),4,3)+' '+
substring(cast([Customers].[Account Detail].[Account Number],varchar(10)),7,4)+' '

Its a simple substring operation, withthe account number being cast as a 10 character string