Paginated Report - Measure and Recordset From Query consideration

[FrancoCNR]

Hallo, i'me newbie in Power BI and i've 2 questions:
I've PRO License.
1. Is possibile with a Paginated Report (.rdl) obtain data from Oracle  Store Procedure that return SYS_REFCURSOR passing one parameter? I've used OLEDB Driver and is possibile insert parameters but i can't able to pass an OUT parameter.
I would avoid to create TYPED Data in Oracle (many columns are retrurned from Store Procedure) and use a pipelined function.
2. I've created a paginated report with online tool. I need to apply a measure that limits data (i've done this in power bi desktop report) but i can't add measure to filter group but i can add measure to report.
Thanks in advance.

[FrancoCNR]

Thansk.

But in this way , i need to make an OBJECT TYPE in Oracle. Is Right ?
"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."

Thanks in advance.

[v-prasare]

Hi @FrancoCNR,

Converting a stored procedure into a function that returns SYS_REFCURSOR works without OBJECT TYPEs because SYS_REFCURSOR is a predefined Oracle type.
OBJECT TYPEs are only required when using pipelined or table functions, which return collections and are queried using TABLE(). Please refer to below documentation and let me know if this helps resolve your query

Using Ref Cursors To Return Recordsets - ORACLE-BASE

Thanks,

Prashanth

[FrancoCNR]

Ok. It means that my call in RDL DataSet is something like this

{call ? := MyPackage.MyFunction( :param1, :param2 )}

where :param1 and :param2 are IN parameters and 
? = is SYS_REFCURSOR RETURNED BACK.
Thanks again.

[v-prasare]

Hi @FrancoCNR,

We would like to confirm if our community members answer resolves your query or if you need further help. If you still have any questions or need more support, please feel free to let us know. We are happy to help you.

Thank you for your patience and look forward to hearing from you.
Best Regards,
Prashanth Are
MS Fabric community support

[RicardoTraNa]

Hi!! With Paginated Reports you generally cannot pass an OUT SYS_REFCURSOR from an Oracle stored procedure using OLE DB. The report expects a query that directly returns a result set. The usual workaround is to wrap the procedure in a table/pipelined function or view and query it with SELECT * FROM TABLE(...).

In the other hand, measures in paginated reports don’t behave like in Power BI Desktop. You can’t reliably use a measure to filter groups/datasets because filtering happens before measures are evaluated. The best approach is to push the filter logic into the SQL/query or create a calculated field in the dataset.

Hope this helps! 

[v-prasare]

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

[FrancoCNR]

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.

[v-prasare]

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 DUAL

The supported approach is to expose the cursor as a table source and query it using

SELECT *

FROM TABLE(PKG_POWERBI.MyFunc(:DataRif))

Thakns,

Prashanth