Macro functions and prompts in Cognos BI reports

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.

  • Dimitris

    hi….
    nice WORK!!!
    one question…i manage to choose a date period from 2 calendars using substitute…and so on…But unfortuantely this influence all my report.
    My question is,
    if i have the same measure and i want to have values from 2 different periods in the same crosstab,one period from the calendar one…and one the same but for the year ago…

    Any help??

    tnks one more time!

  • ykud

    Hi, Dimitris.
    A couple of questions, if I may:
    Are you using a dimensional or Dimensionally Modeled Relational model in framework manager? If you are using DMR, you can use ParallelPeriod function that will provide you the same period, year ago values.
    Are you using macro function as filter or as a way to get a Data Item with selected date? If you use macros to create a Data Item, you can wrap up rather crude macro logic with cases to substitute year value with previous one.

    Yuri

  • Dimitris

    Are you using a dimensional or Dimensionally Modeled Relational model in framework manager?
    ///Cognos Cube from transformer.

    f you are using DMR, you can use ParallelPeriod function that will provide you the same period, year ago values.
    ///ok i find it…

    1.i want my calendar not to be shown as a promp page(so i have to make it optional) how???
    2.i set some javascript in order to set the calendar to my appropriates dates,but these values not pass in reports,just in calendars…any idea??

    i use this code for all my measures…

    aggregate(

    CurrentMeasure

    within set
    (intersect(lastPeriods(-370;#’[Analysis Sales].[Time].[Time].[Day]->:[PC].[@MEMBER].['+substitute('-';'';substitute('-';'';prompt('Date_From','token';'XXXXXXXXXX')))+']‘#);
    lastPeriods(370;#’[Analysis Sales].[Time].[Time].[Day]->:[PC].[@MEMBER].['+substitute('-';'';substitute('-';'';prompt('Date_To','token';
    'XXXXXXXXX'
    )))+']‘#) )))

    what to put in the 3rd option???where is the default value???(=XXXXXXXX).I want my start date to be 1 of current year and ends to current date..

    tnks in advance…

  • ykud

    Hello again Dimitris.
    Considering your questions:
    “1.i want my calendar not to be shown as a promp page(so i have to make it optional) how???”

    If you don’t want anything to be shown on a page — just delete it ) Your filters are tied to a variable, which will remain in any case.
    And then you can add a filter on a report page and connect it to this variable, if you need.

    If I understand you correctly, you want a report with measures aggregated from start of the year to the current day (when report is run). If this is the single goal, you can easily do this via “YTD” category in Transformer. By adding a relative time element in time dimension, you can aggregate all dates from year start to current date set in transformer. And your measures will be as simple as tuple(CurrentMeasure,YTD).

    But if you’ll need more flexible time reports with current period prompts — you’ll have to do it in Report Studio.

    Sorry for delay in answering,
    Yuri

  • Dimitris

    Hello,tnks for your replies.

    I use as a package for my reports , a cognos cube from transformer.

    My aim is to have a single page with 2 calendars where when i select a period(Start_from , End_to),my 2 measures in my crosstab to beinfluenced as following,
    the first one with the selected period choosen
    and
    the second one with the same period but from the last year.
    I do not use filters because are not recommended for cubes.
    So , i make a data item for my first measure,
    /////////////////
    aggregate(
    CurrentMeasure
    within set
    (
    intersect(lastPeriods(-370;#’[Analysis Sales].[Time].[Time].[Day]->:[PC].[@MEMBER].['+substitute('-';'';substitute('-';'';
    prompt('Date_From','token';'XXXXXXXXXX')))+']‘#);
    lastPeriods(370;#’[Analysis Sales].[Time].[Time].[Day]->:[PC].[@MEMBER].['+substitute('-';'';substitute('-';'';prompt('Date_To','token';
    'XXXXXXXXX'
    )))+']‘#) )))
    ////////////////////////////////
    In this way i manage to aggregate my measure for the desired period.
    My 2 questions come here.
    I’ve read that in order not to appear the 2 calendars as a prompt page(only in my main one),i have to make my data item optionally.This can be done with macro by using a 3 parameter?right?Right,(with filters,we just set it as an optionally one).With Javascript i managed to set the calendar values to default values,1 from current year and current date.But unfortuantely these 2 parameters do not pass in my crosstab.How can i manage this?Is there any other way,(without javascript),to pass from macros these 2 defaults values for my crosstab?

    My second measure has the same logic.I find it with using the parallel period to the previous one.Ok.But still have the 2 questions above.

    How to pass in macros default values,1 of year and current date
    or
    How can i set through javascript my defaults one but there are still passed into crosstab.

    Tnks again!

  • ykud

    Dimitris,
    I’d repeat my suggestion: try CurrentPeriod category in Transformer cube you’re using as a source to get current date and year. It’d be easier than any javascript i can think of.

    Sometimes, when prompt values are not reprompted\passed, you can add a javascript refresh command and therefore reload the report.