i have a dimensional report with a value prompt where i have 1 static option where i use null and display'Null'. when i say use null, i just actually leave the use option blank. my question is how do i set the default as this option? since it is null, i cant actually set the default option box. any ideas are appreciated.
Not sure if this will help, but you could use null as a default value in a prompt macro. Like:
#prompt('MyParm','token','NULL')#
thx for the suggestion. let me add some detail to see if or how this can be applied.
i have a prompt that allows the user to select the required budget type. that value is then passed in a cascade to a second prompt that returns only the values applicable for that particular budget type. in the second prompt, i use a case statement to return the right values based on the selected value in first prompt. here is the code for the second prompt (data item in the second prompt query):
=============================================
case caption([Profit and Loss].[Budget].[Budget].[Version]->?Budget?)
when 'ACTUAL' then
case caption([Profit and Loss].[Budget].[Budget].[Version])
when 'ACTUAL' then 'vs PY'
when 'Business Plan Year 1' then 'vs FBP'
when 'March Update' then 'vs MU'
when 'June Update Year 1' then 'vs JU'
when 'September Update' then 'vs SU'
when 'October Update' then 'vs NU'
else '-'
end
when 'Business Plan' then
case caption([Profit and Loss].[Budget].[Budget].[Version])
when 'ACTUAL' then 'vs PY'
when 'October Update' then 'vs NU'
when 'Preliminary Business Plan Year 1' then 'vs PBP'
else '-'
end
end
=============================================
when ACTUAL or Business Plan is selected, the parameter is passed to the cross tab which sets the value in the column. when the static value null is selected, null is passed to the cross tab and a blank column is displayed. so functionally, everything works as i need it to. i just now want the second prompt to default to null so that if no selection is made, null is automatically set.
where would i put that prompt macro statement in this code for the second prompt?
or alternatively, how do i modify the case to accept the 'Null' static value?
[case caption([Profit and Loss].[Budget].[Budget].[Version]->?Budget?)]
thanks for your input.
Try leaving the default value on the prompt blank, and use a prompt macro like this:
case caption([Profit and Loss].[Budget].[Budget].[Version]->#prompt('Budget','token','NULL')#
it didnt like that:
XQE-V5-0017
V5 syntax error found for data item 'Version Ops4' of query 'Budget Ops', invalid token "->" found after "case caption([Profit and Loss].[Budget].[Budget].[Version]->[Profit and Loss].[Budget].[Budget].[Version]".
Syntax - looks like I left off a right parenthesis for the caption function....
case caption([Profit and Loss].[Budget].[Budget].[Version]->#prompt('Budget','token','NULL')#)
yeah, i saw that and adjusted for that. it didnt like it.
So stepping back and taking a look (after having coffee), the caption function will only return a caption for a valid MUN.
case caption([Profit and Loss].[Budget].[Budget].[Version]->?Budget?). Null won't give you a valid MUN
How about having your case statement evaluate the prompt instead.
case
when #prompt('SecondParam','token','NULL')# is not null
then
case caption([Profit and Loss].[Budget].[Budget].[Version]->?Budget?)
when 'ACTUAL' then
case caption([Profit and Loss].[Budget].[Budget].[Version])
when 'ACTUAL' then 'vs PY'
when 'Business Plan Year 1' then 'vs FBP'
when 'March Update' then 'vs MU'
when 'June Update Year 1' then 'vs JU'
when 'September Update' then 'vs SU'
when 'October Update' then 'vs NU'
else '-'
end
when 'Business Plan' then
case caption([Profit and Loss].[Budget].[Budget].[Version])
when 'ACTUAL' then 'vs PY'
when 'October Update' then 'vs NU'
when 'Preliminary Business Plan Year 1' then 'vs PBP'
else '-'
end
end
else NULL
end
got another error...see pic:
here is the new code for reference:
case
when #prompt('Ops4','token','NULL')# is not null
then
case caption([Profit and Loss].[Budget].[Budget].[Version]->?Budget?)
when 'ACTUAL' then
case caption([Profit and Loss].[Budget].[Budget].[Version])
when 'ACTUAL' then 'vs PY'
when 'Business Plan Year 1' then 'vs FBP'
when 'March Update' then 'vs MU'
when 'June Update Year 1' then 'vs JU'
when 'September Update' then 'vs SU'
when 'October Update' then 'vs NU'
else '-'
end
when 'Business Plan Year 1' then
case caption([Profit and Loss].[Budget].[Budget].[Version])
when 'ACTUAL' then 'vs PY'
when 'October Update' then 'vs NU'
when 'Preliminary Business Plan Year 1' then 'vs PBP'
else '-'
end
end
else NULL
end
just to be sure, what should go in 'SecondParm'?
#prompt('SecondParam','token','NULL')#
right now i have it set to the parameter that the prompt passes to. see image.