Using @Prompt in sql using SAP BO WEBI 4.2 SP3

Question

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.

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.

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.

For instance, looking at invoice date from 400 days before today and onwards would look like Where DIM_TIME_INV.DAY_DAY >= sysdate -400 - 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.

I found an article reg. the use of @Prompt 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.

SELECT
  @select('DIM_TIME_INV.DAY_DAY') >= sysdate -(@prompt('Invoiced, days before today:','N',[DIM_TIME_INV.DAY_DAY],mono,free))
FROM
  DIM_TIME_INV

Testing the SQL gives following error: ORA-00936 SAP kba 2054721

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.

Does anyone know of a way to use the @Prompt 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.

Thanks // C

Joe Joe · Accepted Answer · 2017-01-18T13:18:29

With regard to your example code, I think you're on the right track but your syntax has some issues:

SELECT
  @select('DIM_TIME_INV.DAY_DAY') >= sysdate -(@prompt('Invoiced, days before today:','N',[DIM_TIME_INV.DAY_DAY],mono,free))
FROM
  DIM_TIME_INV

First, both @Select and @Prompt must refer to universe objects, not columns. The syntax for both is: class name\object name. Assuming that the DIM_TIME_INV.DAY_DAY is associated with a universe object named Day Day in a class named Dim Time, the above code should be:

SELECT
  @select('Dim Time\Day Day') >= sysdate -(@prompt('Invoiced, days before today:','N','Dim Time\Day Day',mono,free))
FROM
  DIM_TIME_INV

Also note that the object reference in the @prompt call is delimited by single quotes, not brackets.

Next, I'm assuming that DAY_DAY is a date field. Its reference in the @prompt call would cause the prompt to display a list of values, sourced from DAY_DAY. But you want a numeric value from the prompt, not a date, so I would just leave that out, which will let the users enter a numeric value:

SELECT
  @select('Dim Time\Day Day') >= sysdate -(@prompt('Invoiced, days before today:','N',,mono,free))
FROM
  DIM_TIME_INV

Next, even with this corrected syntax, there will be an issue using this code as you have it. A good way to debug @prompt issues is to view the SQL in the WebI report after you get the error -- the SQL will show the rendered result, with all functions (@select and @prompt) expanded. For the above, you might get SQL like:

SELECT
  DIM_TIME_INV.DAY_DAY >= sysdate -(400)
FROM
  DIM_TIME_INV

This, of course, is invalid - you can't have a condition in the SELECT clause. If this is truly intended to be a condition (which I think it is, based on your objective), then it should be a predefined condition rather than a dimension.

With that said, I think you're on the right track for what you want to do. With the above corrections, you would have a predefined condition that you could drop into reports, which would enable the users to select the starting period (by number of days ago) for the report. You could create additional prompts with different logic, ex:

@select('Dim Time\Day Day') >= sysdate -(@prompt('Invoiced, weeks before today:','N',,mono,free) * 7)

or

@select('Dim Time\Day Day') 
BETWEEN sysdate - @prompt('Starting days ago:','N',,mono,free)
    AND sysdate - @prompt('Ending days ago:','N',,mono,free)

Using @Prompt in sql using SAP BO WEBI 4.2 SP3

1 Answers