Tag: macros

Macros, Prompts

Macros (3 of n++): Prompts

| cognospaul

Macros allow you to alter SQL/MDX before it’s send to the datasource. Using the macro functions timestampmask, you can easily select specific members, or query certain tables.

Prompt macros give you an extremely fine grain of control. You can, for instance, allow a user to which measure he wants to see in his graph, or create dynamic default values.

To begin with, the structure of the prompt macro:

#prompt/promptmany(‘parameterName’,’dataType’,’defaultValue’,’leadingText’,’source’,’trailingText’)#

parameterName is self explanatory. Note that it is case sensitive. If you want to pass a value to that parameter via the URL or POST you need to preface it with a p_ so Par_Year becomes p_Par_Year.

The dataType will reject anything not covered by the datatype. The string ‘Hello World!’ would be rejected if the datatype was set to integer. The datatype also determines the type of prompt generated. ‘Integer’, for example, will always create a text box prompt even if the source field (see below) is filled.

The defaultValue will be returned if no parameter is selected. This does not need to obey the dataType. For example, you could have a filter set up: [Year] = #prompt(‘parYear’,’integer’,’year(getDate())’)# If 2010 was selected the filter would be [Year] = 2010, if no value was returned to the prompt the filter would be [Year] = year(getDate()).

The leadingText is text that is automatically entered before the selected parameter. For example a filter: #prompt(‘parYear’,’integer’,’1=1′,'[Year] = ‘)# In that example if 2010 was selected [Year] = 2010 would be returned to the filter. If nothing was selected, the filter would be 1=1.

The source will a list or tree prompt based on a referenced field. #prompt(‘timeMember’,’memberuniquename’,”,”,'[NS].[TimeDimension].[TimeHierarchy]’)# will create a tree prompt that will allow the user to select any member from the TimeHierarchy. #prompt(‘timeMember’,’memberuniquename’,”,”,'[NS].[TimeDimension].[TimeHierarchy].[MonthLevel]’)# will create a list prompt that will allow the user to select a single member from the MonthLevel. You can also include functions in the source: #prompt(‘timeMember’,’memberuniquename’,”,”,’filter([NS].[TimeDimension].[TimeHierarchy].[MonthLevel],[Measures].[Sales]>1000)’)# will create a list prompt of all months that had more than 1000 sales.

The trailingText, much like the leadingText, placing text after any values selected. #promptmany(‘Years’,’memberuniquename’,’children([NS].[TimeDimension].[TimeHierarchy]->[All Member])’,’set(‘,'[NS].[TimeDimension].[TimeHierarchy].[YearLevel]’,’)’)# will either return the children of the All member (if no values are selected), or will return a set of members that the user selected.

Macros, OLAP

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

| cognospaul

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.

8.4, Macros

Macros (part 1 of many)

| cognospaul

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.