Report to display negative values only.

User_JL3CW · October 2017

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.

Regards,

Chandra.

Gianni Ceresa · October 2017

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.

BeginnersMind · October 2017

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 · October 2017

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 · October 2017

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?

Regards,

Chandra.

Gianni Ceresa · October 2017

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 · October 2017

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.

Oracle Analytics Cloud and Server

Categories

Report to display negative values only.

Answers