cancel
Showing results for 
Search instead for 
Did you mean: 

dynamic filters based on optional prompt value

Former Member
0 Kudos

Hi there,

I am very new to business Objects, I am using BO 4.1 SP03. My business requirement is as below but I am not sure how to set this up in.

I have Universe built in IDT sourcing data from SQL database. I have field in database called Sales Cat type with 4 types 1,2,3,4.

I have build Webi report on top of this universe with optional prompts - prompt 1, prompt 2, prompt 3, prompt 4.

When prompt 1 is Null then I want all the sales category data. But when Prompt 1 is not null then I want only 1.

I want this to be dynamic and not static, I know that in static mode I can set filter on table itself.

It is very urgent and thanks so much for your excellent expert advices in advance.

Thanks

Happy

Accepted Solutions (0)

Answers (5)

Answers (5)

Former Member
0 Kudos

Hi All,

I have figured out a way to do this, term for this is cascading prompt which works fine, which gives me prompt 1 and sales category associated to that.

it is now coming as Prompt 1 = 1 then sales category is coming up as cat 1 and cat2.

What I want to do is now if I fill data in prompt 1 then sale cat should 1 by default without user entering the value. and when I don't enter anything then sales cat should be empty.

I am setting up default value as cat 1. When I fill prompt 1 then that works fine but when not selecting anything for prompt 1 as sales cat is set to cat 1 it is filtering all the data by that.

how to solve this

Thanks

Happy

Former Member
0 Kudos

Thanks all for your reply,

Gajawada I am not sure where and how can  I do that in IDT, can you please help me understand ?

When i am using case when it is throwing me error ,may be I am not using in the right area.

Thanks

Happy

Former Member
0 Kudos

Hi,

CASE statement works on for this requirement as Yuvraj said, you can get the desired output for this.

You can alternatively use If Else also to get this. If you are using IDT then create custom LOV with

IF Else then your problem will be solved.

Regards,

G.Sainath

Former Member
0 Kudos

Hi,

Can you try creating a universe level filter something like below in Business Layer and check:

CASE

    WHEN @prompt (<< define your first prompt here >>) is NULL THEN sales_category column

    WHEN @prompt (<< define your first prompt here >>) is NOT NULL THEN 1

 

  ELSE

END..

You can drag this filter as your query filter and check.

Its just an idea. Check if it helps in any way.

Regards,

Yuvraj

Former Member
0 Kudos

Hi,

I am assuming the prompt is on Sales Cat Type. If that is the case, then why not use only one prompt and make it as optional and use the In List Operator in the prompt condition. If you dont enter anything, you will get data for all Cat Type else only for the Cat Type entered.

Former Member
0 Kudos

Thanks Kashif,

I am not sure where and how to set this up, can you please help where exactly I how to set this in IDT ?

Thanks

Former Member
0 Kudos

You can set the prompt as optional in Webi report. The below prompt comes up when you click on Prompt Properties :-