I have read several other post and even blogs on using parameters and variables inside of an SQL statements and have been playing for a couple of days now trying to get it to work. After several interations, I finally got a "successful" execution; however, it did not bring back any data as the variable did not match the data. The only way I could get it pass both syntax and Oracle submission was to make the value a text field; however, I am needed to do DateTime values within my SQL.
What I would like is have the "FromDate" and "ToDate" that can be used on the where .... between clause in the Oracle to_date statement to supply the dates. I would also like to be able to do this with the ability for the users to supply the parameters via the Power BI Online Service or parameters passed via the report URL lauch.
Please note that I have used parameters and a template to do filter and that works great; however, it does not work with Online Services and is not support with DirectQuery. I have also used parameters feed from an excel spreadsheet and that works great; howevever, you can not use DirectQuery this way either.
Please find the direct query below and I would like to replace the first field of the to_date sections of the code with the variables/parameters and use witih DirectQuery and Online Services. Any help would get greatly appreciated.
let
Source = Oracle.Database("proderp.colo", [HierarchicalNavigation=true, Query="select EDIPRHEADER.creation_date as ""TRANS DATE""#(lf),(select account_name from apps.hz_cust_accounts_all EDIHCA where EDIHCA.attribute4=EDIPRHEADER.TRADINGPARTNERID) as ""ACCOUNT""#(lf),EDIPRHEADER.PURCHASEORDERNUMBER as ""CPO NUM""#(lf),EDIPRHEADER.CONTRACTNUMBER as ""ORDER RELEASE""#(lf),EDIPRHEADER.DEPARTMENT as ""DEPT""#(lf),EDIPRHEADER.PROMOTIONDEALNUMBER as ""PROMO DEAL""#(lf),(select sum(orderqty) from apps.XXSP_PO_IN_LINE EDIPOL where EDIPRHEADER.ORIG_SYS_DOCUMENT_REF = EDIPOL.ORIG_SYS_DOCUMENT_REF) as ""TOTAL UNITS""#(lf),EDIPRHEADER.creation_date as ""ISA DATE""#(lf),EDIPRHEADER.creation_date as ""ISA TIME""#(lf)from apps.XXSP_PO_IN_HEADER EDIPRHEADER#(lf)where EDIPRHEADER.creation_date between to_date('07/20/2017 00:00:00', 'mm/dd/yyyy hh24:mi:ss') and to_date('07/20/2017 11:00:00', 'mm/dd/yyyy hh24:mi:ss')#(lf)order by EDIPRHEADER.creation_date desc"])
in
Source
