Categories
- All Categories
- 101 Oracle Analytics News
- 9 Oracle Analytics Videos
- 14.3K Oracle Analytics Forums
- 5.3K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 56 Oracle Analytics Trainings
- 60 Oracle Analytics Data Visualizations Gallery
- 4 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Display Measure values when contain negative value

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 "Contain Negative".
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 "Contain Negative" (if my measure values contain at least one -ve value) then my report should display measure column values (both +ve and -ve values), here if all the +ve all there then it should not show anything.
Regards,
Chandra.
Answers
-
Your dashboard prompt needs to populate a presentation variable based on the selection. If user select -ve then you need to have a CASe statement in your measure that checks the presentation variable and then only return measure values < 0 and the ELSE condition should return all values.
0 -
Hi All,
I have the following scenario of measure column data.
When i select ALL in the prompt it has to show all the data (4 rows) and when i select Negative then it has to show 1 row and 3 rd row of data.
I have written the following filter at report level
"-Analysis"."Balance - units" < case when '@{PV_Negative}{ALL}' ='ALL' then 99999999999 else 0 end
The above formula displaying correct data for ALL condition
for second condition ("-Analysis"."Balance - units" < 0) it is displaying -ve values from 1st and 3rd rows.
But i need to show +ve values also from 1st and 3rd rows.
Regards,
Chandra.
0 -
Try this: CASE WHEN '@{PV_Negative}{ALL}' = 'ALL' THEN "-Analysis"."Balance - units" WHEN '@{PV_Negative}{ALL}' <> 'ALL' THEN CASE WHEN "-Analysis"."Balance - units" < 0 THEN "-Analysis"."Balance - units" END END
please note that I haven't tested this logic but something like this should do the trick...
0 -
Isn't this the same/strictly related to Report to display negative values only. ?
0 -
Well spotted @Gianni Ceresa! Duplicating questions doesn't double the chance of your issue being resolved, it has the reverse effect...
0 -
Not the same questions, but both are asked by me only.
0 -
Hi all,
The above script is not displaying +ve values for the second condition. It's displaying -ve values from 1st and 3rd row.
Regards,
Chandra.
0 -
You're data doesn't support what you are trying to do ... you need a flag on the row that is there is any -ve in the row to mark it 'Y' then filter for that ...
0 -
Hi All,
I am attaching screen shot with actual scenario.
In above report result the measure column "-units" displayed for monthly data.
For the formula written:
CASE WHEN '@{PV_Negative}{ALL}' = 'ALL' THEN "Analysis"."- units" WHEN '@{PV_Negative}{ALL}' <> 'ALL' THEN CASE WHEN "Analysis"."- units" < 0
THEN "Analysis"."- units" END END
The above formula is working fine for ALL condition from db prompt.
It's displaying all the four rows as shown above.
If i select "Negative" from DB prompt, It should display 3rd row and 4th row (all values -ve and +ve values)
but it's displaying only -ve values from 3rd and 4th row.
Regards,
Chandra
0 -
Thomas gave you the solution: a flag in your data ...
Because what you are asking is something like: if there is a single negative value for any period then display the whole row, with all the periods including positive and negatives ...
I would say you can still make it, but the conditions will be a bit more weird.
What else do you have in rows in your pivot? Because "- Units" doesn't repeat itself just for fun. So what you could try is a condition where "- Units" aggregated by that thing <> ABS("- Units") aggregated by that thing.
PS: don't even try to just copy/paste these bits and hope it works, this gives you a direction, now follow it completing the missing bits and applying some elementary logic to make things valid in a "WHERE" condition of a query
0