none
How to Create a Report in SSRS from Local XML File

    Question

  • Hi

    i need to create a Report from XML file which available in my current folder.

    can anybody suggest How to create a Data source from XML file and How to make a  query for that.

     

    Thanks

    Mahesh

     

    Wednesday, May 30, 2007 5:49 AM

Answers

All replies

  • Are you doing this in an application with ReportViewer in LocalMode?  Or is this going to be a server-side app (where you're just using the XML file in your folder as a test file)?

     

    The way to do it is different, depending on the answer to this first question... but in both cases I will have the followup question:

     

    Is the XML file in a regular, table-like format (like Dataset XML), so that it will be easy to make into a datasource? Or is the schema different altogether?

     

    >L<

    Thursday, May 31, 2007 12:10 AM
  • Hi thanks for ur reply

     

    now we have chenged the way i need to prepare a report

    One web service will return a DataSet

    based on that Dataset i need prepare a report.

     

    Thanks

    Mahesh

    Thursday, May 31, 2007 10:12 AM
  • hi Mahesh,

     

    That change doesn't really change anything about my first question, although it answers the second one<g>. 

     

    The way you are going to do this is still going to be different depending on whether the report is rendered server-side or client-side.  Are you writing a web-application, a win-forms application, or what?  Are you using the reportviewer client control?

     

    If you need to send that dataset as a source to a server-side rendering of the report, you are still going to have to send that information as XML to the server. (But that will be easy because the dataset will turn itself into the "right kind" of XML for this purpose.)  If you need to do this, we will discuss further. 

     

    If you are rendering the report using reportviewer, so you can use local report mode, then you can almost certainly give the localmode report your dataset without any trouble, and without turning it into XML first.  In fact, if you *are* using XML, you load it into a dataset to give to the viewer control <s>.

     

    Hope this helps

     

    >L<

    Thursday, May 31, 2007 2:49 PM
  • Hi Lisa

    Thanks for giving more information

     

    I need to prepare a Server side Report .

    with sql server 2005 we got One VS 2005 IDE in that i have taken Report Server Project Here am creating my reports.

     

    for this they are given me a webservice link that will return a Data set

    based on that i need to prepare a Report.

     

    Previously i know how to prepare a report from Sql server Database.

     

    Thanks.

    Mahesh

     

     

    Friday, June 01, 2007 4:45 AM
  • OK.  If the webservice returns a dataset, then it returns it as an XML message.  You should be fine with this in a server-side report.

     

    Define the datasource as follows:

     

    • The DataSource is of type XML.
    • The connection string for the web service datasource is in fact the URL for the web service!  (http://...)  For testing you can put up a local copy of it (http://localhost/test/my.xml) and assign that as your datasource.
    • The query string, believe it or not, can just be "*". (no quotes) if the XML is a "raw" dataset style.  You can also use the (bleh) element query syntax that they've specified -- see here http://technet.microsoft.com/en-us/library/ms365158.aspx -- If you get stuck, post an example of what the web service response message looks like and I'll try to help. 

     

    To get this to work really easily, they should make sure that the first record has no nulls in it, because that's how the engine is going to figure out what the fields are... using the element query syntax doesn't help this. 

     

    Whether nulls are a problem or not is going to depend on how they are serializing the dataset.  You'll know if it is a problem if the nulls don't show up as empty elements (if the serialized XML completely omits them). 

     

    If they are familiar with the problem already, this may not be an issue.  If it is an issue and they can't figure out how to serialize it properly, they can prepend (union) a single empty row that has all the columns and no nulls, and then you suppress the first row.  It is a kludge, but it works if necessary...

     

    HTH,

     

    >L<

     

     

     

     

    Friday, June 01, 2007 4:23 PM
  • Hi

    Lisa

     

    i was struck with writing query

    i got the webservice responce

     

      <?xml version="1.0" encoding="utf-8" ?>
    - <DataSet xmlns="http://tempuri.org/">
    - <xsTongue Tiedchema id="NewDataSet" xmlns="" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urnTongue Tiedchemas-microsoft-com:xml-msdata">
    - <xs:element name="NewDataSet" msdata:IsDataSet="true" msdata:UseCurrentLocale="true">
    - <xs:complexType>
    - <xs:choice minOccurs="0" maxOccurs="unbounded">
    - <xs:element name="Project">
    - <xs:complexType>
    - <xsTongue Tiedequence>
      <xs:element name="ProjectUID" msdataBig SmileataType="System.Guid, mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" type="xsTongue Tiedtring" minOccurs="0" />
      <xs:element name="ProjectName" type="xsTongue Tiedtring" minOccurs="0" />
      </xsTongue Tiedequence>
      </xs:complexType>
      </xs:element>
      </xs:choice>
      </xs:complexType>
      </xs:element>
      </xsTongue Tiedchema>
    - <diffgrBig Smileiffgram xmlns:msdata="urnTongue Tiedchemas-microsoft-com:xml-msdata" xmlnsBig Smileiffgr="urnTongue Tiedchemas-microsoft-com:xml-diffgram-v1">
    - <NewDataSet xmlns="">
    - <Project diffgr:id="Project1" msdata:rowOrder="0">
      <ProjectUID>8f584354-259b-48fb-a82b-838dd0d75a52</ProjectUID>
      <ProjectName>Reports</ProjectName>
      </Project>
      </NewDataSet>
      </diffgrBig Smileiffgram>
      </DataSet>

     

    In DataSet dialog Box i have taken a query

    first i tried with "*" only it is showing error

    an tried with this

     

    <Query>
        <SoapAction>
            http://tempuri.org/GetAttributes
        </SoapAction>
        <Method Namespace="http://tempuri.org/"
            Name="GetAttributes">
        </Method>
    </Query>

     

    It is also not displaying the values of ProjectUid and ProjectName.

     

    can u help me how to write the query for this type of WebService Responce.

     

    Thanks

    Mahesh

     

    Wednesday, June 06, 2007 10:42 AM
  • Mahesh, I thought they were sending you a dataset -- is the method GetAttributes the way you are supposed to get that dataset or does it represent a query you are trying to do to get the metadata *describing* the dataset?

     

    Either way...  To use the dataset "raw", the thing you are receiving should look like, well, a serialized dataset, like you would get from a dataset if you used its .WriteXML() or .GetXML() methods.  You can't use the "*" (I don't think) unless it looks like that. I can't remember what MS blog I read about this in -- it works, but it is only the simplest way. 

     

    It is not the only way.  Here is a very quick walkthrough that you should find helpful for your scenario: http://blogs.msdn.com/bimusings/archive/2006/03/24/560026.aspx

     

    Here is another walkthrough http://msdn.technetweb3.orcsweb.com/gsnowman/archive/2005/10/12/480321.aspx

     

    And a blog entry that goes through it again http://blogs.msdn.com/bwelcker/archive/2005/11/13/492296.aspx

     

    I think they both show you examples of query syntax.

     

    Here is a tutorial about using XML as a datasource that will help you -- I'm pointing you at lesson 2, where you're learning how the web service is supposed to return the serialized data set  but you probably want to look at all the lessons in this tutorial -- http://technet.microsoft.com/en-us/library/aa337489.aspx

     

    Other references describing other aspects  that I have found helpful.
    http://technet.microsoft.com/en-us/library/aa964129.aspx
    http://channel9.msdn.com/ShowPost.aspx?PostID=137650

     

    >L<

     

     

    Wednesday, June 06, 2007 3:48 PM