Oracle Communities

Oracle Analytics and AI

Child Item

how to remove one specific row from data using a dashboard prompt?

for some reason I am struggling to create a way to filter one specific row in my data. My data has about 100 lines but one is skewing everything else. I would like the user to be able to filter this one out from a dashboard prompt.

I create a session variable, radio button, custom values 'Yes' and 'No', with default selection on 'No'.

I was then going to do a CASE WHEN on the row identifier and simply rename this identifier if "Yes" is selected. But the problem is that my session variable is not accepted by OBIEE, I get a error:

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27045] Nonexistent column: "Yes".

Please have your System Administrator look at the log for more details on this error. (HY000)

From a dashboard prompt, how do I create a 'Yes' and 'No' variable that I can then use in the analysis?

Thanks,

Yann

Find more posts tagged with

Accepted answers

All comments

[Deleted User]

The error message points to you using "Yes" as a column reference rather than as a value.

What are you doing exactly in technical terms? How are you filtering exactly, on what and how and what are yoy putting into the variable?

Barticchia

Do you have everytime the same data?

In that case, more easily, you could use it down on the filter panel.

I used this kind of thing for WriteBack method (necessary condition to insert a new row into DB)

2783793

remove row.PNG

Sorry I wasn't probably clear. I have a database of customer opportunities, all have an ID, a customer name and revenue. The database is small (200 rows) but will increase a lot. One row (a deal much bigger than everything else) tends to hide everything (skewing the data) and I would like to have the option to ignore ID 14 with a simple yes/no prompt on the dashboard prompt.

My first thought was to simply use a case when in the analysis and when "yes" is selected I would change the ID 14 to something to an ID that doesn't exist in the database so it is ignored. but this doesn't seem like a good method to me.

What is the best way to achieve this?

Thomas Dodds

Use a prompt to let the user set a range for acceptable Revenues ...

2783793

It's a good idea but I was hoping to target this specific row. But yes it's a good workaround.

Robert Angel

Or use "bins" to group revenue ranges into A, B , C , D, E (or even yes / no) groups and that put your dashboard prompt group filter on that

Robert Angel

Taking the bins thought further you could even divide the revenue by total revenue and have a smaller than 80% filter or selector, that would remove the ice berg from a graph with ice cubes, and would work forever....

Thomas Dodds

Targeting a specific row is counter-productive to analysis ... if this is what the requirements are then OBIEE is a very expensive excel option. Go back to excel and hide the row or delete it.r

Analytically what you have is an outlier ... so you need to statistically account for it (and others like it) or allow the user to weed it out on demand.ula

Statistically you could write a filter formula to evaluate it's proximity to the average and filter it (and it's friends) out that way.

Jerry Casey

To combine your original idea with Robert's, use a variable prompt with radio buttons for Yes and No that sets a presentation variable, say "varExclude".

in your query filter, include lines like this:

'Yes'='@{varExclude}{No}' AND [Filter that excludes outliers]

'No'='@{varExclude}{No}' AND ID NOT NULL (or whatever)

Robert Angel

Agreed, data fudges are just nasty, but being able to segment your revenue into homogeneous groups - not a bad idea.