none
MS Reporting using XML with multiple data sets RRS feed

  • Question

  • Wow, the MS reporting documentation really does lack viable documentation.  If anybody knows of a good book, I'm all ears, (not the Ross Perot type of ears). 

    I am trying to use MS reporting for the following schema as it's data source:

    <?xml version="1.0" encoding="utf-8"?>
    <xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
     <xs:element name="xmlErrorRoot">
      <xs:complexType>
       <xs:sequence>
        <xs:element name="RecordType">
         <xs:complexType>
          <xs:sequence>
           <xs:element maxOccurs="unbounded" name="Error">
            <xs:complexType>
             <xs:sequence>
              <xs:element name="ErrorCategory" type="xs:string" />
              <xs:element name="ErrorNumber" type="xs:unsignedByte" />
              <xs:element name="ErrMsg" type="xs:string" />
              <xs:element name="DataCode" type="xs:string" />
              <xs:element minOccurs="0" name="RecordNumber" type="xs:unsignedShort" />
             </xs:sequence>
            </xs:complexType>
           </xs:element>
          </xs:sequence>
          <xs:attribute name="ID" type="xs:unsignedShort" use="required" />
          <xs:attribute name="Data" type="xs:string" use="required" />
         </xs:complexType>
        </xs:element>
       </xs:sequence>
      </xs:complexType>
     </xs:element>
    </xs:schema>
    This schema makes you have two data sets to be used in the Report.  One data set I named RecordData which I bind to the RecordType Table in the ds.  The Other is ErrorData which is bound to the Error Table in the ds.

    This is the code I use to populate and show the reports:

      Public Shared Sub ShowMSErrorReport(ByVal XmlDataLocation As String)
        Dim myViewer As New TWOReportViewer
        myViewer.ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local
        myViewer.ReportViewer1.LocalReport.ReportEmbeddedResource = "TheMissingLink.xmlImportErrorLog.rdlc"
    
        Dim ds As New DataSet
        ds.ReadXml(XmlDataLocation)
    
        ''Use the following snippit to see if the ds is populated
        'For t = 0 To ds.Tables.Count - 1
        '  Dim rowColl As DataRowCollection = ds.Tables.Item(t).Rows
        '  For r = 0 To rowColl.Count - 1
        '    For c = 0 To ds.Tables.Item(t).Columns.Count - 1
        '      Console.WriteLine("Table :{2}: Column :{1}: Data:{0}:", rowColl.Item(r).Item(c).ToString, _
        '               ds.Tables.Item(t).Columns.Item(c).ColumnName, _
        '               ds.Tables.Item(t).TableName)
        '    Next
        '  Next
        'Next
    
        Dim dsErrorData As New ReportDataSource
        Dim dsRecordData As New ReportDataSource
    
        dsErrorData.Name = "ErrorData"
        dsErrorData.Value = ds.Tables("Error")
    
    
        myViewer.ReportViewer1.LocalReport.DataSources.Add(dsErrorData)
        myViewer.Show()
        myViewer.ReportViewer1.RefreshReport()
    
      End Sub
    
    On my report I just dropped in the RecordNumber, ErrorNumber, ErrMsg into the details section of a list.

    There are two problems. 

    1) I can't get the report to show ANY data at all, (not even with a simple list).  But, if I put in headers, the text shows for the header, so I know the report definition is good. 

    2) How do you add data from two different data sets, when the tablix requires that you bind it to only one data set at a time?

    I'm assuming that you have to link the two tables together somehow as well.  I can't find any documentation on accomplishing that either.

    I'm also assuming that I will need to bind in both data sources with the code below.

     Public Shared Sub ShowMSErrorReport(ByVal XmlDataLocation As String)
        Dim myViewer As New TWOReportViewer
        myViewer.ReportViewer1.ProcessingMode = Microsoft.Reporting.WinForms.ProcessingMode.Local
        myViewer.ReportViewer1.LocalReport.ReportEmbeddedResource = "TheMissingLink.xmlImportErrorLog.rdlc"
    
        Dim ds As New DataSet
        ds.ReadXml(XmlDataLocation)
    
        ''Use the following snippit to see if the ds is populated
        'For t = 0 To ds.Tables.Count - 1
        '  Dim rowColl As DataRowCollection = ds.Tables.Item(t).Rows
        '  For r = 0 To rowColl.Count - 1
        '    For c = 0 To ds.Tables.Item(t).Columns.Count - 1
        '      Console.WriteLine("Table :{2}: Column :{1}: Data:{0}:", rowColl.Item(r).Item(c).ToString, _
        '               ds.Tables.Item(t).Columns.Item(c).ColumnName, _
        '               ds.Tables.Item(t).TableName)
        '    Next
        '  Next
        'Next
    
        Dim dsErrorData As New ReportDataSource
        Dim dsRecordData As New ReportDataSource
    
        dsErrorData.Name = "ErrorData"
        dsErrorData.Value = ds.Tables("Error")
    
        dsRecordData.Name = "RecordData"
        dsRecordData.Value = ds.Tables("RecordType")
    
        myViewer.ReportViewer1.LocalReport.DataSources.Add(dsRecordData)
        myViewer.ReportViewer1.LocalReport.DataSources.Add(dsErrorData)
        myViewer.Show()
        myViewer.ReportViewer1.RefreshReport()
    
      End Sub

    It would be great if there was a visual example to accomplish this.  This is SO simple to do in Crystal.  But the MS reporting is really hard to grasp coming from a tool like Crystal.

    Any help out there?

     


    Greg Kowieski The World On-Line
    Tuesday, May 18, 2010 12:24 PM

