Oracle Business Intelligence

Products Banner

Report to display negative values only.

Received Response

Hi all,

I have a requirement where in my report should display results based on dashboard prompt values.

For example, a dashboard prompt which should contain values "ALL" and "Negative only".

When the user selects "ALL" in DB prompt then my report to display all the values (both +ve and -ve measure column values).

If i select "Negative only" then my report should display only negative measure column values.

DB Prompt column can be a measure column.

Please let me know how to achieve this.




  • Hi,

    What did you try so far?

    Because ... it sounds like all you need is a filter. If you have a prompt with "all" and "negative only" then you need a filter based on that value.

    If you apply some logic you can combine things: prompt = negative AND your column < 0 gives you negatives if you selected negative in the prompt.

  • I would achieve this with a prompt that sets a presentation filter and then some case logic on your numeric field to do what you describe based on the value set in the presentation variable.

  • asim cholas
    asim cholas ✭✭✭✭✭

    create a variable prompt and add two custom values

    All and Negative

    add a filter in report like below (Try to find the maximum value BI Supports and keep there (9999999999)

    YourMeasureColumn< case when '@{YourPresentation Variable}{All}' ='All' then 9999999999 else 0 end

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi Asim,

    I have created a variable prompt with ALL and Negative custom values.

    In my measure column formula, i have written the following formula.

    case when '@{PV_Negative}{ALL}' ='ALL' then "- Inventory"."Projected balance"

    when '@{PV_Negative}{Negative}' ='Negative' then ??? (HERE I HAVE TO SHOW NEGATIVE VALUES ONLY from the column "- Inventory"."Projected balance")

    else 0 end

    How to achieve -ve values here?



  • Did you read what has been posted above? "prompt = negative AND your column < 0"

    But this will gives you 0 when numbers are positive, you need that logic in a filter, not in the column formula (formula change the values you see, filters limit the values returned by the query, so it's kind of obvious why you need to do that thing in a filter).

  • asim cholas
    asim cholas ✭✭✭✭✭

    Do not edit your current measure column.

    add a filter for "- Inventory"."Projected balance"

    convert it to sql


    and paste the below formula in it

    "- Inventory"."Projected balance"< case when '@{YourPresentation Variable}{All}' ='All' then 9999999999 else 0 end

    Variable prompt should be like below.