| View previous topic :: View next topic |
| Author |
Message |
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
 Posted: Thu Sep 01, 2005 11:19 pm Post subject: Using a small program to generate the report |
 |
|
Well since you are having to actually print something..and I can't get the report wizard to create the report layout you want.
I probrably should do this with mail merge. Howver,given how new I am to Open Office also it was faster for me to just do this basic program. I think you can follow this BASIC
code and finish the report quite easily.
You need to change 3 constants get it working on your machine
const cDatabaseName = "val"
change "val" to the name of your database
const cReportTemplate = "c:\Documents and Settings\andrew\My Documents\valreport.ott"
change the filename with path to a writer template you create. You can just create a new WRITER document and save as a template. This way you can set the default paragraph style that you would like.
CONST REPORT_TITLE = "STUDENT - FAMILY CONTACT INFORMATION "
Change this for a differnt title.
Now how to use this code. From your meny select Tools>Macros>Organize Dialogs
This will open a dialog box with three tabs. Select the Libraries tab.
On this page select NEW...
Name it something like SCHOOLWORK or whatever - then OK
Then select EDIT
Now copy and paste all the code from this message into the basic editor.
Change the CONSTANTS and you have a first report
OK, save the library.
When you want to run a report you just use the menu
Tools>Macros>Run Select your library and run the Main procedure.
| Code: |
REM ***** BASIC *****
' BECAUSE I JUST CANT FIGURE OUT HOW ELSE TO DO THIS
' really simple report
'
' I WILL DO THE FOLLOWING FOR A SIMPLE REPORT
' OPEN 2 ROWSETS ONE FOR PARENTDB AND ONE FOR STUDENTDB
'
' CREATE A NEW WRITER DOCUMENT BASED ON THE TEMPLATE 'SCHOOL_ROSTER'
'
' ITERATE OVER THE STUDENTDB ROWSET AND WRITE EACH STUDENTS DATA
' AS ONE LINE TO THE NEW DOCUMENT
' WRITE EACH FIELD FROM PARENTDB TO THE DOCUMENT
'
' THE REPORT LAYOUT
'
' TITLE
'
' DATE
'
' STUDENT RECORDS
' StuName1 GradeTeacher1
' StuName2 GradeTeacher2
' StuName3 GradeTeacher3
' StuName4 GradeTeacher4
' PARENT RECORD
' Phone1
' ParentName
' Addr
' Email
'
' .........
' ........
REM *** CHANGE ME
const cDatabaseName = "val"
REM *** CHANGE ME
const cReportTemplate = "c:\Documents and Settings\andrew\My Documents\valreport.ott"
REM *** REPORT TITLE
CONST REPORT_TITLE = "STUDENT - FAMILY CONTACT INFORMATION "
REM *** COLUMN NUMBERS FOR PARENTDB
CONST PARENT_PHONE = 6
CONST PARENT_NAME = 2
CONST PARENT_ADD = 3
CONST PARENT_EMAIL = 8
REM *** COLUMN NUMBERS FOR STUDENTDB
CONST STUDENT_LAST = 2
CONST STUDENT_FIRST = 3
CONST STUDENT_GRADE = 4
DIM iLINE_BREAK as integer
DIM iPARAGRAPH_BREAK as integer
Sub Main
dim oReportDoc as object
dim oCursor as object
DIM PARETNS AS OBJECT
DIM STUDENTS AS OBJECT
DIM oConn as object
' setup a few simple values for later
iLINE_BREAK = com.sun.star.text.ControlCharacter.LINE_BREAK
iPARAGRAPH_BREAK = com.sun.star.text.ControlCharacter.PARAGRAPH_BREAK
' GET A CONNECTION TO THE DATABASE
oConn = OPEN_DATASOURCE( cDatabaseName )
'open a new document
oReportDoc = GET_NEW_DOCUMENT()
' get a cursor to this new document
oCursor = oReportDoc.getText.createTextCursor()
' WRITE THE TILE LINE AND DATE
WRITE_LINE( REPORT_TITLE, oCursor, oReportDoc )
WRITE_PARA( "", oCursor, oReportDoc )
WRITE_LINE( DATE, oCursor, oReportDoc )
WRITE_PARA( "", oCursor, oReportDoc )
WRITE_PARA( "", oCursor, oReportDoc )
' NOW WRITE THE STUDENT AND PARENT RECORDS
PARENTS = GET_PARENTS( oConn )
WHILE PARENTS.NEXT
STUDENTS = GET_STUDENTS( PARENTS )
WHILE STUDENTS.NEXT
WRITE_LINE( FORMAT_STUDENT_LINE( STUDENTS ), oCursor, oReportDoc )
WEND
'WRITE_LINE( ">>>Parent<<<", oCursor, oReportDoc )
WRITE_LINE( PARENTS.GETSTRING(PARENT_PHONE), oCursor, oReportDoc )
WRITE_LINE( PARENTS.GETSTRING(PARENT_NAME), oCursor, oReportDoc )
WRITE_LINE( PARENTS.GETSTRING(PARENT_ADD), oCursor, oReportDoc )
WRITE_LINE( PARENTS.GETSTRING(PARENT_EMAIL), oCursor, oReportDoc )
'WRITE_LINE( "", oCursor, oReportDoc )
WRITE_PARA( "", oCursor, oReportDoc )
WEND
End Sub
FUNCTION OPEN_DATASOURCE( REG_NAME AS STRING ) AS OBJECT
DIM oDBCon as Object
OdbCon = CreateUnoService("com.sun.star.sdb.DatabaseContext").getByname( REG_NAME ). _
GetConnection( "", "" )
OPEN_DATASOURCE = oDBCon
END FUNCTION
FUNCTION GET_NEW_DOCUMENT AS OBJECT
dim oDoc as object
Dim sURL as String
Dim FileProperties(1) As New com.sun.star.beans.PropertyValue
FileProperties(0).Name="AsTemplate"
FileProperties(0).Value=True
FileProperties(1).Name="MacroExecutionMode"
FileProperties(1).Value=4
sURL = convertToURL( cReportTemplate )
oDoc = StarDesktop.LoadComponentFromUrl(sURL, "_blank" , 0, FileProperties())
GET_NEW_DOCUMENT = oDoc
END FUNCTION
sub NEW_LINE( CURSOR AS OBJECT, DOC AS OBJECT )
'CURSOR.gotoEndOfSentence( Expand )
CURSOR.gotoEndOfParagraph( Expand )
DOC.Text.insertControlCharacter(Cursor, iLINE_BREAK, False)
CURSOR.gotoNextSentence(False)
END SUB
SUB WRITE_LINE( s AS STRING, CURSOR AS OBJECT, DOC AS OBJECT)
CURSOR.STRING = S
NEW_LINE( CURSOR, DOC )
END SUB
SUB WRITE_PARA( s AS STRING, CURSOR AS OBJECT, DOC AS OBJECT)
CURSOR.STRING = S
'CURSOR.gotoEndOfSentence( Expand )
CURSOR.gotoEndOfParagraph( Expand )
DOC.Text.insertControlCharacter(Cursor, iPARAGRAPH_BREAK, False)
CURSOR.gotoNextSentence(False)
END SUB
FUNCTION GET_STUDENTS( PARENTS AS OBJECT ) AS OBJECT
DIM STUDENTS AS OBJECT
STUDENTS = createUnoService("com.sun.star.sdb.RowSet")
With STUDENTS
.activeConnection = PARENTS.ActiveConnection
.CommandType = com.sun.star.sdb.CommandType.COMMAND
.Command = "select * from StudentDB where StuFamID = " & PARENTS.GETSTRING(1)
.EscapeProcessing = False
.execute
End With
GET_STUDENTS = STUDENTS
END FUNCTION
FUNCTION GET_PARENTS( oDBConnection as object ) AS OBJECT
DIM oParents as object
oParents = createUnoService("com.sun.star.sdb.RowSet")
With oParents
.activeConnection = oDBCOnnection
.CommandType = com.sun.star.sdb.CommandType.COMMAND
.Command = "select * from ParentDB"
.EscapeProcessing = False
.execute
End With
GET_PARENTS = oParents
END FUNCTION
FUNCTION FORMAT_STUDENT_LINE( STUDENT AS OBJECT ) AS STRING
DIM S AS STRING
S = STUDENT.getString(STUDENT_LAST) & ", " & STUDENT.getString(STUDENT_FIRST)
S = S & STRING( 50 - LEN(S), "." ) & STUDENT.getString(STUDENT_GRADE)
FORMAT_STUDENT_LINE = S
END FUNCTION
|
I created the tables just like yours and this is a sample run of the report...for two families
| Code: |
STUDENT - FAMILY CONTACT INFORMATION
09/02/2005
Madison, Bob......................................2 ED
Madison, Dolly....................................1 SM
(177)655-1897
James
100 Founding Way
jmadison@foundingfathers.com
Marley, Janet.....................................1 SM
Maryley, Ted......................................4 TH
(662)555-1212
Bob
200 E. Indy Ln
bmarley@coolsounds.com
|
HTH
Drew
_________________
Blog - http://baseanswers.spaces.live.com/ |
|
| Back to top |
|
 |
