Categories
- All Categories
- Oracle Analytics Learning Hub
- 29 Oracle Analytics Sharing Center
- 18 Oracle Analytics Lounge
- 236 Oracle Analytics News
- 45 Oracle Analytics Videos
- 16K Oracle Analytics Forums
- 6.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 88 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Passing multiple values to a presentation variable
I'm trying to pass multiple values to a presentation variable within a filter using the following syntax ("tablename.fieldname" IN (@{pv_PSRig}['@'])) and I've used ("tablename.fieldname" IN (@{pv_PSRig}['@']{'Defaultfield'})). Neither of these worked; I can see the values being passed, but they error out with the following message :
 Error | |
| View Display Error | |
Odbc driver returned an error (SQLExecDirectW). Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <,>: Syntax error [nQSError: 26012] . (HY000) | |
Or this message when I open up the report:
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P
Answers
-
I searched before I posted and I didn't find any solutions that worked. I, also, checked out your links and they don't work either when I plug them into the formula. What I'm trying to do is use the fliter feature in the Edit Formula area and I would like to pass through multiple values through a presentation variable, but I'm getting multiple errors when I try to do this. I copied and pasted the errors I was getting in my original post, but it didn't come out so well.
This is the condensed version of the formula I'm trying to get right:
IFNULL(filter("Fact - RDays"."PRDays" USING ("Rigs"."Rig Number" IN(@{PSRig}['@']))), 0)
That formula gives me this error when I insert values from the dashboard prompt:
The number of columns returned in retrieving column metadata was invalid (expected: 1; received: 2)
SQL Issued: SELECT 'R0080','R0089' FROM "XXHP DW - GLTrans"
If I add a default value to the variable (ex: @{PSRig}['@']{'R0511'}, it gives me the following error message:
Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P:OI2DL65P
State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 27002] Near <,>: Syntax error [nQSError: 26012] . (HY000)
SQL Issued: {call NQSGetQueryColumnInfo('SET VARIABLE DISABLE_CACHE_HIT=1; SELECT (''R0072'',''R0073'') FROM "XXHP DW -GL Trans"')
Any version of the variable syntax within my formula is not giving me the option to display multiple values.
Any help would be much appreciated. Thanks!
0 -
Below works perfectly fine in OBIEE 12c environment:
FILTER("Revenue Metrics"."Revenue" USING ("Products"."Product" IN (@{Product_PV}['@'])))
FILTER("Revenue Metrics"."Revenue" USING ("Products"."Product" IN (@{Product_PV}['@']{'7 Megapixel Digital Camera'})))
The only difference is first formula will always display error at Report Level until Presentation Variable is assigned with a value through dashboard prompt and second will filter data on default value at report level and will change output on dashboard upon values selected in dashboard prompt
And result gets filtered based on Dashboard Prompt even when more than one value is selected in prompt
0 -
So from the looks of it, my syntax is correct and should be working; we're on Oracle Business Intelligence 11.1.1.7.160119 and we're in the process of upgrading to 12C; are you saying that it only works on that version and something may be wrong with the version I'm on? Let me know! Thanks!
0 -
Will test it on 11.1.1.7.160119 and let you know.
Meanwhile checkout below KM document which explains on how to make use of Presentation Variable in Column formula based on Column Type
OBIEE 11g: Presentation Variable With Multi Value Selected Gives 'No Results' and Wrong Query Generated. (Doc ID 1418868.1)
0 -
Ok great and thanks for testing on my version.
I checked out the KM document and I'm using the syntax documented in the article and I'm getting this error message:
The number of columns returned in retrieving column metadata was invalid (expected: 1; received: 2)
Not sure why it's behaving differently than any other other column prompt...
0 -
I did exact test as mentioned before on OBIEE 11.1.1.7.170418(latest OBIEE 11.1.1.7.x bundle patch) and even there did not face any issue,may be you are trying something more then just passing multiple value through a presentation variable to a column on a single dashboard with a single report, because whatever testing I did for you has been pretty straight forward, you can refer to my previous post.
But depending on your latest mentioned error message "The number of columns returned in retrieving column metadata was invalid (expected: 1; received: 2)" it looks like you are hitting the issue described in below KM document.
So review below KM document described scenario and verify if it is similar to your issue and don't go by document saying that issue seems to be fixed in 11.1.1.7.150120 and above because IT HAS NOT BEEN FIXED proof is BUG 12756414 which clearly states that issue has been fixed in 12.2.1.0.0 and also even when document says issue has been fixed in 11.1.1.7.150120 and above still Patch is available for same version that means you need to request for One off Patch 12756414 from Oracle to fix the error message mentioned by you in your last post.
OBIEE 11g - Dashboard Prompt Scope Issue when Presentation Variable is Used (Doc ID 2025301.1)
0 -
Ok will do; thank you! That is the exact error I'm getting and also, this one.
OBIEE 11G: Error: "[nQSError: 27005] Unresolved column: saw_0 (HY000)" When Populating a Variable Prompt with a SQL Statement (Doc ID 1345964.1)
It says it's been fixed in a version below the one I have, but obviously, it hasn't.
Thanks for all your help! I truly appreciate it!
0