Oracle Business Intelligence

Products Banner

Display Measure values when contain negative value

Received Response
163
Views
12
Comments

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

  • Joel
    Joel ✭✭✭✭✭

    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.

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi All,

    I have the following scenario of measure column data.

    pastedImage_0.png

    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.

  • Joel
    Joel ✭✭✭✭✭

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

  • Joel
    Joel ✭✭✭✭✭

    Well spotted @Gianni Ceresa! Duplicating questions doesn't double the chance of your issue being resolved, it has the reverse effect...

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Not the same questions, but both are asked by me only.

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    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.

  • Thomas Dodds
    Thomas Dodds ✭✭✭✭✭

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

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    Hi All,

    I am attaching screen shot with actual scenario.

    pastedImage_0.png

    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

  • 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

  • User_JL3CW
    User_JL3CW ✭✭✭✭✭

    HI all,

    In rows i have the following columns.

    pastedImage_0.png

    I am unable to understand how to have a FLAG at row level to check if there is a single negative value for any period then display the whole row. Please suggest.

    Regards,

    Chandra.

  • Joel
    Joel ✭✭✭✭✭

    @Gianni Ceresa has already suggested that you use the SUM of the Absolute of the measure to compare against the SUM of the measure by whatever you're aggregating in your table. If the SUM of your measure is different from the SUM of Absolute components then you know the row has at least a negative record. This can be used to set flag at row level of your table.