Cognos BI macro functions are like regular expressions, you can revisit them any time and find a lot of new things as well as a lot of things you forgot since you last looked at it )

I had a chance to play with quite complex use case lately and even wrote an article to our PMsquare newsletter. Signup for it, there’s really good content there (when I’m not involved) and we’ll be talking about TM1 and JMeter in next edition, yay!

Before we jump into the article, just a quick macro trick I keep reinventing all the time:

An expression like ‘if string1 contains string2 then output string3’ in Cognos Macro functions would look like this

substitute('grepMe','',
	grep('grepMe',
		split('splitMe',
			substitute(string2, 'splitMestring3grepMeSplitMe', string1)
			)
		)
	)

And the article itself:

Some of us already use Cognos BI macro functions quite extensively, but it’s not every day that a project requires you to go to the extreme macro weaving level. In this article, I will illustrate the power and flexibility of macro functions in Cognos using a specific example.

A recent project I was working on required using Cognos for operational reporting on an Infor / BAAN ERP system.

BAAN creates a set of identical tables for each of the registered companies with the table names in the following syntax: ttcibd001[COMPANY_CODE]

For example,, if you register two companies:

1. A with code‘10’
2. company B with code ‘20’ , you would have two tables named as follows:
3. Company A: ttcibd00110
4. Company B: ttcibd00120

Therefore, if you want to re-use the report you’ve created for company A, you are required to create a copy of the query subject (or the whole FM model) and repoint all the objects. This greatly increases maintenance and the more companies you have, the worse it gets.

If we leave the ‘proper DWH’ approach aside at the moment, a more clever and maintainable approach would be to use the general model based on the queries like this:

select 10 as company, ttcibd00110.* from ttcibd00110
union all
select 20 as company, ttcibd00110.* from ttcibd00120

We could then use the model level filters to select one or multiple companies to report on. Setting security on this company dimension allows complete re-use of the reports across multiple entities and greatly simplifies development & support.

Such a task obviously calls for macro functions, but it is not an easy path to follow. The thought process is listed below:

1. We can use promptmany(company_code) to retrieve the list of company codes to retrieve in the final query.
2. If you look closely at the required SQL, you can see that we need company code 3 times:

select COMPANY_CODE as company, ttcibd001COMPANY_CODE.*
     from ttcibd001COMPANY_CODE

3. We can call the promptmany statement three times to get the required number of company_codes to form the required expression. Promptmany returns the list of selected companies so if you select companies A and B, you will get an (A,B) as the result. So to construct our three-part select statement we can call promptmany three times with trailing characters to get lists of (A1,B1) , (A2,B2), (A3,B3)
4. We then combine the (A1,B1) , (A2,B2), (A3,B3) lists and sort them to get (A1,A2,A3, B1,B2,B3)
5. Replacing 1,2,3 with appropriate parts of the target expression will give us the correct expression. The best way to do a replace in macro functions, counterintuitively, is not #substitute#, as it works only for the first occurrence of the expression. Instead, the combination of join and split functions (split by the string you want to replace and join by the string you want to replace it with) will create the correct expression.

And the resulting expression (in case you have trouble sleeping):

#
'SELECT '+
substitute('UNION ALL SELECT removeMe', '' ,
    join('',
        split('a3',
            join('.',
                split(' a2',
                    join('',
                        split('a1',
                                join('',
                                    array(
                                        sort(
                                                array(
                                                    split('!',    
                                                        csv(
                                                            split(',',
                                                                promptmany('Company','integer','1,2') +','
                                                                )
                                                            ,’ a1AS [Company],table!','')
                                                        )
                                                        ,
                                                        split('!',    
                                                        csv(
                                                            split(',',
                                                                promptmany('Company','integer','1,2') +','
                                                                )
                                                            ,' a2* from table!','')
                                                        )
                                                        ,
                                                        split('!',
                                                            csv(
                                                                split(',',
                                                                    promptmany('Company','integer','1,2') +','
                                                                    )
                                                                ,' a3UNION ALL SELECT !','')
                                                        )
                                                )
                                            )
                                            ,'removeMe'
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
#