cancel
Showing results for 
Search instead for 
Did you mean: 

WEBI prompts and Filtering

Former Member
0 Kudos

Hi all,

I am new to BO and I am working in BO XI R3.

I have to create a WEBI report which will have two prompts.

1.first prompt is based on AMOUNT column in my report eg: Amount(LOV): $55000

2.second prompt is not based on any column in the report or universe. but it should accept the value from the user eg: Range value: $3500

The result should show all the rows that are having Amount>= $51500 (i.e $55000-3500) and Amount<=$58500 (i.e $55000+3500)

Can any one suggest what steps I need to follow in webI or designer to get this type of results.

Thanks!

Edited by: undefined on Dec 14, 2009 5:07 AM

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi,

As rohit said you can create two variables and assign the prompts to them. Same for validating also.

For your requirement I think creating prompts at report level is better.

Cheers,

Suresh Aluri.

Former Member
0 Kudos

Hi,

This can be done in the following steps:

Create two prompts which will ask for the input from the user. You can create prompts at the report level or universe level.

Say the prompts string are : Enter Amount and Enter Range.

Now at the report level retrieve the two user response in the two separate variables.

v Amount = ToNumber(UserResponse("Enter Amount"))

v Range = ToNumber(UserResponse("Enter Range"))

Now create two more variables as:

v Max Amount = v Amount + v Range

v Min Amount = v Amount - v Range

Now apply the global filters on the Amount as Amount <= v Max Amount and Amount >= v Min Amount.

Note: you need to take care of $.

Regards,

Rohit

Former Member
0 Kudos

Rohit,

Thanks for you reply. I tried creating prompt(filter) in the universe level as @Prompt('Enter a range value','N',,mono,free,) in the where condition and draged it in filter panel in webI report. It is accepting the value and throwing me this error as

"A database error occured. The database error text is: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.. (WIS 10901)".

Can you explain me where I am going wrong?

Thanks,

Former Member
0 Kudos

Hi,

You are using wrong syntax, go with the following syntax for XI R2:

@Prompt('Enter a range value','N',,mono,free)

if you are going for the extended prompt syntax then use the following:

@Prompt('Enter a range value','N',,mono,free,not_persistent,)

Regards,

Rohit

Former Member
0 Kudos

Hi,

I got the same error for @Prompt('Enter a range value','N',,mono,free,not_persistent,) syntex. I am working in BO XI R3.

Is there any other option?

thanks

Former Member
0 Kudos

Thanks for your reply

I tried these steps.

1. I created an object Named "Range Value" and put @Prompt('Enter value','N',mono,free,not_persistent,) in where condition in the universe.

2. In webi report I dragged this "Range Value" object in the query filter panel and selected it as prompt and put prompt message as 'Enter value'.

3. After I run the query I am getting an error as "A database error occured. The database error text is: [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.. (WIS 10901)".

4. I tried to change the prompt message in query filter as 'Enter a range value' and run the query. this time it prompted me with 2 prompt messages

a.Enter value (the Prompt message which I gave it in the object in the universe):

b.Enter range value(the Prompt message which I gave it in the query filter panel):

5. when I entered same values in both the prompts again it was throwing me the same above error.

6. Later I tried to put 'Enter a range value' prompt as optional and then run the query. this time I entered value in only one prompt and it worked. BUT the problem is that it shows 2 prompt Messages.

7. I repeated same procedure to create another prompt "Enter Amount" and the same thing happen 2 prompt messages

a."Enter Amount"

b."Enter Amount value"

How can I avoid showing 2 prompt messages for one object?

I avoided that and created all below variables in the webI report

a.v Amount = ToNumber(UserResponse("Enter Amount"))

b.v Range = ToNumber(UserResponse("Enter value"))

c.v Max Amount = v Amount + v Range

d.v Min Amount = v Amount - v Range

now when I was trying to filter the report with Max Amount and Min Amount I am not able to select the variable in the WEBI filter panel. It only gives me LOV of that columns.

Where I am going wrong?

how can I avoid 2 prompt messages for one object and how to filter the amount with min and max values.

Can you please explain me more in detail.

thanks,

Edited by: undefined on Dec 15, 2009 5:58 AM

Edited by: undefined on Dec 15, 2009 5:59 AM

Former Member
0 Kudos

Hi,

You are getting this error because I guess you are not giving LHS for comparison.

Create objetcs in the universe as:

Simple Measure - Amount (only select clause no where clause)

Now create a condition object as:


((@Select(Class\Amount) <= (@Prompt('Enter Amount','N',,mono,free)+@Prompt('Enter Range','N',,mono,free))) 
And (@Select(Class\Amount) >= (@Prompt('Enter Amount','N',,mono,free)-@Prompt('Enter Range','N',,mono,free))))

Note: The Amount should be fetched without $ sign from the repository.

Drag this condition object in the condition panel and amount in the select clause. Now you don't need to create any objects on the report level.

Regards,

Rohit

Former Member
0 Kudos

It worked. Thank you!