Form1.vb

Imports System
Imports System.Windows.Forms
#Region "usings_sql"
Imports DevExpress.DataAccess.ConnectionParameters
Imports DevExpress.DataAccess.Sql
#End Region
#Region "using_datafederation"
Imports DevExpress.DataAccess.DataFederation
#End Region
#Region "using_excel"
Imports DevExpress.DataAccess.Excel
Imports System.IO
#End Region
#Region "usings_report"
Imports System.ComponentModel
Imports System.Drawing
Imports DevExpress.XtraReports.UI
#End Region

Namespace BindReportToFederatedUnionQuery
	Partial Public Class Form1
		Inherits Form

		Public Sub New()
			InitializeComponent()
		End Sub
		Private Sub Button1_Click(ByVal sender As Object, ByVal e As EventArgs) Handles button1.Click
			#Region "ShowDesigner"
			Dim designTool As New ReportDesignTool(CreateReport())
			designTool.ShowRibbonDesignerDialog()
			#End Region
		End Sub
		#Region "CreateFederationDataSource"
		Private Shared Function CreateFederationDataSource(ByVal sql As SqlDataSource, ByVal excel As ExcelDataSource) As FederationDataSource
			' Create a federated query's SQL and Excel sources.
			Dim sqlSource As New Source(sql.Name, sql, "Customers")
			Dim excelSource As New Source(excel.Name, excel, "")

			' Create a federated Union query.
			Dim contactsNode = sqlSource.From().Select("ContactName", "City", "Phone").Build().Union(excelSource.From().Select("ContactName", "City", "Phone").Build(), UnionType.Union).Build("Contacts")
				' Select the "ContactName", "City" and "Phone" columns from the SQL source.
				' Union the SQL source with the Excel source.
					' Select the "ContactName", "City" and "Phone" columns from the Excel source.
				' Specify the query's name and build it.


			' Create a federated data source and add the federated query to the collection.
			Dim federationDataSource = New FederationDataSource()
			federationDataSource.Queries.Add(contactsNode)
			' Build the data source schema to display it in the Field List.
			federationDataSource.RebuildResultSchema()

			Return federationDataSource
		End Function
		#End Region
		#Region "CreateReport"
		Public Shared Function CreateReport() As XtraReport
			' Create a new report.
			Dim report = New XtraReport()

			' Create data sources. 
			Dim sqlDataSource = CreateSqlDataSource()
			Dim excelDataSource = CreateExcelDataSource()
			Dim federationDataSource = CreateFederationDataSource(sqlDataSource, excelDataSource)
			' Add all data sources to the report to avoid serialization issues. 
			report.ComponentStorage.AddRange(New IComponent() { sqlDataSource, excelDataSource, federationDataSource })
			' Assign a federated data source to the report.
			report.DataSource = federationDataSource
			report.DataMember = "Contacts"

			' Add the Detail band and labels bound to the federated data source's fields.
			Dim detailBand = New DetailBand() With {.HeightF = 50}
			report.Bands.Add(detailBand)
			Dim contactNameLabel = New XRLabel() With {.WidthF = 150}
			Dim cityLabel = New XRLabel() With {.WidthF = 150, .LocationF = New PointF(200, 0)}
			Dim phoneLabel = New XRLabel() With {.WidthF = 200, .LocationF = New PointF(400, 0)}
			contactNameLabel.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[ContactName]"))
			cityLabel.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[City]"))
			phoneLabel.ExpressionBindings.Add(New ExpressionBinding("BeforePrint", "Text", "[Phone]"))
			detailBand.Controls.AddRange( { contactNameLabel, cityLabel, phoneLabel })

			Return report
		End Function
		#End Region
		#Region "CreateSqlDataSource"
		Private Shared Function CreateSqlDataSource() As SqlDataSource
			Dim connectionParameters = New SQLiteConnectionParameters("Data/nwind.db", Nothing)
			Dim sqlDataSource = New SqlDataSource(connectionParameters) With {.Name = "Sql_Customers"}
			Dim categoriesQuery = SelectQueryFluentBuilder.AddTable("Customers").SelectAllColumnsFromTable().Build("Customers")
			sqlDataSource.Queries.Add(categoriesQuery)
			sqlDataSource.RebuildResultSchema()
			Return sqlDataSource
		End Function
		#End Region
		#Region "CreateExcelDataSource"
		Private Shared Function CreateExcelDataSource() As ExcelDataSource
			Dim excelDataSource = New ExcelDataSource() With {.Name = "Excel_Suppliers"}
			excelDataSource.FileName = Path.Combine(Path.GetDirectoryName(GetType(Form1).Assembly.Location), "Data/Suppliers.xlsx")
			excelDataSource.SourceOptions = New ExcelSourceOptions() With {.ImportSettings = New ExcelWorksheetSettings("Sheet")}
			excelDataSource.RebuildResultSchema()
			Return excelDataSource
		End Function
		#End Region
	End Class
End Namespace