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:

Sales	Month
	Sales Target	Sales
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...

why Filters Should Be Avoided When Working with a Dimensional Model

I can see three scenarios when you may think that you need to use a filter:

Filtering along the axes of a crosstab

Say you have a typical crosstab with one dimension along the horizontal axes and another one along the vertical axes. e.g.

<Sales Total>	<Date>	<Date>
<Product Type>	<1234>	<1234>
<Product Type>	<1234>	<1234>

which would give you something like:

Sales Total	1/1/2007	...	04/21/2008	04/22/2008
Camping Equipment	1,000$	...	1,000$	1,000$
...	...	...	...	...

You want to filter the time dimension you only report for the year 2008. Or you want to only include product types with "camping" in the name. What you really want in this case is to build a memberset that you will then use as the axes of the crosstab. When you drag-and-drop a dimension level in Report Studio it really translates to something like members([my_Dimension].[my_Hierarchy].[my_Level])
e.g.
members([Product Dimension].[Product Type Hierarchy].[Product Type])
(Check "Corercion Rules" in the Report Studio documentation.)

Instead of getting all members you need to build a member set. You can do this by putting members together... or taking members away from the full set.

The former could be done with techniques such as
[my_Dimension].[my_Level]->?Parameter1?
e.g.
ancestors([Time Dimension].[YMD Hierarchy].[Year]->?Year?, [Time Dimension].[YMD Hierarchy].[Day])

the latter could be done using the filter() function
e.g.
filter([Product Dimension].[Product Type Hierarchy].[Product Type], roleValue('_memberCaption', currentMember([Product Dimension].[Product Type Hierarchy])) contains 'camping')

