Skip to Content
author's profile photo Former Member
Former Member

Show lowest granular prompt when multiple prompts selected

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jan 05, 2014 at 05:52 AM

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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 05, 2014 at 08:44 AM
    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • author's profile photo Former Member
    Former Member
    Posted on Jan 06, 2014 at 05:39 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.