Advantage of using a prompt macro vs. a prompt in a report?

[bonniehsueh]

I am tasked to create a report with dynamic capabilities such as dynamically selecting columns and filters off of a multi-dimensional data source. I've been doing research to look at my options to achieve this. Many of the examples I've found use prompt macros so I'm curious – what are the benefits of using a prompt macro vs. just using a regular prompt?

I've found prompt macros useful when used in Framework Manager (e.g. dynamically choosing a data source), but have not seen the advantage of using a prompt macro in a report. If anything I've seen it as a pain in a report because you cannot validate a report that uses a prompt macro. You have to replace all the prompt macros with values in order to validate the report. 

The most helpful explanation I've found so far is : "macro is a dynamic object which is used to substitute the values at query run time. prompt is dynamic condition which select the value or enter the value at query run time" http://www.geekinterview.com/question_details/31666

But, what does that mean...? They both provide dynamic values to the report. I'm not clear on what the difference between "substituting a value at query run time" vs. "select the value or enter the value" at query run time ...or I may be focusing on the wrong words...

Thoughts are appreciated. What are the benefits, if any? What am I missing?

[CognosPaul]

Macros, and by extension, prompt macros are among the most powerful tools you can use in Cognos. While mearly useful in relational reports, their applicability in dimensional approaches astronomical proportions. Since you've mentioned that you use dimensional data sources, I will focus on that, but remember that nearly everything I say is applicable to relational as well.

To begin with you need to ensure that you are building your multi-dimensional queries correctly. This post is not a course on dimensional theory but I will give a very brief tutorial.

The most basic output of a dimensional query is a crosstab (CT). The rows or columns of the CT can be made up of members, sets, or measures. The default measure can technically only be a measure. Place years in the columns of the CT, Order Method in the rows and Revenue as the default measure. Every intersection is a tuple. Each intersection can be written as tuple([YearMember],[OrderMember],[Revenue]). Let's say you want to filter those results for only America. In a relational source you would drag the Country field into the detail filters and filter Country = 'America'. In a dimensional it is much simpler. Simply drag the America member into the slicer. The tuple then becomes tuple([YearMember],[OrderMember],[Revenue],[America]).

Why, you may ask, am I saying all of this? Well, let's say you want the use to select a country. Simply adding ?Country? to the slicer won't work. Instead, add this prompt macro:
#prompt('Country','memberuniquename','[Cube].[Dimension].[Hierarchy].[AllLevel].[AllMember]','','[Cube].[Dimension].[Hierarchy].[CountryLevel]')#

Before I explain the macro, let me go through its parameters.
#prompt(ParameterName,Datatype,DefaultValue,PreText,Source,PostText)#

The parameter name is obvious. That is the name of the parameter.
The datatype can include string, integer, memberUniqueName, date and Token (and a few others but I'm ignoring them for now). Token deserves its own section and I'll get to that later.
The default value is what is used if the user does not enter a value. In a dimensional source this does not have to be a value itself, it could be a set function for instance.
The PreText will be placed before the value the user selects or enters.
The source will be used to populate a value or tree prompt for the user to select.
The PostText will be placed after the value the user selects or enters.

This prompt macro will prompt the user for a single MUN (memberUniqueName) from the CountryLevel. If the user does not select a value the All member will be used, essentially leaving the crosstab unfiltered.

What if, in addition to being able to select the country, the user wants to select a range of years, or show the top five years if unselected?
#promptmany('Year','memberuniquename','topSum([Cube].[TimeDim].[YearHier].[YearLevel],5,[Revenue])','set(','[Cube].[TimeDim].[YearHier].[YearLevel]',')')#
This willl prompt the user to select zero or more years from the year level, or return the top 5 years based on revenue.

The token prompt lets you enter functions as opposed to values.
Let's say your users want to be able to select Top 5 Years, Top 5 Countries or All products with less than 100 sales
This is time consuming but easy with a token prompt.
Create the following autosubmit value prompt:
SelectedFunction
Use                                                              Show
topSum([Cube].[TimeDim].[YearHier].[YearLevel],5,[Revenue])      Top 5 Years
topSum([Cube].[Dim].[Hier].[CountryLevel],5,[Revenue])           Top 5 Countries
filter([Cube].[Dim].[Hier].[CityLevel],[Sales]<100)              Products with less than 100 sales


Now in the columns place the macro:
#prompt('SelectedFunction','token','topSum([Cube].[TimeDim].[YearHier].[YearLevel],5,[Revenue])')#
Whatever the user selects will be sent to the prompt and run as if it were part of the query.

Everything I've written barely scrapes the surface of all of the capabilities of the prompt macro. When combined with other macros, the possibilities become endless.

See this for example:
lastPeriods (5,item(filter([Cube].[Time].[Year].[Year],caption(currentmember([Cube].[Time].[Year]))= #sq(timestampmask(prompt('date','token','','','',' 00:00:00.000+02:00'),'yyyy'))#),0))