Forum Discussion
Paginated Report - Measure and Recordset From Query consideration
Hi FrancoCNR,
First, Paginated Reports cannot retrieve data from an Oracle stored procedure that returns a SYS_REFCURSOR through an OUT parameter. Although the parameter can be defined in the dataset, the report engine cannot bind an OUT cursor to a result set when using OLE DB or ODP.NET. The recommended and supported approach is to convert the stored procedure into an Oracle function that returns a SYS_REFCURSOR and then call that function using a Select statement. This avoids the use of OUT parameters and does not require Oracle object types or pipelined functions.
Second, DAX measures cannot be used to filter rows or groups in Paginated Reports. While a measure can be displayed in the report, it cannot be applied as a group or dataset filter. To limit the data, the filtering logic must be moved into the dataset query itself using SQL WHERE or HAVING clauses. As an alternative, a dataset filter can be used, but this is only recommended for small datasets since filtering occurs after the data has been retrieved.
These behaviors are by design and apply to Paginated Reports both in Power BI Service and SSRS.
Thanks,
Prashanth Are
MS Fabric community support
Ok. Thanks for all.
Last dubt:
I understand that object type in oracle is not requested.
Then in the text edit of Report Builder i can use something like this:
Select PKG_POWERBI.MyFunc(:DataRif) as RESULT FROM DUAL or
Select PKG_POWERBI.MyFunc(:DataRif) FROM DUAL or other ?
Thanks again.Hi FrancoCNR ,
No, you cannot use SELECT PKG_POWERBI.MyFunc(:DataRif) FROM DUAL.
A function returning SYS_REFCURSOR does not return a scalar value and cannot be selected from DUALThe supported approach is to expose the cursor as a table source and query it using
SELECT *FROM TABLE(PKG_POWERBI.MyFunc(:DataRif))Thakns,
Prashanth
