title: Using @Prompt in sql using SAP BO WEBI 4.2 SP3
body: <p>I'm running a series of reports where time window called in query is rolling, and individual per report.. Some reports look 400 days back, others look 10 weeks back, while others again look at -40days/+80days... and so on - many options.
  All reports are scheduled in daily or weekly runs, meaning setting prompts will require a manual reset of prompt for every instance through the scheduler. Not optimal.</p>
  
  <p>I know the universe designer can design specific filters to drag into the queries using the query designer, but with so many different options, I find it a bit of an issue that the universe designer should create specific filters for these specific purposes, adding a vast number of specific filters intended for specific use to various universes.</p>
  
  <p>I'm after an option where it is possible to assign a calculation to a date field, which stay fixed for the purpose of the report for every scheduled instance. </p>
  
  <p>For instance, looking at invoice date from 400 days before today and onwards would look like <code>Where DIM_TIME_INV.DAY_DAY &gt;= sysdate -400</code> - This I can hardcode into the SQl of the specific report, and it will stay through the scheduler and roll 1 day for every day the report is run. But if I decide to make a change in the query elements, the SQl is screwed, and I will have to manually add the modification to the SQL again.</p>
  
  <p>I found an <a href="http://business-objects-xi.blogspot.com/2009/06/prompt.html" rel="nofollow noreferrer">article</a> reg. the use of <strong>@Prompt</strong> and would ask universe designer to try and sandbox this in our version of BO.
  While I'm being impatient, I try myself using following code based on example 4 from linked article.</p>
  
  <pre><code>SELECT
    @select('DIM_TIME_INV.DAY_DAY') &gt;= sysdate -(@prompt('Invoiced, days before today:','N',[DIM_TIME_INV.DAY_DAY],mono,free))
  FROM
    DIM_TIME_INV
  </code></pre>
  
  <p>Testing the SQL gives following error:
  ORA-00936 
  SAP kba 2054721</p>
  
  <p>The whole idea is to have a flexible yet consistent dimension that will calculate every time the report is run, without losing the code whenever new items are added to the report.</p>
  
  <p>Does anyone know of a way to use the <strong>@Prompt</strong> in SQL for SAP WEBI 4.2? - Or any other way to have 'flexible' time dimensions where it is possible to set a from-date or to-date independently or even a range, without having universe designer creating a s**t-load of filters and dump in various universes.</p>
  
  <p>Thanks // C</p>