Macros (Part 2 of ?): Timestamp of a dimensional twist.

One of the problems often faced when working with true dimensional sources (as opposed to DMR which will not be discussed here, ever) is working with dates. You can very easily make Current Year or Previous Year members, but those will not allow you to use the family functions. How then can you make a query default to a specific year?

To begin you must first understand the structure of your cube. Different cubes have different ways of building their members. The Member Unique Name (MUN) for a specific year might look like:

[Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231]

You can use

children([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].#sb(timestampMask($current_timestamp,’yyyy’)+’0101-‘+timestampMask($current_timestamp,’yyyy’)+’1231′)#)

Cognos will resolve that statement to children([Cube].[Date].[Date].[Year]->:[PC].[@MEMBER].[20100101-20101231])

You can also use timestampMask with prompt macros:

#timestampMask(toUTC(prompt(‘pDate’,’date’) +’ 00:00:00.000000000-00:00′),’yyyy’)#

The toUTC command will convert a string 2010-09-26 00:00:00.00000000-00:00 to a timestamp, allowing it to be used with the timestampMask function.

Macros (part 1 of many)

From the User Guide:

A macro is a fragment of code that you can insert in the Select statement of a query or in an expression. For example, add a macro to insert a new data item containing the user’s name.

These two small sentences belie the great value macros offer to report developers. In my own words, macros allow the author to dynamically alter the SQL at runtime.

Practically this allows the author to specify specific fields, tables, or even databases at run time. The author can create filters that check users’ credentials, or redirect users to different tables based on the month or the users’ language settings.

All macros are delimited with two #s. The following is an acceptable macro: #sq(‘Hello World!’)# The sq() function will wrap a string in single quotes. If you attempted to run the same macro without the sq(), Cognos would try to interpret Hello World! as a function call, yielding you a syntax error. Understanding this behavior is key to building complex macros.

Let’s say that you’re working on a database that has materialized views for each month. The tables are named v_YYYYMM: 201001, 201002, 201003, etc… Your report needs to run against the current month. You can use the macro function timestampMask with the parameter $current_timestamp.

[ns].[table].#sb(‘v_’ + timestampMask($current_timestamp,’yyyymm’))#

$current_timestamp checks the time on the Cognos server (a side note, I am pretty sure that it checks the dispatcher. If you have multiple dispatchers in different time zones this can cause some issues unless they’re all synced). Lets say that returns 2010-09-18 22:20:31.000+02:00. timestampMask() will take the timestamp and return it with the specified format: 201009. sb() will then wrap ‘v_’ and 201009 in square brackets: [v_201009]. Cognos will then attempt to run [ns].[table].[v_201009].

Ultimately this will allow you to run cleaner and faster SQL.

Dynamic Year prompt

One of the requirements that I have often encountered is a dynamic year prompt. The user would like a list of the past n years, defaulting to a specific one (the previous year for instance). Cognos does not have a built-in way of dynamically setting a default value on a prompt. There is a way around this though.

To start go to the query that needs to be filtered. In this example I’ll be using a relational source.

The filter line should be

[ns].[Time].[Year] = #prompt('Year','integer','year(getDate))-1')#

Use a prompt macro to get the default year

The prompt macro will automatically insert the default year into the filter, in this case the current year – 1. Note that the year(getDate()) is T-SQL, PL/SQL users should using something like to_char(add_months(sysdate, -12),’YYYY’).

If there is no value returned the parameter on runtime the filter will default to the previous year. This works well, but the user wants the year selected on the value prompt.

Add some fields to the report page to test it.

For that we’ll need the prompt on the page. Create the value prompt, give it the parameter name Year, and click finish. Set the name of the prompt to Year. Set Auto-Submit to Yes. Add static choices 1 – 5.

The next step is to change the numbers into years, and to automatically select the previous year.

Add an HTML item to the right of the prompt:

<script>
var form = getFormWarpRequest();
var YearList = form._oLstChoicesYear;
var i = 0;
d=new Date();
year = d.getYear()
while (i <= 4)
{
YearList.options[i+2].text=year - i;
YearList.options[i+2].value=year - i;
i++;}
YearList.remove(0);
YearList.remove(0);
YearList.options[1].selected=true;
YearList.removeAttribute("hasLabel");
</script>

Note that the while statement loops 5 times. If you want more than 5 options increase the number of loops accordingly.

If it works the report will look like: It works except for one small issue. When the user selects another year, the report is correct but the Javascript forces the year in the prompt back to the default selection!

The best way to solve this is to set the HTML Source Variable with a Boolean variable.

Use the report expression

ParamValue('Year') is not null

Select the new variable from the Condition Explorer and click on the HTML Item. If it is not already, set the HTML Source Variable to the variable that was just created. Set the Source Type to Report Expression and paste in the following code:

'<script>
var form = getFormWarpRequest();
var YearList = form._oLstChoicesYear;
var i = 0;
d=new Date();
year = d.getYear()
while (i <= 4)
{
YearList.options[i+2].text=year - i;
YearList.options[i+2].value=year - i;
i++;}
YearList.remove(0);
YearList.remove(0);
YearList.removeAttribute("hasLabel");
YearList.options[' + number2string(extract("year",date2timestamp  (Today()))-string2int32(ParamValue('Year'))) + '].selected = true;
</script> '

Now when no year is selected it will default to the previous year, and select the second value in the prompt. When a year is selected, it will find the number of years between that year and current year (extract(“year”,date2timestamp (Today()))-string2int32(ParamValue(‘Year’))), and will select the year at that index. Some locales use a semi-colon instead of a comma.