valkal
General User
Joined: 25 Aug 2005
Posts: 13
Location: Minnesota
|
| Posted: Sat Sep 03, 2005 5:04 pm Post subject: Error running BASIC code |
|
|
Drew -
Thanks --TONS-- for your BASIC code!!
I got a chance to install & run it tonight, and it gave me an error.
| Quote: | OpenOffice.org 1.9.122 BASIC runtime error:
Property or method not found. |
The OdbCon= statement in the following code piece was highlighted:
| Code: | FUNCTION OPEN_DATASOURCE( REG_NAME AS STRING ) AS OBJECT
DIM oDBCon as Object
OdbCon = CreateUnoService("com.sun.star.sdb.DatabaseContext").getByname( REG_NAME ). _
GetConnection( "", "" )
OPEN_DATASOURCE = oDBCon
END FUNCTION |
Thanks!
_________________
Val
Running OOo 2.0.2 on Windows XP |
|
| Back to top |
|
|
DrewJensen
Super User
Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD
|
| Posted: Sun Sep 04, 2005 2:40 am Post subject: |
|
|
That was a problem with the line break because of the post to the board.
Just put the line all together by removing the space and the _
OdbCon = CreateUnoService("com.sun.star.sdb.DatabaseContext").getByname( REG_NAME ).GetConnection( "", "" )
Should be fine then.
_________________
Blog - http://baseanswers.spaces.live.com/ |
|
| Back to top |
|
|
|
|
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|
