none
Passing XML into a SSRS report from c#

    Question

  • I have a SSRS report that queries an XML file. I'm calling this report from c# and redering it as a PDF. The only way I can do this is to save the XMl into a path that is already defined in the report. My question is, can I send the XML into the report so that the report uses the parameterised report as the datsource?
    Friday, August 20, 2010 5:08 PM

All replies

  • Hi TheGreatJC,

    If I understand correctly, you are using XML data source in a server report(a report with extension .rdl, and has been deployed to a Report Server). Now, you want to know, if it is possible to pass the XML in the report using C# code.  In other words, using a dynamic XML file instead of using static XML file path. If I have misunderstand, please don't hesitate to let me know.

    As you may know, we can use the embedded XML document as XML data source, and we can use parameters in the query statement. So, we can store the XML document in a parameter, and then pass it in the query statement. In this case, we can then set th parameter's value through SetParameters method of the Report Vieer control.

    Below are the detailed steps:

    1. Create a XML data source
    2. Create a parameter(ReportParameter1 in this sample)
    3. Create a Dataset with the following statement:
       ="<Query><XmlData>" & Parameters!ReportParameter1.Value & "</XmlData></Query>"
    4. Set the default value of the parameter. Sample as below:
       ="<Customers><Customer ID=""1"">Bobby</Customer></Customers>"
    5. Then, we can change the value this parameter via SetParameters method

    After that, we can pass XML into a SSRS report without save the XML file to a  specified path.

    For more information, please see:
    XML Query Syntax for Specifying XML Report Data: http://msdn.microsoft.com/en-us/library/ms345251.aspx
    ServerReport.SetParameters Method: http://msdn.microsoft.com/en-us/library/ms252178(VS.80).aspx

    If you have any more questions, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, August 23, 2010 6:16 AM
  • Hi Jin,

    You are correct with your understanding. Your post has helped me, I'm nearly there but I'm trying to create the right dataset/query.

    My XML (soted as a static XML file) is as follows:

    <CUSTOMER>
    <DETAILS>
     <CUSTOMERNAME />
     <CUSTOMERTYPE />
     <ACTION>Update</ACTION>
     <UPDATEDATE>2010-08-25</UPDATEDATE>
    </DETAILS> 
    </CUSTOMER>

    and my query is

    <Query>
      <ElementPath>
        CUSTOMER {}/es:DETAILS
      </ElementPath>
    </Query>

    This provides me with fields I need from the <DETAILS> node. How do I specify the dataset with the parameter ReportParameter1 - I have defaulted this to be the entire XML string.

    Thanks,

    John

     

    Wednesday, August 25, 2010 4:42 PM
  • Hi John,

    For your issue, we can follow these steps:
    1. Set the datasource connection of the XML data source to be empty(means a XML datasource with blank connection string).
    2. Set the question to be:
            ="<Query>" & Parameters!ReportParameter1.Value & " <ElementPath>CUSTOMER {}/es:DETAILS</ElementPath></Query>"
    3.Create a parameter named ReportParameter1
    4. Set the default value for this parameter as I mentioned in my first reply.
    5. Use SetParameters to pass in value.

    If there is anything unclear, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Thursday, August 26, 2010 1:59 AM
  • Hi Jin,

    I'm trying to do as you've suggested, I have the datasource set up etc. I'm a little unclear as to where you're adding the ="<Query>" & Parameters!ReportParameter1.Value & " <ElementPath>CUSTOMER {}/es:DETAILS</ElementPath></Query>" piece. I can't set it in the query textbox from the dataset properties, nor can I add it into the XML source.

     

    Thanks,

    John

    Thursday, August 26, 2010 4:27 PM
  • Hi John,

    We need to add the expression in the dataset definition.

    The expression means ="<Query>" & Parameters!ReportParameter1.Value & " <ElementPath>CUSTOMER {}/es:DETAILS</ElementPath></Query>"

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Friday, August 27, 2010 1:49 AM
  • Hi Jin,

    We're getting places now, but I have a problem on the data source if I levae it empty, I get "

    • An error occurred during client rendering.
      • An error has occurred during report processing. (rsProcessingAborted)
        • The data source 'DataSource1' cannot be found. (rsDataSourceNotFound)

    "

    message when its rendered. I understand you can leave the connection string empty as you've suggested.

     

    John

    Friday, August 27, 2010 1:03 PM
  • Hi John,

    Has the 'DataSource1' been uploaded(deployed) to the Report Server? If not, please upload it. If yes, please delete it, and then re-deploy the datasource again.

    Yes. I am sure, we can leave it blank.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, August 30, 2010 9:56 AM
  • Hi Jin,

    The datasource in this case is part of the report, its not shared datasource. So when the report is deployed, it will be part of the rdl. I cannot convert it to a shared datasource, so I've created a new shared datasource and deployed that separately. I've changed my dataset to be based on that new Data Source, but I'm getting

    An error occurred during client rendering.
    An error has occurred during report processing.
    Query execution failed for dataset 'DataSet1'.
    Failed to prepare web request for the specified URL.
    Invalid URI: The URI is empty.

    The Dataset has the expression:

    =

    "<Query>" & Parameters!ReportParameter1.Value & " <ElementPath>CUSTOMER {}/es:DETAILS</ElementPath></Query>"

    And ReportParameter1 has a default value of:

     

     

    <CUSTOMER><DETAILS><ACTION>Update</ACTION><UPDATEDATE>2010-09-20</UPDATEDATE></DETAILS></CUSTOMER>

    Thanks, John

    p.s. I can get this working my saving the xml onto a folder and changing the embedded data source to point at this file using the parameter, so If I can't get your way to work, I'll fall back on this.

     

    Monday, August 30, 2010 4:42 PM
  • Hello Jin,

    I am trying to use this to solve a similar problem, but I cannot seem to get the Query String in my dataset to work.  I keep getting the following error:

    An error occurred while setting the Command Text property of the data extension command. 
    The XmlDP query is invalid. Syntax error at line 1, character 21 of the ElementPath.

    Dataset query:

    ="<Query><XmlData>" & Parameters!ReportParameter1.Value & "</XmlData></Query>"

    Parameter default value:

    <Customers><Customer ID=""1"">Bobby</Customer></Customers>

    Any ideas?

    Thanks!

    Becky

    Thursday, October 14, 2010 6:11 PM
  • Hi Becky,

    The parameter value must include the =, the first ", and the last ".

    Here are some scree shots for this solution, these may help you to understand the solution:
    Create shared data source:

    Create the data source in the report based on the shared data source(of course, we can use embedded data source directly. If using embedded data source, please ignore step1):

    Create parameter:

    Assign value for the parameter:

    Now, create the dataset(NOTE: we need to create the Fields manually, as the Reporting Services can't generate the fields automatically when we use dynamic data source):

    Final, preview the report:

    If there is still anything unclear, please feel free to ask.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Friday, October 15, 2010 2:13 AM
  • I have followed your instructions exactly and am still getting the error I mentioned above:

    An error occurred while setting the Command Text property of the data extension command. 
    The XmlDP query is invalid. Syntax error at line 1, character 21 of the ElementPath.

    ------------------------------
    ADDITIONAL INFORMATION:

    The XmlDP query is invalid. Syntax error at line 1, character 21 of the ElementPath. (Microsoft.ReportingServices.DataExtensions)

    It appears the "error" is occuring where the ampersand (&) is located to concatanate the parameter string with the query string.  Does this work in Sql Reporting Services 2005?  Even if I set my parameter to be the whole string, and set that parameter as teh value of the "query string" for the dataset, it does nto work,...

    Monday, October 18, 2010 4:02 PM
  • Becky,

    I am having the same exact issue.  Were you able to resolve this?  If so, how?

    Thank You,

    Candace

    Thursday, November 18, 2010 7:44 PM
  • Hi,

    Could you please post the query you used?

    From the error message "The XmlDP query is invalid. Syntax error at line 1, character 21 of the ElementPath", it seems you used ElementPath in the query, and there may have errors in the ElementPath.

    If possible, could you please send me the RDL? This will help to troubleshoot the issue too. You can send the file to sqltnsp AT microsoft dot com. Please send the mail with thread URL too.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Wednesday, November 24, 2010 2:00 AM
  • I get the same error, "The XmlDP query is invalid. Syntax error at line 1, character 21 of the ElementPath". I've sent my RDL and included my query below:

    ="<Query><XmlData>" & Parameters!Report_Parameter_1.Value & "</XmlData></Query>"

    Tuesday, November 30, 2010 6:33 PM
  • Hi Chunk,

    Thank you for your mail and detailed description.

    According to your description, I can reproduce the issue in Business Intelligence Development Studio(BIDS) 2005. This seems to be a limitation in BIDS 2005.
    However, based on my testing, we can ignore the error, and preview the report, the data is presented in the report correctly. Just note, we need to add the Dataset Field manually.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Wednesday, December 01, 2010 1:45 AM
  • Thank you Jin. This works great. Are multiple namespaces supported for the XML parameter?
    Friday, December 03, 2010 8:00 PM
  • Hi,

    As far as I know, multiple namespaces is not supported.

    Thanks,
    Jin Chen


    Jin Chen - MSFT
    Monday, December 06, 2010 6:26 AM