How to create @prompt filter at universe level for my Webi reports

[Former Member]

Dear all,

I am using BO XI 3.1 SP3 with SAP BW 7.0.

I have created an open[big] query based on a multiprovider for my OLAP universe.

OBJECTIVE: to create LOV prompts for Calender year, for my webi reports, which takes 2010 as input then offsets the input year and then shows Gross sales for the prompt year [2010], 2009 & 2008.

ISSUE: if 2010 is selected in webi prompt filter, then it doesn't display any data for any other year, lets say gross sales for 2009.

ALTERNATIVE: i tried to create a prompt filter in the universe with this syntax,

<FILTER KEY="@Select(Calendar year\Calendar year).[TECH_NAME]"><CONDITION OPERATORCONDITION="InList"><CONSTANT TECH_NAME="@Prompt('Enter year','N',,Mono,Free,Not_Persistent)"/></CONDITION></FILTER>

it worked fine. but i also want to create a measure or dimension which subtracts 1 from the above filter and return a result with a previous year.

e.g

PROMPT: 2010

PROMPT-1: 2009

Universe structure

[TIME] class

[Calender year] class

[Calender year] dimension ( type:string)

Please guide me with correct procedure and syntax.

[Former Member · Accepted solution]

Hi,

This can be easily done by replacement Path in BEX query Desingner or by using Different variables for each year and making them mandatory.

Try changing the BEx underlying your universe, that will be mich simpler as you will get , prompts created itself when you create a universe.

Deepak

[Former Member]

masood44 ,

I think you should just prompt for year and capture and use the selection at the webi report level.

say you have calendar year(convert it into numeric) prompt at webi level. say user selects 2010 capture it in a variable var1:userresponse('enter year'). then create a variABLE (var2) with formula :

if(isPromptAnswered('enter year')='yes';([year] where year inlist(var1;var1-1;var1-2);null)

build your report with var2 and gross income

Thanks,

Karthik

Edited by: kbharadwaj79 on Jun 2, 2011 5:34 PM

[Former Member]

i tried to capture year, converted it and then subtracted it. but the initial prompt filter on webi limits the data to year taken in the prompt.

if 1 take 2010 in prompt then data(gross sales) for 2009 or 2008 will show null as filter is applied on year 2010

[Former Member]

then u shudn't limit the prompt to only 2010 initially. rather allow a range as you need to show data for history.

lets say that the current query has to be limited to only 2010 bcos of the requirement then build another query and this time pull data for 2010 2009 2008 and then merge the dimensions to achieve the data