This post spent a year in drafts ;-( But Macro functions are an interesting feature of Cognos BI and sometimes their usage is unavoidable.

You can use macro functions in Report Studio to do some manipulations with prompt values before they are used for actual processing in filters, report functions, etc.

As a general (and very common) example, you can use macro functions to do datetime format transformations, to allow one various filters to be based on a single prompt (selecting ‘Version X, year 2009’ in prompt, which you can split up to two filters, for year and version) and do some other string conversion stuff.

Usually such need arises when you have drill-through reports with different source-target dimension member formats, such as OLAP (Transformer, TM1, Ms AS, Essbase) -> Relational or vice versa. For example (a very simple and not close to real-life), if you have a detailed transaction-level report based on relational database and a MOLAP-aggregated data on top of it.

Fictional Example

If, for example, your date MUNs in OLAP are like ‘2010-02-10’ and relational database expects something like ‘2010.02.10’, you can change string value of prompt by changing filter in detail report from

date = ?date_prompt?

to

date = #subtitute('-','.',prompt(date_prompt,'string','1'))

prompt macro function returns prompt value and substitute subtitutes strings )

Real-life example

One of real-life examples, where I had to use macro functions extensivly was OLAP(Essbase) to Relational drillthrough setup.

When using Essbase it’s better to have unique member names for dimension elements since this speeds query time a bit. So usually some unique identifiers are added to common conformed dimensions. On this project we’ve constructed a cube with financial transactions, with a number of participants for each operation. The same company can be a client in some operation and a supplier in another. So unique postfixes were added to each of dimensions in Essbase cube. Relational model for Oracle, however, had a single Party dimension table, which contained all possible participants.

This problem is easily solved with tecnique given above, I’ve just removed unique identifier.

The other problem in the same case is that Essbase dimensions usually have hierarchies ) Since operation participants can be aggregated by industry or region I can receive industry (or region) label in dimension prompt (building special prompts for each level is just impossible).

So I had to detect the level of member I’ve received (which was easier by having those unique level identifiers) and then to filter accoding column in relational table. So I can receive values like ‘Pharmaceuticals SUPPLIER INDUSTRY’ or ‘10241235 SUPPLIER ID’ from Essbase. Creating such a filter turns into filter like:

when (#sq(sb(csv (grep(': SUPPLIER_INDUSTRY';array(prompt('SUPPLIER_ID';'string';'1')))) ))#,'[]') THEN
([Relational Souce].[Supplier].[SUPPLIER_INDUSTRY] = #substitute(': SUPPLIER SUPPLIER_INDUSTRY';'';prompt('SUPPLIER_ID';'string';'1'))#)
when (#sq(sb(csv (grep(': SUPPLIER ID';array(prompt('SUPPLIER_ID';'string';'1')))) ))# ,'[]') THEN
([Relational Souce].[Supplier].[ID]= #substitute(': SUPPLIER ID';'';prompt('SUPPLIER_ID';'string';'1'))#)
else (1=1)

This really looks awfull, but it’s the only way I could find out to do ‘search’: that is to form an array from prompt values and do a ‘grep’ over it and compare it with empty string. But it does the trick nicely.