Show lowest granular prompt when multiple prompts selected

[Former Member]

Hi All,

I need your help here, I have created a cross tab report which has 3 level of User prompts, as U1,U2 and U3, all are InList, now what I need is when I select values in U1 & U2 prompts i want only the U2 prompt values should be displayed on the report
Similarly When U2 and U3 selected I want to see U3 alone in Report, and when U1 & U3 then i wnat to see only U3 values.
only take the lowest granularity

Note U1,U2&U3 are in Hirearchy, U3 reporting to U2 which inturn reports to U1

I need your help on this .Thanks in Advance

[Former Member]

Hi,

You didn't mention the version of BO you are using currently.

I'm able come with the condition to handle your scenario by Creating the condition object at Universe level.But you need to modify this condition object accroding to your requirement.

I have created this object by considering the following hiearachy Deptno>Job>Ename

Forumula for condition object is like below :

(Case When (NVL(@Prompt('3. Select EName','A','Emp\Ename',mono,free),Null) is not Null AND EMP.ENAME=@Prompt('3. Select EName','A','Emp\Ename',mono,free)) THEN 1 WHEN (NVL(@Prompt('2. Select Job','A','Emp\Job',mono,free),Null) is not Null AND NVL(@Prompt('3. Select EName','A','Emp\Ename',mono,free),Null) is  Null AND EMP.JOB=@Prompt('2. Select Job','A','Emp\Job',mono,free)) THEN 1 WHEN (NVL(@Prompt('1. Select Deptno','N','Emp\Deptno',mono,free),null) is not null AND NVL(@Prompt('2. Select Job','A','Emp\Job',mono,free),Null) is Null AND NVL(@Prompt('3. Select EName','A','Emp\Ename',mono,free),Null) is  Null AND EMP.DEPTNO=@Prompt('1. Select Deptno','N','Emp\Deptno',mono,free)) THEN 1 ELSE 0 END)=1

I will try to explain what I'm trying to achieve by using above formula by below steps :

Case 1 : When Ename prompt is not Null then it will filter the data based on ename column

Case 2: When  Job prompt is not null and Ename Prompt is Null then it will filter the data based on Job Column

Case 3: When Deptno Prompt  is not null and Job Prompt is Null and Ename Prompt is Null then it will filter the data based on Deptno column values.

Above I used NVL function to identify wheather prompt value is blank or not. you can try with other functions if you get the required functionality to handle above three cases.

I hope this helps. Let me know if it is working for you or not.

Sampath

[Former Member]

Thanks Sampath, problem here is i dont have access to universe, so i need to handle it in report level. Can we use IF condition to achieve this

??

Thanks for your help in advance..

[Former Member]

Yeah I understood your issue but as we are manipulating the where clause we should do it at universe level or need to use custom SQL to fire the required query at database level.

In Case if you want to implement this at report level you need to get all the data in the report then by using Input Control's you can achieve required result by implement case formula mentioned in my post with IF condition in variable.

Please to be noted that here we are trying to change the query which going to fire on the database that we can't change at report level. we should handle at universe level or query panel level by using custom SQL.

I hope this helps.

Sampath

[arijit_das]

BOBJ Tricks: Cascading Prompts

http://scn.sap.com/thread/3253580

[Webi 4.x] Cascading prompts - BICS connection

[Former Member]

Hi Arijit thanks for your reply, but i have to do it in a report level, since i cannot use Universe ! previously I have used a IF condition and restricting it based on the User response, but since I need to select multiple values if is use If contion= X then its taking only the x values and ignoring the Inlist properties.

[Former Member]

Any help would be much appreciated !! Previously I tried with IF condition which dint work properly. can some one suggesgt me here.