prompt not mandatory

[Avisto.sbu]

Hi,

I'm using SQL into a query of report studio,
and I don't want prompts to be mandatory

If the value is not entrered it should be all values

here is my sql:

Code Select Expand


select
SUM( ca.X)/(select SUM(X) from X where left(ref_month,4) =#prompt('Year','integer','2014')#)  CA,
CONVERT( DECIMAL,SUM( p.Y),2)/(select SUM (Y) from Y where left(ref_month,4) =#prompt('Year','integer','2014')#) TRAFIC,
f.faisceau_country PAYS

from
X ca
,
Y p
,
dim_dates d
,
dim_faisceaux f
,
dim_shops s

where
ca.ref_shop =s.shop_id
and
p.ref_shop =s.shop_id
and
ca.ref_faisceau =f.faisceau_id
and
p.ref_faisceau =f.faisceau_id
and
ca.ref_month =d.month_key
and
p.ref_month =d.month_key
and d.year_key =#prompt('Year','integer','2014')#
and d.month_name =#prompt('month','string','')#
and s.shop_category =#prompt('Category','string','')#
and s.shop_terminal =#prompt('Terminal','string','')#
and s.shop_zone =#prompt('Zone','string','')#
and s.shop_category <>'N/A'

group by
f.faisceau_country

thanks

[MDXpressor]

An optional prompt isn't the problem here, you made the prompt optional by including the <default value> parameter of the #prompt('<name>', '<datatype>, '<default value'>)# macro.  What you're missing is the escape clause in your filter (1=1) whenever the prompt is unanswered.  To accomplish this try a conditional filter statement for each prompt. I'll show you what the one for s.shop_category could look like below.  You can replicate it for each of the prompts.  As a side note, with a default value of 2014 in your 'Year' prompt, you are going to be filtering against 2014, because that value actually exists in your dataset.  When I create a default a prompt for an optional filter, I usually choose a default value which doesn't exist in the dataset:  "All Data"

So, my prompt macro would change from yours slightly for Category:

Code Select Expand

#prompt('Category','token', '"All Data"')#

Now you have to make your filter clauses a little more complex.  Each prompted where clause needs a conditional filter, so for Category's where clause:

Code Select Expand

...and
(
   (
   #prompt('Category','token','"All Data"')#='All Data'
         AND
    1=1
    )
    or
    s.shop_category = #prompt('Category','token','"All Data"')
)
and ...