Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hoping someone can help me with this...
I've read the "Deep Dive into Query Parameters and Power BI Templates" article about the use of parameters in Power BI Desktop . Further to this, I've been trying - unsuccessfully - to make use of a parameter in a simple test SQL query that accesses an Oracle database.
Let's say I have a simple query:
SELECT col1, col2
FROM mytable
WHERE col3 = 'mytestvalue'
I then create a parameter called "myparam", which I'd like to use instead of the literal value:
SELECT col1, col2
FROM mytable
WHERE col3 = &myparam
I've tried various ways of expressing the parameter, e.g. &myparam& / :myparam / "&myparam&" but none have worked - I just get different Oracle errors raised, e.g. ORA-904: invalid identifier, ORA-1008 not all variables bound.
How should I express the parameter to get this to work?
Solved! Go to Solution.
Hi ankitpatira
Thanks for your response.
Unfortunately, the examples given in these articles don't seem to address the specific scenario that I'm encountering when trying to use the parameter as a filter when accessing data from an Oracle database.
I know, it is too late; just to close the loop and share solution for others who are looking for working solution.
Step#1: Create Parameter pID [text parameter and keep default value]
Step#2: On Dataset that is pulled on Query Editor, right click and go to Advanced Editor. Edit below code as per your database and table name.
let
pID=ID,
Source =
Oracle.Database("your database name",
[HierarchicalNavigation=true,
Query="SELECT * FROM MYtableName#(lf) where DA_LOAN_ID='"&pID&"'"])
in
Source
Thanks Kailas684, this sort of worked for me. My requirement is to pass a list to the sql query from the parameter list.
Insead of this where DA_LOAN_ID='"&pID&"'
I want something that is where DA_LOAD_ID IN ('"&ParameterList&"')
User | Count |
---|---|
93 | |
83 | |
77 | |
74 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |