Forum Discussion

Ashok_Gopeani's avatar
Ashok_Gopeani
Icon for Helper I rankHelper I
1 year ago
Solved

Syntax required for referring paginated report embedded url parameters in direct query where clause

Hi Team,

 

I am trying to build on paginated report with the data source  Oracle and trying to access through embedded rul like below

https://app.powerbi.com/xxxxx4b40-81a7-7dc0599e8645&experience=power-bi?rp:Cm_Run_ID=1695544

here Cm_Run_ID is parameter in paginated report. and i am trying to use this parameter as query param in my direct query as below SELECT * FROM table name WHERE cm_run_id = :Cm_Run_ID;

but this is not working for me, so could you please let me know who to refer url report parameters as query parameters.

thank you in advance.

 

 

 

Need Help
  • Ashok_Gopeani's avatar
    Ashok_Gopeani
    1 year ago

    this got resolved with referring report parameters in actual report url but not embedded url of the report.

9 Replies

  • Akash_Varuna's avatar
    Akash_Varuna
    Icon for Super User rankSuper User
    1 year ago

    Ashok_Gopeani Ensure the URL parameters are correctly formatted (e.g., ?rp:Cm_Run_ID=1695544) and that the report parameter matches the Oracle column data type (e.g., Integer). Link the report parameter to the query parameter correctly, and test the functionality.

  • Ashok_Gopeani's avatar
    Ashok_Gopeani
    Icon for Helper I rankHelper I
    1 year ago

    Akash_Varuna :

    i think i am associating URL parameters with query parameters correctly..

    https://app.powerbi.com/xxxxx4b40-81a7-7dc0599e8645&experience=power-bi?rp:Cm_Run_ID=1695544

    SELECT * FROM table name WHERE cm_run_id = :Cm_Run_ID;

    Parameter setting on dataset

     

    please verify above informaiton and let me know where i done the mistake...

    one thing i have observed here is whatever url param value i have set that is not passing to query parameter in the direct query... 

    please let me know if i need to do it differently

    thank you

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Ashok_Gopeani,

    Thanks for reaching out to the Microsoft fabric community forum.

    From what I can see, the parameter setup in your report and dataset looks correct. You’re using ":Cm_Run_ID" in your Oracle query, the parameter type is set to Integer, and it’s properly mapped in the dataset properties.

    The main issue appears to be with the URL format you're using to pass the parameter. In your current URL "https://app.powerbi.com/xxxxx4b40-81a7-7dc0599e8645&experience=power-bi?rp:Cm_Run_ID=1695544", there are two question marks (?), which can cause the query string to be misinterpreted and prevent the parameter from being passed correctly.

    Just make sure there's only one '?' to begin the query string, and use '&' to add additional parameters.

     

    I would also take a moment to thank Akash_Varuna, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

     

    If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

    Best Regards,
    Hammad.
    Community Support Team

     

    If this post helps then please mark it as a solution, so that other members find it more quickly.

    Thank you.

    • Ashok_Gopeani's avatar
      Ashok_Gopeani
      Icon for Helper I rankHelper I
      1 year ago

      Hi Anonymous : i think i have used only one question mark(?) in the url and i have verified one more time, still i am getting same issue.

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Ashok_Gopeani,

        If the URL is correctly using only one '?', and you're still seeing that the parameter isn't getting passed to the query, a few additional things might be worth checking:

        * Ensure the report parameter is named exactly Cm_Run_ID (case-sensitive) and matches what's used in     the URL (rp:Cm_Run_ID=1695544).

        * ALso try to open the report without any parameter first to ensure it's not caching an older state or

           running with defaults.

        * Try changing the parameter’s data type from Integer to Text just to see if the value comes through.

           Sometimes, even if types appear to match, implicit conversion issues can block the value.

        Even after all the mentioned steps you are still unable to solve the issue you can reach out to Microsoft Support by raising a ticket with Microsoft Support.

        Please refer below link on how to raise a contact support or support ticket.
        How to create a Fabric and Power BI Support ticket - Power BI | Microsoft Learn

         

        Best Regards,
        Hammad.
        Community Support Team.