What I learned about Report Databinding

[Bill]

I am writing this for 2 reasons.

Firstly to help out anyone that like me, has spent hours on Report Databinding issues.
Secondly, to put this down on paper (virtual?) to help me remember what I learned.
The problem I was having is related to binding a SQLDatasource to a report.
I was having a hell of a time trying to figure out why the filtered datasource was not getting reflected in the web report viewer.

Heres the setup I was using:

- A Telerik  Report with 1 table and 1 crosstab in a class library (as recommended by Telerik)
- The report had the report datasource, the table and crosstab datasources pointing to the reports SQLdatasource object.
-The report had no filters and no parameters and displayed all the data in the query (via stored proc) - good so far
-The report worked fine in the designer preview -also good
- A web page with a reportviewer
- Code behind setting the reportviewer report to the report described above (The working Code shown at the end of this article)

Now here is what was happening,

When I designed the report, and viewed it in the preview tab in the report designer, the report displayed fine and showed all the data in the query as it should.
When I viewed the report in the web page using the report viewer, I used code behind to get the data from a query, set a filter and bind the datasource of the report to that. The problem I was having was that the report was displaying all the data and not the filtered data.

I tried several things to troubleshoot this over several hours...
Some of the things I noticed were:
1. I could not get the NeedDatasource in the web report code behind to fire
2. If I put a Datagrid on the page bound to the same data, it showed the filtered data just fine but the report did not.
3. Weird- if I changed the filter to return no data (ID=9999), no data displayed in the report but if I used any valid filter (ID=1), all the data displayed
4. If I set the datasource in the report to nothing it displayed nothing even though getting set in code behind

After messing with this for several hours I got it to work properly and here's what I needed to do, and what I learned in the process:

The main problem I was having was that in my report, the main report was pointing to the report SQLdatasource, as were the 2 bound objects on the report, a table and a crosstab. All of the messing around I was doing was never setting the datasource of the 2 objects properly. Setting the main report datasource did not change the 2 bound objects in the report.

To fix this I did a few things:
1. Set the 2 objects datasources to point to the main report datasource using the NeedDataSource event (IN THE REPORT)

Private Sub Table1_NeedDataSource(sender As Object, e As System.EventArgs) Handles Table1.NeedDataSource
sender.DataSource = Report.DataSource
End Sub

Private Sub Crosstab1_NeedDataSource(sender As Object, e As System.EventArgs) Handles Crosstab1.NeedDataSource
sender.DataSource = Report.DataSource
End Sub

Something important to note here:
Dont use Table1.DataSource=Report.DataSource- it wont work. Make sure you refer to 'Sender'

