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:
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
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:
#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:
...and
(
(
#prompt('Category','token','"All Data"')#='All Data'
AND
1=1
)
or
s.shop_category = #prompt('Category','token','"All Data"')
)
and ...