Categories
- All Categories
- 137 Oracle Analytics News
- 24 Oracle Analytics Videos
- 14.6K Oracle Analytics Forums
- 5.6K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 52 Oracle Analytics Trainings
- 9 Oracle Analytics Data Visualizations Challenge
- 4 Oracle Analytics Career
- 2 Oracle Analytics Industry
- Find Partners
- For Partners
Dynamic topN
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
Answers
-
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
0 -
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
)
0 -
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?
0 -
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.
0 -
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)
0 -
What are the errors that you are getting?
Sent from my iPhone
0 -
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.
Please have your System Administrator look at the log for more details on this error.
0 -
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
0 -
Almost there! Try '@{PV_DYNAMICSELECT}' and remove the {} as you're not setting a default value.
0 -
That worked. Thanks so much for your help!!
0