2. In the report constructor, be sure to set the datasource to nothing (null in C#) or the NeedDataSources WILL NOT FIRE !

 

Public Sub New()

 InitializeComponent()

 Table1.DataSource =Nothing
  Crosstab1.DataSource =Nothing

End Sub

So now in the report viewer (web page) code behind, when you run the method to load the report data, you set the main report datasource to the queried data object, whatever happens to be (Dataset, datareader, collection etc), and then since the report objects have their datasources set to nothing, the NeedDatasource events back on the report code behind fire and set the datasources for the table and crosstab to the main datasource for the report (which was set in the web page code behind).
Now- this gives us another very nice benefit, when you view the report in the report designer, it will work correctly because you leave the report and the table and the crosstab on the report bound to the SQL dataSource in the report. Some other forum posts I have seen recommended to set the datasource when building the report and then removing it when done designing.
You dont need to do that using this approach.

Here is the code for the report and web page code behind:

Report code behind (All the code)

Partial Public Class AI_Programs_Report_E
    Inherits Telerik.Reporting.Report

    Public Sub New()
        InitializeComponent()
        Table1.DataSource = Nothing
        Crosstab1.DataSource = Nothing
    End Sub

    Private Sub Table1_NeedDataSource(sender As Object, e As System.EventArgs) Handles Table1.NeedDataSource
        sender.DataSource = Report.DataSource
    End Sub

    Private Sub Crosstab1_NeedDataSource(sender As Object, e As System.EventArgs) Handles Crosstab1.NeedDataSource
        sender.DataSource = Report.DataSource
    End Sub
End Class

 
Web Page Code Behind (all revelant code only)

Imports Telerik.Web.UI
Imports Telerik.Reporting
=======================
    Protected Sub Page_PreRender(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.PreRender
        SetReportDataSource()
Blah Blah...
=======================
    Protected Sub btnfilterNow_Click(sender As Object, e As System.EventArgs) Handles btnfilterNow.Click
        SetReportDataSource()
    End Sub
=======================
    Sub SetReportDataSource()
         Dim sql As String = DSListing.SelectCommand ' Select blah from blah
    Dim connectionString As String = "Data Source=(local);Initial Catalog=AI_DNN;Integrated Security=True" Dim adapter As New SqlDataAdapter(sql, connectionString) Dim dataSet1 As New DataSet() adapter.Fill(dataSet1) Dim table As DataTable = dataSet1.Tables(0) Dim foundRows() As DataRow Dim expression As String = "ID=563" ' Or some filter string foundRows = table.Select(expression) ReportViewer1.Report = New Report Dim report1 As New TelerikReports_Sarvac.AI_Programs_Report_E() report1.DataSource = Nothing ' DSListing ' dataSet report1.DataSource = foundRows 'dataSet ReportViewer1.Report = report1 ReportViewer1.RefreshReport()
end sub

I hope this clears some things up for you and saves you some time and headaches.
Please drop me an email if it does at [e-mail]

Cheers...

[Bill]

Now after all that, I discovered something that could invalidate some of my previous speel...
I noticed after submitting that post that still, NeedDataSource was not firing in the report after the first time when the page first loaded.
The Report cintrructor was firing but not any of the NeedDataSource events.
Again after a few hours of trying stuff, I found the culprit.
The part of my page that is used to set the filter parameters and also includes the button used to reload the page- the 'Filter Now' button, was inside an AJAX panel but the report viewer was not.
I moved the AJAX panel closing tag after the report viewer and now the NeedDatasource event fires everytime.

Good GAWD !

[TonyG]

Sounds like a typical day at the office with modern technology. Thanks for the info. :)

[Steve]

Hi guys,

A much cleaner solution to reuse the same datasource in all nested Data Items would be by using Bindings and setting their DataSource property to the following expression: =ReportItem.DataObject (this expression references the parent's data item, in your case report). You can see this approach in action in the ProductCatalog demo report.
However having multiple data items (including the report being one) bound to the same data source and then filter the nested data items is a quite an overhead.
Instead you can bind them directly to =ReportItem.DataObject.MyField which retrieves the field data from the report's datasource or even better do this on data source level by creating a DataSourceParameter to filter the data based on the master report field:
 

Name	Type	Value
@MyField	String	= ReportItem.DataObject.MyField

Best wishes,
Steve
the Telerik team

[Steve]

Hi Steve

I have a different question. I am new to Telerik reporting. Actually, I am binding the data to the report viewer by using the Telerik report wizard where we can set the stored procedure name. By using this way I am able to bind. But, I don't want to use by this way. I want to bind the data using pro-grammatically in the code behind with the dataset. The output to my stored procedure is XML, so I am converting the xml into dataset. Now, I am binding that dataset to report viewer, but it's not.
For testing purpose, I have binded the dataset to gridview and it's showing me all the data. But, I am not able to do the same with the report viewer.

Dim dsSelected As New DataSet
            Dim xNodeList As Xml.XmlNodeList = xmlDocument.GetElementsByTagName("detail")
            Dim xReader As Xml.XmlTextReader
            Dim x As Integer
            For x = 0 To xNodeList.Count - 1
                xReader = New Xml.XmlTextReader(xNodeList.Item(x).OuterXml, Xml.XmlNodeType.Element, New Xml.XmlParserContext(Nothing, Nothing, Nothing, Xml.XmlSpace.None))
                dsSelected.ReadXml(xReader, XmlReadMode.InferSchema)
            Next x
 
Dim objectdatasource As New Telerik.Reporting.ObjectDataSource()
            objectdatasource.DataSource = dsSelected    
            Dim report As New Telerik.Reporting.Report()
            report.DataSource = dsSelected    
            Dim source As New Telerik.Reporting.InstanceReportSource()
            source.ReportDocument = New Report1()
            ReportViewer.ReportSource = source
            ReportViewer.DataBind()

[Steve]

Hello,

Problems we notice in your code:

• you instantiate the base class Telerik.Reporting.Report and set its DataSource property. This would accomplish nothing as the base class is an empty report definition, moreover you never use this report anywhere after that.
• you instantiate Report1() class, but is that report bound and created by you beforehand? Grid controls are not appropriate comparison, as most of them have the automagical "AutogenerateColumns" property that would iterate your datasource and create columns from it. This would not happen for a template component like Telerik Report, you should create the layout yourself either by manually laying out report items or use the Report Wizard to create the report. See Creating a Simple Report for more information.

Kind regards,
Steve
the Telerik team

[Steve]

Sorry on that as I have posted on the other forum as well.
I am have created an instance for the Report1() and I have also assigned the datasource. But the report is blank. I went to debug the code and see what's going on. Actually, the code where I am converting the xml to dataset, at the runtime, I am not able to see the data in the "Visualizer", but when I have added the quick watch to see the row count and the columns, then I noticed the dataset has the data which it shows as "31" rows(which actually it is) and as well as "10" columns. I have created the layout or rather should say "Design" using the report wizard. Now, I need to bind this resultant dataset to my report viewer. Can you verify the code which i have sent to convert the xml to dataset whether it's right or wrong?

[Steve]

Hi Steve,

Telerik Reporting is neither aware of your data source nor it matters to it, as long as it is supported and DataSet is a supported object:

• ObjectDataSource Component
• How to: Bind to a DataSet

As you state that you have bound a Grid successfully, we suspect your data is correct and that is why we asked you whether you have created the report with TextBox items with proper expressions mapping to your DataTable columns, as this is one of the differences between a Grid and Telerik Report.

If you're still having problems, it would be best to open a support ticket and attach a sample runnable project that exhibits the problem and we would advise you accordingly.

Greetings,
Steve
the Telerik team

[Steve]

Thanks for your response. I am to figure it out. My report viewer is working fine. But, it's actually taking some time to load the data. My dataset contains 31 rows with 10 columns. But it's taking 8-9 seconds to load... I have sent the code of my dataset in my earlier post.
Appreciate your help on this.....

[Steve]

Hello Steve,

I'm not sure where you're going with this, but if you talk about performance comparison of a Grid control and a reporting product and you expect similar results, you would be disappointed and it is wrong to make such comparison in a first place.

All the best,
Steve
the Telerik team

[Steve]

No No....I am not comparing the report with the Gridview. I am actually binding my dataset to objectdatasource and to the report viewer.
So, when i click "Submit Button", then I need to load my report viewer which is taking sometime to load the data. It's a bit slow. That's my concern... The dataset only contains 31 rows...

[Steve]

Hi Steve,

Usually data retrieval is very fast, but if you suspect that it is not in your case, you can profile your data retrieval. As the report showing in the viewer includes data retrieval, processing and rendering, during which certain complex layout and paging rules are applied. So the great flexibility offered by the reporting engine comes at a price: increased processing times and memory footprint.

Kind regards,
Steve
the Telerik team

[Steve]

I have posted it in the other forum. For no more confusions..I am posting in the reporting issue forum........
Thank you for the response...I will work on the radcombobox issue, and coming to the report viewer issue, this is what i have got the response. I didn't understand what you have meant.....

Hi Steve,

Usually data retrieval is very fast, but if you suspect that it is not in your case, you can profile your data retrieval. As the report showing in the viewer includes data retrieval, processing and rendering, during which certain complex layout and paging rules are applied. So the great flexibility offered by the reporting engine comes at a price: increased processing times and memory footprint.

Kind regards,
Steve 
the Telerik team

According to my code, which is 

'Converting the stored procedure output xml to a dataset.
            Dim dsSelected As New DataSet
            Dim xNodeList As Xml.XmlNodeList = xmlDocument.GetElementsByTagName("detail")
            Dim xReader As Xml.XmlTextReader
            Dim x As Integer
            For x = 0 To xNodeList.Count - 1
                xReader = New Xml.XmlTextReader(xNodeList.Item(x).OuterXml, Xml.XmlNodeType.Element, New Xml.XmlParserContext(Nothing, Nothing, Nothing, Xml.XmlSpace.None))
                dsSelected.ReadXml(xReader, XmlReadMode.InferSchema)
            Next x

'Checking the dataset row count and binding it to the objectdatasource and then finally to the report viewer.
                
	If dsSelected.Tables(0).Rows.Count > 0 Then
             ReportViewer.Visible = True
             Dim objectDataSource As New Telerik.Reporting.ObjectDataSource()
             objectDataSource.DataSource = dsSelected
             Dim report As New Report1()
             report.DataSource = dsSelected
             Dim source As New Telerik.Reporting.InstanceReportSource()
             source.ReportDocument = report
             ReportViewer.ReportSource = source
       End If

I had a break point at this line "Next x" and also "ReportViewer.ReportSource = source". At that moment my data got retrieved and it's in my dataset and also is binded to the report viewer.  After that if i click "F5", it says "Generating report" for a long time.That's it....... I don't think it's the data retrieval issue. I guess it would be the processing and rendering issue.

[Stef]

Hi Steve,

Please, check the attached sample illustrating loading data from XML file into a DataSet object and passing a table from that data set as a data source for a report. The processing is less than a second.

If this does not cover your scenario, e.g. your report definition is more complex, please modify the sample project or send us your own in a support ticket, so we can check it. We will update this forum thread in the end for anyone concern.

Kind regards,
Stef
the Telerik team

[Steve]

Stef,

Thanks for your response. I can't specify the xml path because, I am not retrieving it from my system. Actually, my stored procedure output is xml. If I have an xml file in my system then i can specify as "ds.ReadXml(Path.Combine(Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location), "../../Data.xml"))". But, since my stored procedure output is xml, I don't think I can do that way to have specified. How about the code which I have posted earlier ? I don't think there's a problem with my dataset, I guess it's something to do with the data processing and rendering to the report. I have break points in my code after the dataset binding. Its not taking too much of time.

[Stef]

Hi Steve,

Even if the XML for the DataSet comes from database there is no change in the time for which the report is processed and displayed. Please, check the modified sample (run it first to test the time for processing when the data comes from XML file and then close and rerun to test when the XML code comes from a query). We have used the same XML code as in the file for the field in the database.
Check if the data that comes from your database and read into a DataSet object is what you expect with some control with options for column auto-generation.

Let us know if you have further questions.

Regards,
Stef
the Telerik team

[Steve]

Thanks for the response. I figured out what was the issue...Thanks again.......
One more question, since my xml has multiple childnodes.

<xml tablename="RptTransferSumReport" class="RptTransferSumReport">
  <object>
    <records>
      <detail>
        <NumTransfers>3</NumTransfers>
        <Amount>4.50</Amount>
        <FromAcctNo>225096</FromAcctNo>
        <ToAcctNo>853682</ToAcctNo>
      </detail>
      <TotalCount>
        <total>3</TotalCount>
        <TotalAmount>4.50</TotalAmount>
      </total>
    </records>
  </object>
</xml>

I am able to convert this into a dataset. Problem is I am using  

Dim xmlDocument1 As New XmlDocument()

"xmlDocument1.GetElementsByTagName("detail")" and 
"xmlDocument1.GetElementsByTagName("TotalCount")" . I am able to bind "detail" as one table in a dataset(with ds.tables(0)), but I am not able to bind the "total" data to my report viewer. I am getting an error like this. I have attached the image, also my report definition. I need to show the "total" in my report viewer as well.
------------------------vb code-------------------------

Dim dsSelected As New DataSet
                    Dim xNodeList As Xml.XmlNodeList = xmlDocument1.GetElementsByTagName("detail")
                    Dim xNodeTotalList As Xml.XmlNodeList = xmlDocument1.GetElementsByTagName("total")
                    Dim xReader As Xml.XmlTextReader
                    Dim xTotalReader As Xml.XmlTextReader
                    Dim x As Integer
                    For x = 0 To xNodeList.Count - 1
                        xReader = New Xml.XmlTextReader(xNodeList.Item(x).OuterXml, Xml.XmlNodeType.Element, New Xml.XmlParserContext(Nothing, Nothing, Nothing, Xml.XmlSpace.None))
                        xTotalReader = New Xml.XmlTextReader(xNodeTotalList.Item(x).OuterXml, Xml.XmlNodeType.Element, New Xml.XmlParserContext(Nothing, Nothing, Nothing, Xml.XmlSpace.None))
                        dsSelected.ReadXml(xReader, XmlReadMode.InferSchema)
                        dsSelected.ReadXml(xTotalReader, XmlReadMode.InferSchema)
                    Next x

If you can send me the code on how to bind both to dataset and render it in the report viewer. I would appreciate it......  
 

[Stef]

Hello Steve,

The provided xml structure will create two different tables for the detail and TotalCount elements, and if the xml contains more records elements their detail and TotalCount values will be added to the corresponding tables, so depending on your user story you would need to rearrange your data,e g.:

• put the needed data on one level in one element so it can be read as one table in the Dataset object;

<xml tablename="RptTransferSumReport" class="RptTransferSumReport">
  <object>
    <records>
      <detail>
        <NumTransfers>3</NumTransfers>
        <Amount>4.50</Amount>
        <FromAcctNo>225096</FromAcctNo>
        <ToAcctNo>853682</ToAcctNo>
        <total>3</total>
        <TotalAmount>4.50</TotalAmount>
      </detail>
    </records>
  </object>
</xml>

• or flatten the data after it is read in a DataSet, e.g. create a new DataTable object containing the needed columns and rows from both tables;

Dim xmlDocument1 As New XmlDocument()
       xmlDocument1.LoadXml(dataTable.Rows(0)("XMLContent").ToString())

       Dim dsSelected As New DataSet
       Dim xNodeList As Xml.XmlNodeList = xmlDocument1.GetElementsByTagName("records")
       Dim xReader As Xml.XmlTextReader
       Dim x As Integer
       For x = 0 To xNodeList.Count - 1
           xReader = New Xml.XmlTextReader(xNodeList.Item(x).OuterXml, Xml.XmlNodeType.Element, New Xml.XmlParserContext(Nothing, Nothing, Nothing, Xml.XmlSpace.None))
           dsSelected.ReadXml(xReader)

       Next x

       Dim table1 = dsSelected.Tables(0)
       Dim table2 = dsSelected.Tables(1)
       For Each col As DataColumn In table2.Columns
           table1.Columns.Add(col.ColumnName, col.DataType)
           table1.Rows(0)(col.ColumnName) = table2.Rows(0)(col.ColumnName)
       Next

Other approach is to change the report definition layout to utilize both tables:

• use Table/SubReport item which has its own DataSource property besides the report's one - How-To: Creating Master-Detail Reports Using SubReports;

I hope this helps.

Kind regards,
Stef
the Telerik team