Platforms to show: All Mac Windows Linux Cross-Platform
/SQL/Reporting From A Database/Reports Example with MBS
Required plugins for this example: MBS SQL Plugin
You find this example project in your Plugins Download as a Xojo project file within the examples folder: /SQL/Reporting From A Database/Reports Example with MBS
This example is the version from Thu, 13th Dec 2017.
Project "Reports Example with MBS.xojo_binary_project"
MenuBar MenuBar1
MenuItem FileMenu = "&File"
MenuItem FileClose = "Close"
MenuItem UntitledSeparator0 = "-"
MenuItem FileQuit = "Quit"
MenuItem UntitledMenu1 = ""
MenuItem EditMenu = "&Edit"
MenuItem EditUndo = "Undo"
MenuItem UntitledMenu0 = "-"
MenuItem EditCut = "Cut"
MenuItem EditCopy = "Copy"
MenuItem EditPaste = "Paste"
MenuItem EditClear = "Clear"
MenuItem UntitledSeparator = "-"
MenuItem EditSelectAll = "Select All"
MenuItem HelpMenu = "Help"
End MenuBar
Class App Inherits Application
EventHandler Sub Open()
// Create Database Object
OrdersDB = New SQLDatabaseMBS
// where is the library?
OrdersDB.SetFileOption SQLConnectionMBS.kOptionLibrarySQLite, getfolderitem("/usr/lib/libsqlite3.0.dylib", folderitem.PathTypeShell)
dim file as FolderItem = FindFile("Orders.rsd")
// Set Database File
#if RBVersion >= 2013 then
// Xojo
OrdersDB.DatabaseName="sqlite:"+file.NativePath
#else
// Real Studio
OrdersDB.DatabaseName="sqlite:"+file.unixpathMBS
#endif
// Connect to the database
if OrdersDB.Connect() then
SampleReports.Show
end if
End EventHandler
Function FindFile(name as string) As FolderItem
// Look for file in parent folders from executable on
dim parent as FolderItem = app.ExecutableFile.Parent
while parent<>Nil
dim file as FolderItem = parent.Child(name)
if file<>Nil and file.Exists then
Return file
end if
parent = parent.Parent
wend
End Function
Property OrdersDB As SQLDatabaseMBS
End Class
Class SampleReports Inherits Window
Control RunReportButton Inherits PushButton
ControlInstance RunReportButton Inherits PushButton
EventHandler Sub Action()
Dim ps As New PrinterSetup
dim sql as string
if rpt <> nil then rpt.close
select case ListOfReportsPopup.RowTag(ListOfReportsPopup.ListIndex)
case "ListofOrders"
// Build the SQL statement that will be used to select the records
sql = "SELECT O.OrderNumber, C.ID, C.Company, C.LastName, O.DateOrdered, O.Total"+ _
" FROM Orders O, Customers C WHERE O.CustomerID = C.ID"
rpt = New ListofOrders
case "BreakingListofOrders"
// Build the SQL statement that will be used to select the records
sql = "SELECT O.OrderNumber, C.ID, C.Company, C.LastName, O.DateOrdered, O.Total"+ _
" FROM Orders O, Customers C WHERE O.CustomerID = C.ID order by c.company, o.ordernumber"
rpt = New BreakingListofOrders
case "ListOfProducts"
// Build the SQL statement that will be used to select the records
sql = "SELECT * from Products"
rpt = New ListOfProducts
end select
// Now we select the records from the database and add them to the list.
dim rs as recordSet
rs = app.ordersDB.sqlSelect( sql )
if rs = nil then
beep
MsgBox "No records found to print."
else
dim rsq as new Reports.RecordSetQuery(rs)
If rpt.Run( rsq, ps ) Then
If rpt.Document <> Nil Then ReportViewer1.SetDocument( rpt.Document )
End If
end if
End EventHandler
End Control
Control ReportViewer1 Inherits ReportViewer
ControlInstance ReportViewer1 Inherits ReportViewer
End Control
Control ListOfReportsPopup Inherits PopupMenu
ControlInstance ListOfReportsPopup Inherits PopupMenu
EventHandler Sub Change()
EnableButtons
ReportViewer1.mCurrentReportType = me.RowTag(me.ListIndex)
End EventHandler
EventHandler Sub Open()
me.AddRow "List of Orders"
me.RowTag(me.ListCount-1) = "ListOfOrders"
me.AddRow "List of Orders - grouped by company"
me.RowTag(me.ListCount-1) = "BreakingListofOrders"
me.AddRow "List of Products"
me.RowTag(me.ListCount-1) = "ListOfProducts"
me.ListIndex = 0
End EventHandler
End Control
Control StaticText1 Inherits Label
ControlInstance StaticText1 Inherits Label
End Control
EventHandler Sub Close()
if rpt <> nil then rpt.close
End EventHandler
EventHandler Sub Open()
EnableButtons
End EventHandler
Private Sub EnableButtons()
RunReportButton.Enabled = ListOfReportsPopup.ListIndex > -1
End Sub
Property Private rpt As Report
End Class
Class ReportViewer Inherits ContainerControl
Control Canvas1 Inherits Canvas
ControlInstance Canvas1 Inherits Canvas
EventHandler Sub Paint(g As Graphics, areas() As REALbasic.Rect)
If mCurrentPicture <> Nil Then
g.DrawPicture( mCurrentPicture, 0, 0, Me.Width, Me.Height, 0, Scrollbar1.Value, Me.Width, Me.Height )
Else
g.DrawRect( 0, 0, Me.Width, Me.Height )
End If
End EventHandler
End Control
Control ScrollBar1 Inherits ScrollBar
ControlInstance ScrollBar1 Inherits ScrollBar
EventHandler Sub ValueChanged()
Canvas1.Refresh( False )
End EventHandler
End Control
Control PreviousButton Inherits PushButton
ControlInstance PreviousButton Inherits PushButton
EventHandler Sub Action()
If mCurrentPage > 1 Then SetCurrentPage( mCurrentPage - 1 )
End EventHandler
End Control
Control NextButton Inherits PushButton
ControlInstance NextButton Inherits PushButton
EventHandler Sub Action()
If mCurrentPage < mDocument.PageCount Then SetCurrentPage( mCurrentPage + 1 )
End EventHandler
End Control
Control PrintReport Inherits PushButton
ControlInstance PrintReport Inherits PushButton
EventHandler Sub Action()
Dim ps As New PrinterSetup
dim sql as string
Dim rpt As Report
select case mCurrentReportType
case "ListofOrders"
// Build the SQL statement that will be used to select the records
sql = "SELECT O.OrderNumber, C.ID, C.Company, C.LastName, O.DateOrdered, O.Total"+ _
" FROM Orders O, Customers C WHERE O.CustomerID = C.ID"
rpt = New ListofOrders 'set the report project item we are going to use to print the report
case "BreakingListofOrders"
// Build the SQL statement that will be used to select the records
sql = "SELECT O.OrderNumber, C.ID, C.Company, C.LastName, O.DateOrdered, O.Total"+ _
" FROM Orders O, Customers C WHERE O.CustomerID = C.ID order by c.company, o.ordernumber"
rpt = New BreakingListofOrders 'set the report project item we are going to use to print the report
end select
// Now we select the records from the database and add them to the list.
dim rs as recordSet
rs = app.ordersDB.sqlSelect( sql ) 'select the records using the sqlquery chosen above
if rs = nil then
beep
MsgBox "No records found to print."
else
'set the resolution to 300 DPI for printing
ps.MaxHorizontalResolution = 300
ps.MaxVerticalResolution = 300
If ps.PageSetupDialog Then
dim g as graphics
g = OpenPrinterDialog(ps, nil)
if g <> nil then
dim rsq as new Reports.RecordSetQuery(rs) 'Put the records found into a Reports.DataSet
If rpt.Run( rsq, ps ) Then 'if the report runs successfully
rpt.Document.Print(g)
End If
end if
end if
end if
End EventHandler
End Control
Private Sub SetCurrentPage(pageNum As Integer)
mCurrentPage = pageNum
mCurrentPicture = mDocument.Page(mCurrentPage)
ScrollBar1.Maximum = mCurrentPicture.Height - Canvas1.Height
ScrollBar1.Value = 0
Canvas1.Refresh( False )
End Sub
Sub SetDocument(doc As Reports.RBReportDocument)
mDocument = doc
mCurrentPage = 1
If doc.PageCount > 0 Then SetCurrentPage( mCurrentPage )
End Sub
Property Private mCurrentPage As Integer
Property Private mCurrentPicture As Picture
Property mCurrentReportType As String
Property Private mDocument As Reports.RBReportDocument
End Class
Class ListOfOrders Inherits Report
Control RectangleShape1 Inherits ReportRectangleShape
ControlInstance RectangleShape1 Inherits ReportRectangleShape
End Control
Control Label1 Inherits ReportLabel
ControlInstance Label1 Inherits ReportLabel
End Control
Control Label2 Inherits ReportLabel
ControlInstance Label2 Inherits ReportLabel
End Control
Control Label3 Inherits ReportLabel
ControlInstance Label3 Inherits ReportLabel
End Control
Control Label4 Inherits ReportLabel
ControlInstance Label4 Inherits ReportLabel
End Control
Control LineShape1 Inherits ReportLineShape
ControlInstance LineShape1 Inherits ReportLineShape
End Control
Control Field1 Inherits ReportField
ControlInstance Field1 Inherits ReportField
End Control
Control Field2 Inherits ReportField
ControlInstance Field2 Inherits ReportField
End Control
Control Field3 Inherits ReportField
ControlInstance Field3 Inherits ReportField
End Control
ReportSection Body
ReportSection PageFooter
ReportSection PageHeader
End Class
Class BreakingListofOrders Inherits Report
Control Label1 Inherits ReportLabel
ControlInstance Label1 Inherits ReportLabel
End Control
Control Field2 Inherits ReportField
ControlInstance Field2 Inherits ReportField
End Control
Control Field1 Inherits ReportField
ControlInstance Field1 Inherits ReportField
End Control
Control Label2 Inherits ReportLabel
ControlInstance Label2 Inherits ReportLabel
End Control
Control Field3 Inherits ReportField
ControlInstance Field3 Inherits ReportField
End Control
Control Label4 Inherits ReportLabel
ControlInstance Label4 Inherits ReportLabel
End Control
Control Label5 Inherits ReportLabel
ControlInstance Label5 Inherits ReportLabel
End Control
Control Field4 Inherits ReportField
ControlInstance Field4 Inherits ReportField
End Control
Control Label6 Inherits ReportLabel
ControlInstance Label6 Inherits ReportLabel
End Control
Control Field5 Inherits ReportField
ControlInstance Field5 Inherits ReportField
End Control
ReportSection Body
ReportSection GroupFooter1
ReportSection GroupFooter2
ReportSection GroupHeader1
ReportSection GroupHeader2
ReportSection PageFooter
ReportSection PageHeader
End Class
Class ListOfProducts Inherits Report
Control Label1 Inherits ReportLabel
ControlInstance Label1 Inherits ReportLabel
End Control
Control Label2 Inherits ReportLabel
ControlInstance Label2 Inherits ReportLabel
End Control
Control Label3 Inherits ReportLabel
ControlInstance Label3 Inherits ReportLabel
End Control
Control Label4 Inherits ReportLabel
ControlInstance Label4 Inherits ReportLabel
End Control
Control Field1 Inherits ReportField
ControlInstance Field1 Inherits ReportField
End Control
Control Field2 Inherits ReportField
ControlInstance Field2 Inherits ReportField
End Control
Control Field3 Inherits ReportField
ControlInstance Field3 Inherits ReportField
End Control
Control Picture1 Inherits ReportPicture
ControlInstance Picture1 Inherits ReportPicture
End Control
ReportSection Body
ReportSection PageFooter
ReportSection PageHeader
End Class
End Project
The items on this page are in the following plugins: MBS SQL Plugin.
