today I will deal with macro functions. I had a customer who wanted to switch between two dimensions to drill up & down in a dimensional report. That means you have to dynamically change the MUN(=member unique name) for this element to decide whether to use dimension A or dimension B.
The two MUNs for the dimensions looked like this on the highest level:
- [Dimensional].[PG].[PG].[Product Group]
- [Dimensional].[AX].[AX].[Target Group]
To solve this task I used the following macro functions:
- Operator +
- Function substitute (<search pattern>, <replace text>, <orig text>)
- Function prompt (<p_name>, <data type>, <default>)
To use macro functions you have to define a macro block like this: # ….… #. Between the # symbols you will place the macro functions. The macro then defines the expression throughout the macro functions. In our case it will result in one of the two MUNs described above.
Example: You could create a query with a query item that contains the following macro function: # ‘Test1‘+‘Hello‘ #. This will result in the string Test1Hello at runtime in this query item. It’s like you would place this string in the query item by yourself but with macro functions you have the opportunity to dynamically define such expressions.
If we take a look at our example. We’ve create a text: Test1Hello. If we placed this into the query item we would receive an error because of the syntax. We need to put ‘ ‘ around it to make it a string. For this we can use the macro function sq( … ). Sq stands for single quotes. We will enhance our little example to this: # sq(‘Test1‘ + ‘Hello‘) #. This will result in the string ‘Test1Hello‘ and no error will occur. By the way the operator + concats two strings.
To get back at my customer’s problem. The function substitute(…) will replace the string <search pattern> within the string <orig text> by the string <replace text>. Prompt(…) will ask for parameters and returns the value of a parameter. First argument of this function is the name oft he parameter, second the datatype and third a default value. The third argument is optional. Regarding the datatype. There is the string which is text with single quotes around it and there exists token which is a text too but without single quotes. If you have a prompt page with this parameter it won’t be asked for it again but the value is returned here as well.
With the knowledge of these functions we can now come to the solution for my problem:
# ‘[Dimensional].[' + prompt('p_proddim', 'token' ,'PG') + '].[' + prompt('p_proddim', 'token' ,'PG') + '].[' + substitute('AX', 'Target Group', substitute('PG', 'Product Group', prompt('p_proddim', 'token' ,'PG'))) + ']‘ #
The parameter p_proddim contains the selected dimensions (‘PG’ or ‘AX’). First we take the constant text at the beginning (‘[Dimensional.[‘) then we concat the content of p_proddim then the string ‘].[‘ and then again the content of p_proddim. The result is: [Dimensional].[PG].[PG].[ Instead of PG could also be AX depending on the parameter’s selection. The next part is something like an if-then-else. But it’s realized via a nested substitute call. The inner substitute takes the parameter value of p_proddim and checks the value for ‘PG’. If it is found it’s replaced by ‘Product Group’. If not nothing happens. The result of this substitute is the text to be checked in the outer substitute. This one is checked for ‘AX’ and is then replaced by ‘Target Group’.
This is one example how to use macro functions. There are a lot others. In future articles I will deal with macro functions again.
If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!