How to Use a Single SQL DataSet in Master-Detail scenario
Environment
| Product | Progress® Telerik® Reporting |
Description
When you need to pass data from a SqlDataSource bound to a data item to its child data item, for example in the Master-Detail scenario, you need to pass all of the data. This is due to the fact that SqlDataSource can return only a single DataTable. Even if the query returns a DataSet, only the first of the tables will be used - Selecting Data with the SqlDataSource component. In some cases, this may significantly deteriorate the performance.
The general approach for binding the DataSource of the child data item to parent data is explained in the
Use DataObject as a datasource for nested data items (Table, List, Crosstab, Graph) section of
How to use the ReportItem.DataObject property in expressions article.
Suggested Workarounds
MS SQL Server 2016 introduced the JSON functions that enable you to combine classic relational columns with columns that contain data formatted as JSON text. This way you may compose a query that returns the main data as regular columns of a DataTable, and the data for the child data item converted into a single JSON field. Here is a sample query:
select c.ProductCategoryId, c.Name,
(select * from Production.ProductSubcategory sc where sc.ProductCategoryID = c.ProductCategoryID for Json Auto) as JSONOUT
from Production.ProductCategory c
The fields ProductCategoryId and Name can be used for the main data item. The field JSONOUT can be used as a Source for a JsonDataSource providing data to the nested data item. This source that will become available only at runtime can be bound to the DataSource property of the child data item with the following Expression:
Property path | Expression
DataSource.Source | =ReportItem.DataObject.JSONOUT
Design Time Support
The above Binding will replace the design time JsonDataSource -> Source property of the JsonDataSource attached to the child data item. Therefore, you may assign a dummy JSON data with the same schema (in the particular case the schema of the ProductSubcategory table) to the child data item's DataSource for design purposes. This will allow you to use the Fields Global Object to easily design the layout of the nested data item.
Notes
Applicable for MS SQL Server 2016 and later