Friday, April 25, 2008

when to use completeTuple()

I found a case when you need to use completeTuple even though tuple() - to me at least - would seem like a reasonable choice too.
This is when you have dimensions in context that do not apply.

an example will help

Let's say you have branches who make sales and you set a sales target, however this target is the same for all branches. You set a different target for every month.

Yo want to have a crosstab report showing the sales of each branch for every month and the sales target also:






SalesMonth
 Sales TargetSales
Branch  



The thing is that if you don't use completeTuple() in the expression to get the Sales Target then Cognos will get "confused" by the presence of "Branch" which is not in scope for the Sales Target measure.

You need to write something like this:

Sales Target:=
completeTuple([Sales Target], currentValue([Time Dimension].[YMD hieararchy]))

...especially if your crosstab is more comlex than this example...

No comments:

Post a Comment