nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Dynamic topN

Susan Z.

Hi,

I am new to BICS and have a question about a dashboard I am creating.

I have 3 columns: Supplier, number of POs, Amount spent.

I want to design my dashboard so that through a prompt (or some other way?) the user selects what he wants to rank the suppliers by.

so he has to options: Amount or Number.

If he selects the "amount", then he will see the top 10 suppliers by amount spent. If he selects "number", he will see top 10 suppliers by number of POs.

How can I make it so that the end user chooses the parameter that ranks the supplier?

Thanks,

Susan

Find more posts tagged with

Accepted answers

All comments

Joel Acha

You can use your Dashboard Prompt to populate a presentation variable factColumn for example with values A and B as an example. The value of the presentation variable can then be used in a column formula in your report to determine which measure to use based on selected presentation variable value.

The column formula would look something like this:

CASE WHEN @{factColumn}{}= 'A' THEN factcolumnA

WHEN @{factColumn}{}= 'B' THEN factcolumnB

END

Uma Mahesh

As Joel Acha Said Create an DashBoard Prompt(Variable Prompt). which gives you presentation variable like: Amount; Number...

in report insert this formula.

TOPN("Suppliers Balance column",10 BY

CASE

WHEN @{PV_DYNAMICSELECT}{} = 'AMOUNT' THEN "Amount Column"

WHEN @{PV_DYNAMICSELECT}{} = 'NUMBER' THEN "Number Column"

END

)

Susan Z.

Thank you both Joel and Uma.

I get the idea of the case statement

When I try to incorporate that into the column formula in my Analysis though I get an error.

That is where I am supposed to insert the formula. Right?

In the column formula of the Analysis which is included in the Dashboard?

Uma Mahesh

Yes,

If you are using pivot table put suppliers column in dimension side and amount column that contains case condition in measure side.

u'll get what u want.

asim cholas

if both of the columns are from same presentatiion table , simply create the columns as the same in presentation variable or vice versa and apply formula like below

topn(table.@{PV},10)

Joel Acha

What are the errors that you are getting?

Sent from my iPhone

Susan Z.

I get some sort of syntax error

Formula syntax is invalid.

[nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <=>: Syntax error [nQSError: 26012] . Please have your System Administrator look at the log for more details on this error. (HY000)

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

Susan Z.

I did these:

1) Created a presentation PV_DYNAMICSELECT and selected radio buttons values to be : AMOUNT and VOLUME

2) I created a column in the criteria tab with the formula:

CASE

WHEN @{PV_DYNAMICSELECT}{} = 'AMOUNT' THEN "Purchase Orders"."Total Amount"

WHEN @{PV_DYNAMICSELECT}{} = 'VOLUME' THEN COUNT(DISTINCT "By Purchase Order Detail"."Full PO Number")

END

3) It does not let me save it because of the above syntax error. What am I doing wrong?

Thanks for your help

Joel Acha

Almost there! Try '@{PV_DYNAMICSELECT}' and remove the {} as you're not setting a default value.

Susan Z.

That worked. Thanks so much for your help!!