Sets

The Set function look quite mundane and even unimportant at first glance that you would not think much of it. In fact, I did not see it used during my search for solutions to Cognos problems. I just stumbled upon it by chance. But when I did, I considered it one of the milestones of my dimensional reporting study.

I compare the Set function to ropemanship. In your daily routine, tying the right knot is not really essential; at best it can make things easy for you. However in certain situations, like rappeling or hanging over a cliff and a rope is tossed to you, knowing how to tie the right knot is a matter of life and death.

Ok so that might be a bit overdramatic. But the very same logic can be said about Set – it usually just make things easy for you, but there are times when its the only solution to a problem. Now lets get to the technical details; if one have the following members in the query: [Jan], [Feb], [Mar] and want to use them as columns for your crosstab, you can drag them to the columns section one by one and place beside each other:

Or you can use a set: [Chosen Months] = set([Jan];[Feb];[Mar])

Both will give the same result.

So whats the big deal with sets? Well you can add other members as long as the said member belongs to the same hierarchy: [Chosen Months] = set([Jan];[Feb];[Mar];[Q1]) And you can also add calculated members from the same hierarchy: [Last Year Q1] = lag([Q1];4) [Chosen Months] = set([Jan];[Feb];[Mar];[Q1]; [Last Year Q1])