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 billymac@advancedinteractive.net
Cheers...
17 Answers, 1 is accepted
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 !
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
Q2’11 SP1 of Telerik Reporting is available for download (see what's new). Get it today.
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 xDim 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()
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
HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!
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?
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:
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
HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!
Appreciate your help on this.....
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
HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!
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...
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
HAPPY WITH REPORTING? Do you feel that it is fantastic? Or easy to use? Or better than Crystal Reports? Tell the world, and help fellow developers! Write a short review about Telerik Reporting and Telerik Report Designer in Visual Studio Gallery today!
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.
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
See what's new in Telerik Reporting Q1 2013. Register for the March 4 webinar to witness the impressive new visualizations in Telerik Reporting. Just for fun, 10 webinar attendees will be randomly selected to win a Telerik T-shirt and a $50 Gift Certificate to ThinkGeek. Register now! Seats are limited!
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.
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
See what's new in Telerik Reporting Q1 2013. Register for the March 4 webinar to witness the impressive new visualizations in Telerik Reporting. Just for fun, 10 webinar attendees will be randomly selected to win a Telerik T-shirt and a $50 Gift Certificate to ThinkGeek. Register now! Seats are limited!
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 xIf you can send me the code on how to bind both to dataset and render it in the report viewer. I would appreciate it......
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
- 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
Telerik Reporting Q1 2013 available for download with impressive new visualizations. Download today from your account.