An optional prompt macro always has a default value specified, so if no prompt value is selected the default value is applied in the query but this will not cause the preceding / trailing text in the macro to get applied. The preceding / trailing text get applied only if a prompt value is selected.
A simple example to demonstrate this:
We have ProdID and Sales displayed in a report.
The report has an optional prompt macro for ProdId defined as:
[Presentation Layer].[REVENUE_FACT].[PRODUCTID]= #prompt('Prod Id', 'Integer' ,1, '', '', 'and [Presentation Layer].[REVENUE_FACT].[SALES] > 5000' )#
Notice that the prompt macro has a default ProdID value of 1 and a trailing text that will add an additional filter of Sales > 5000.
When no value is selected for the ProdId prompt, the output displays data for Prod Id 1. Notice that the additional Sales filter doesn't get applied.
And here is the output when a ProdId selection of 1 is made. The additional Sales filter gets applied and displays the sum total of Sales for ProdId where Sales > 5000.
Posts