All replies

  • Hi Greg,

    Yes, one data region (e.g. a tablix) can only bind to one data set.

    I assume TWOReportViewer is a windows form. It's not clear what ReportViewer1 properties are already set in that form. What happens if you add the following line before you add the data sources?

    myViewer.ReportViewer1.LocalReport.DataSources.Clear()

    Can you also supply a subset of your XML data?


    Cephas Lin This posting is provided "AS IS" with no warranties.
    Tuesday, May 18, 2010 3:52 PM
    Moderator
  • Sample XML data.
    <?xml version="1.0" encoding="utf-8"?>
    <root>
     <RecordType ID="22179" Data="C:\Users\Greg\AppData\Roaming\Microsoft Corporation\Microsoft Dynamics GP\10.0.324.0\TRV CHANGE PROG_22179.xml">
      <Error>
       <ErrorCategory>Imported Records</ErrorCategory>
       <ErrorNumber>0</ErrorNumber>
       <ErrMsg>Record number :1 Imported</ErrMsg>
       <DataCode>Project Type: SUB JOB</DataCode>
       <RecordNumber>1</RecordNumber>
      </Error>
      <Error>
       <ErrorCategory>Imported Records</ErrorCategory>
       <ErrorNumber>0</ErrorNumber>
       <ErrMsg>Record number :2 Imported</ErrMsg>
       <DataCode>Project Type: FINMANAGEMENT I</DataCode>
       <RecordNumber>2</RecordNumber>
      </Error>
      <Error>
       <ErrorCategory>Imported Records</ErrorCategory>
       <ErrorNumber>0</ErrorNumber>
       <ErrMsg>Record number :3 Imported</ErrMsg>
       <DataCode>Project Type: GPINVOICE PDF R</DataCode>
       <RecordNumber>3</RecordNumber>
      </Error>
      <Error>
       <ErrorCategory>_Results</ErrorCategory>
       <ErrorNumber>0</ErrorNumber>
       <ErrMsg>Start Time for impProjectTypes: 5/21/2010 11:51:52 AM
    End Time: 5/21/2010 11:51:52 AM
    Elapsed Time: 0:0:0.3260
    Records imported:65
    Errors Found:0</ErrMsg>
       <DataCode>Import Results for Project Types</DataCode>
      </Error>
     </RecordType>
    </root>

    TWOReportViewer is a windows form with the reportviewer embedded.  No other properties other than the bound report are set outside the code snippet above.  ThIt shows the two data sets, RecordData and ErrorData as the datasources. 

    Just to clarify, if the tablix can only be bound to one data set, how does one pull related information onto a MS report without parsing through the data into a flat file prior to calling the report?

    If this is true, this is a serious drawback of MS reporting over Crystal. 

    PS, thanks for taking time to help.  I was beginning to think I won the award for asking a question that no one could answer. :-)

     


    Greg Kowieski The World On-Line
    Friday, May 21, 2010 4:59 PM
  • Greg,

    Don't know why your report won't show your data. I took your schema and data and successfully generated the report with data below. You can check it against your steps, and try following it and see if you get different results. NOTE: My environment is VS2010.

    1. Add schema and data above to a VB project as TestDataSet.xsd and TestData.xml. The VB project is called "VBApp".
    2. Add a report to the project
    3. Create a new dataset, name it "RecordType", and select the RecordType dataset in Available datasets
    4. Create a new dataset, name it "Error", and select the Error dataset in Available datasets
    5. Create two tables, each for one of the datasets.
    6. Add fields from the datasets to their respective tables
    7. Add a VB form called "TWOReportViewer" to the project
    8. Drag a ReportViewer control to the new form
    9. Select the new report in Choose a Report in the ReportViewer Tasks panel.
    10. Select Dock to Parent Container
    11. Add a Button control to the main form (Form1.vb)
    12. Double-click the button on the main form to add a click event handler
    13. Replace Form1.vb code view with the following code.

    Imports

     

     

    Microsoft.Reporting.WinForms
    Imports System.Data

    Public

     

     

    Class Form1
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
           
    Dim testSet As New DataSet()
            testSet.ReadXml(
    "..\..\TestData.xml")
           
    Dim testform As New TWOReportViewer()
            testform.ReportViewer1.ProcessingMode =
    ProcessingMode.Local
            testform.ReportViewer1.LocalReport.ReportEmbeddedResource =
    "VBApp.Report1.rdlc"
           
    testform.ReportViewer1.LocalReport.DataSources.Add(New ReportDataSource("RecordType", testSet.Tables("RecordType")))
            testform.ReportViewer1.LocalReport.DataSources.Add(
    New ReportDataSource("Error", testSet.Tables("Error")))
            testform.Show()
            testform.ReportViewer1.RefreshReport()
       
    End Sub
    End
    Class

     

     

     

     

     

     

    14. Run the application


    Cephas Lin This posting is provided "AS IS" with no warranties.
    Monday, May 24, 2010 10:48 PM
    Moderator
  • Thanks, I'll try it in the next day or two.  Have to get the release out in 2008 instead using crystal.  Looking at your instructions can we now show data from the two different "Tables" in the same grouping?

    Also, maybe the problem is that I'm doing nothing with tables. I mean, I'm reading in the xml into the data set which is loading the data set tables.  But I'm not doing anthing on the report with tables.  Could that be where I'm going wrong?


    Greg Kowieski The World On-Line

    Wednesday, May 26, 2010 1:47 AM