Filtering along a dimension not present in the crosstab
...to continue the example above you may only want to display sales that were done by a certain branch. Branch does not appear on the axes of the crosstab... so again filter may seem like a good idea. What you really want to do here is to tune the measure that gets displayed. when you drag-and-drop a measure in Report Studio is really translates to something like value(tuple([measure]))
This tuple is incomplete, and will use currentMember() for dimensions that are present in the context and defaultMember otherwise.
e.g.
value(completeTuple([Sales Total], currentMember([Product Dimension].[Product Type Hierarchy].[Product Type]), currentMember([Time Dimension].[YMD Hierarchy].[Date], defaultMember([Branch Dimension].[Location Hierarchy].[Branch])))

The default member is the "all" at the top... in other words the Sales Total will be rolled up and you get the sum of sales done in all the branches. Instead of using a filter you need to tweak the tuple expression to use something other then the defaultMember()
e.g.
tuple([Sales Total], [Branch Dimension].[Location Hierarchy].[Branch]->?Branch?)
will translate to
value(completeTuple([Sales Total], currentMember([Product Dimension].[Product Type Hierarchy].[Product Type]), currentMember([Time Dimension].[YMD Hierarchy].[Date], [Branch Dimension].[Location Hierarchy].[Branch]->?Branch?))

Filtering based on some attributes of the facts
This is the last scenario I can think of ... this is when it goes wild.
You want to apply a filter to the whole thing. e.g. only count sales where the customer payed in US dollars.
You don't have a dimension but you know that you facts do have an attribute that you could use to build a filter.
e.g. Somewhere you had a Sale record with a CurrencyUsedToPay field.

What really happened here is... you missed to create a dimension.

If you use DMR a filter would probably work... it's just not god design. If you use real OLAP then everything is preaggregated... it's too late to try to filter the facts.
You need to revisit the design of your Sales Total cube.

One last though about DMR
Not only is the use of filters "not nice"... often it can give you incorrect results. To return to our first scenario... if you filtered product types using filter and one day you want to add a new row to your crosstab to display the sales total for all product types... you would be in trouble.

Sales Total	1/1/2007	...	04/21/2008	04/22/2008
Camping Equipment	1,000$	...	1,000$	1,000$
...	...	...	...	...
Product Types (All)	...	...	...	...

The way Cognos processes the filter would add a WHERE clause to the SQL SELECT. At the end "all product types" would become "all the selected product types".

Conclusion
The Cognos Report Studio GUI does not distinguish between relational and dimensional models. It always looks the same. This is misleading because certain features should only be used when working with one type of model and not the other.
In case of DMR it's even worse because Cognos tries to interpret these (filter, join, usion) wvwn though it conceptually does not make sense. Sometimes you get what you wanted but often not.

In my opnion detail or summary filter should only be used when working with a relational model.

custom roles do not wok

I tried setting custom roles in a DMR model.

The feature is a total screw up. It just simply does not work.

The roleValue() function does not return an error when I set the role string to something dummy... a role that does not even exist. If just did not return anything.

This would be OK.

The bigger problem is that even when I set it to a proper role type that was created in the model it still does not return anything.

Conclusion: forget custom roles, at least for DMR.

ps My understanding is that DMR was the only kind of model where custom roles should be used at all.

prompts explained

Prompts provide a way to dynamically change the reports.

In other words their function is to "show only this", "show only that', "show between this day and that day".

I think it would be fair to say that a prompt is what appears on the screen and a parameter is a programming variable that stores the users choice.

So prompts and parameters are very closely related, they are practically the same.

A prompt appears minimum in two places. On the prompt page - where we get the value - and in an expression - where we use the value. (Otherwise it would not make sense...)

If a prompt appears in an expression but you forgot to put it on the prompt page then it will be autogenerated by Cognos. You don't want this... it's much better to have control over the prompt page.

However it can appear in more than two places as it gets used in expressions and even in the model. 

If you use DMR it is a good idea to use prompts in the model because you can optimize the performance. e.g. If the user selects only a single branch of a bank then there is no need to calculate all metrics for every branch, you can use the prompt in the model to set a filter on the queries... and everything will run faster.

There are a few important things about prompts that are not obvious from the documentation:

1. In Report Studio you can use the same #prompt()# function as in Framework Manager. (Actually you can use all macro functions.) 
   What you see - what the editor offers - ?Parameter1? is just a shorthand for #prompt('Parameter1', 'string')#
2. You need to refer to prompts in exactly the same way everywhere, in expressions and on the prompt page. Otherwise Cognos gets confused. Thisnk about this when you set a prompt as "required" but its not, or when the prompt pop up again even though it already appeared on the prompt page.
   The property settings should match how the #prompt()# macro is called. 
3. For a prompt being optional is the same thing as having default value. 
   If a prompt has default value then it is optional.
   If a prompt is optional it must have a default value. 
   The only exception is to set the default selection property in Report Studio and set the prompt as required; and then in the expressions not setting a default value. This way the prompt is required and the prompt page can provide the value set as default if you hide the prompt. - This technique allows you to do some tricks...
4. Remember: prompt is a macro. It is just a string replacement. Whatever appears between the hashmarks will be replaced and then the expression will be evaluated.
   If you get an error you can usually see what the prompt macro was replaced with.
   You can also debug the report by droppin a Text Item, setting its source to "Report Expression" and setting the expression to something like ParamValue('parameter1')
   - do not use ParamDisplayValue() .. use ParamValue()
5. Since the prompts will be used in different expressions you get most freedom by setting the prompt type to "token". This way the replacement value won't have quotation marks around it. This is useful. Check the other macro functions such as split(), sq() to build a string from the prompt as needed.
6. Keep in mind the promptmany() function. It is practically the multi-selection prompt.
   If you want the prompt to handle multiple values then you need to use this version. 

Working with prompts using a dimensional model

The prompt can return the parameter value in a number of ways: string, date, token, MUN (Member Unique Name).

If you don't know what MUN is please read the documentation; it is important. It's basically a string identifier for a member of a dimension. It's an ID... plus some extra that helps to figue out what the ID is for. 

When working with dimensional model you want to build a member set from the prompt selection... pretty much always. 

If you think you only need to build a filter expression please think twice.

For a DMR model I'm pretty sure filters should not be used - at all.

and I have the same feeling for an OLAP based dimensional model.

So, forget filters. They are for the people still in the kindergarden who use relational models for BI.

All you want to do is to say which members should appear in a crosstab (or chart) based on the users selection. What you need is a member set.

To build a member set the "string" type of prompt is useless because it is surrounded by single quotes which does not play well with the MUN (Member Unique Name) format.

Either you can get the MUN directly from the prompt() function or you need to build it yourself. In this case "token" is better. It's the same value being returned as when using "string" only without the quotes.

I find that using the question mark shorthand: ?Parameter1? is the same as #prompt('Parameter1', 'string')#

Frankly I'm not 100% sure... but I still prefer using the macro format to be certain of what I'll get.

The easiest thing to build a memberset is to do this:

set( #promptmany('Parameter1', 'MUN' )# )

It gets challenging when you want to make the prompt optional.

If you only return an empty string or a space then after the prompt is processed the expression will look like set( ) ... which Cognos does not like and gives you an error.

Instead use the emptySet function as the prompt's default valuet:

set( #promptmany('OptionalParameter1', 'MUN', emptySet([my_dimension].[my_hierarchy])# )

To have the prompt return MUN you need to set the "Use Value" of its query to a dimension level.

The other option is to build the MUn yourself in the expression. If the MUN has only one ID then it's simple. Something like 

[my_dimension].[my_hierarchy].[my_level]->[all].#sq(prompt('parameter1','token'))#

With DMR model it is often not the case... even if you set a level as "Uniquely Identified" Cognos still includes the business keys of all upper levels in the MUN... which makes it difficult - almost impossible - to build MUNs.

(I think it is a bug in Cognos DMR.)

You can only do it if you prompt the dimension members themselves. then Cognos will do it.

Otherwise... not.

e.g. If you have a hierarchy in the time dimension it can become difficult. Because you want a date prompt - which is not built from the dimension. It's just a calendar.

That's OK... you don't want to use DMR anyways. It sucks. Use real OLAP and take control of how the MUNs are generated.

I figured that with the promptmany function using tokens they are separated by semicolons.

start with #split(';', promptmany('Parameter1', 'token'))# and use the substitute macro to build a lst of MUNs.

Time filtering: From and To

Often you want to run the report for a specified time interval. this is not at easy as it seams.

If you find that a simple filter is not working for you.. and remember, IMO you should never use a filter... then you'll appreciate this tip as building a member set containing all dates between "from" and "to" gets quite challenging.

filter(
[Time Dimension].[My Hierarchy].[Date],
roleValue('_businessKey', currentMember([Time Dimension].[My Hierarchy])) >= #sq(prompt('From', 'Date'))# and 
roleValue('_businessKey', currentMember([Time Dimension].[My Hierarchy])) <= #sq(prompt('To', 'Date'))#
)

i know it's crazy... but this will save you 2 days minimum.

• we're using the filter() function to "build" the member set - by taking away unwanted members.
  
• we rely on the fact that dates as string can be still compared and will maintain the same order as the dates
• note that roleValue() returns the selected attribute as a string, regardless of the attribute's type - which is date in this example

I'll write a post about why filtering should be avoided... so hopefully I'll convince you that I'm not crazy and all this overcomplicated crap is necessary for real life reporting... I mean something other than GO Sales :)

substitute() macro

It's not documented anywhere so here you go:

^ matches the beginning of the string
$ matches the end of the string

Multiple substitute() calls can be nested into one another.

e.g. to put a string into square brackets you could write

substitute('^', '[', substitute('$', ']', '_string_goes_here'))