default for value prompt

[avuong6]

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.

[tjohnson3050]

Not sure if this will help, but you could use null as a default value in a prompt macro.  Like:

#prompt('MyParm','token','NULL')#

[avuong6]

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.

[tjohnson3050]

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')#

[avuong6]

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]".

[tjohnson3050]

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')#)

[avuong6]

yeah, i saw that and adjusted for that. it didnt like it.

[tjohnson3050]

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

[avuong6]

got another error...see pic:

[avuong6]

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

[avuong6]

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.