Xojo Conferences
MBSSep2018MunichDE
XDCMay2019MiamiUSA

Platforms to show: All Mac Windows Linux Cross-Platform

/SQL/Reporting From A Database/Reports Example with MBS
Function:
Required plugins for this example: MBS MacOSX Plugin, 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.rbp"
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

Feedback, Comments & Corrections

The items on this page are in the following plugins: MBS SQL Plugin.




Links
MBS Xojo Plugins