locked
Questions about passing data to SSRS RRS feed

  • Question

  • Hello,

    I am creating a report via Reporting services and want to ask these questions:

    1. Is there a way to pass and parse JSON data to an RDL file? Currently we are using SQL Server 2014 Reporting Services.
    2. Can I open and read XML files via custom code in the Report? (Not through datasource)
    3. Assuming I have XML data as datasource in Reporting Services with below sample format. Is there a way that Reporting Service will be able to iterate through <MainHolder> and <item> nodes?
    <PersonalInfo>
     <MainHolder>
      <item>...</item>
      <item>...</item>
      <item>...</item>
     </MainHolder>
     <MainHolder>
      <item>...</item>
     </MainHolder>
    </PersonalInfo>
    Any info is appreciated. Thanks a much!

    Friday, October 14, 2016 8:02 AM

Answers

  • Hi Adrian,

    1.Is there a way to pass and parse JSON data to RDL file?

    SQL Server Reporting Service doesn’t support the JSON format for processing in the current version, instead you can use WebAPI XML output. About the details you can refer to following case.

    2.Can I open and read XML files via custom code in the Report?(Not through Data Source)

    You’d better use the XML type as Data Source, since the Data Source is XML files. And a workaround that is you can shred the xml using XPath, shred it in the dataset query, or just display it raw like any other string.

    3.Is there a way that Reporting Service will be able to iterate through <MainHolder> and <item>nodes?

    Based on my research, you can try to write a custom assembly and deploy to the report server, then call the function in the  expression to iterate the nodes <MainHolder>  and <item>.

    Hope it helps.

    Best Regards,
    Pirlo Zhang
    • Marked as answer by Adrian Lauron Monday, October 17, 2016 4:17 PM
    Monday, October 17, 2016 9:38 AM

All replies

  • Open your RDL file in Visual studio, right click on RDL file and view Code.

    Thanks

    Friday, October 14, 2016 8:37 AM
  • This seems to be too general. Can you give me more details about this "right click on RDL file and view Code?"

    Thanks.

    Monday, October 17, 2016 2:28 AM
  • Hi Adrian,

    1.Is there a way to pass and parse JSON data to RDL file?

    SQL Server Reporting Service doesn’t support the JSON format for processing in the current version, instead you can use WebAPI XML output. About the details you can refer to following case.

    2.Can I open and read XML files via custom code in the Report?(Not through Data Source)

    You’d better use the XML type as Data Source, since the Data Source is XML files. And a workaround that is you can shred the xml using XPath, shred it in the dataset query, or just display it raw like any other string.

    3.Is there a way that Reporting Service will be able to iterate through <MainHolder> and <item>nodes?

    Based on my research, you can try to write a custom assembly and deploy to the report server, then call the function in the  expression to iterate the nodes <MainHolder>  and <item>.

    Hope it helps.

    Best Regards,
    Pirlo Zhang
    • Marked as answer by Adrian Lauron Monday, October 17, 2016 4:17 PM
    Monday, October 17, 2016 9:38 AM
  • No JSON support in sql server 2014

    Go through these links for XML querying  which will help you query a incoming xml message/doc in the report and display it

    https://msdn.microsoft.com/en-CA/library/ms345251.aspx

    https://msdn.microsoft.com/en-CA/library/ms365158.aspx

    https://technet.microsoft.com/en-us/library/ms345251(v=sql.110).aspx

    https://www.mssqltips.com/sqlservertip/3148/sql-server-reporting-services-xml-data-source-and-data-set/




    • Edited by MLTC Monday, October 17, 2016 3:26 PM
    • Proposed as answer by MLTC Monday, October 17, 2016 3:26 PM
    Monday, October 17, 2016 3:26 PM