OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Join 2 tables on printed report?
Goto page Previous  1, 2
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Thu Sep 01, 2005 11:19 pm    Post subject: Using a small program to generate the report Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
valkal
General User
General User


Joined: 25 Aug 2005
Posts: 13
Location: Minnesota

PostPosted: Sat Sep 03, 2005 5:04 pm    Post subject: Error running BASIC code Reply with quote

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
View user's profile Send private message
DrewJensen
Super User
Super User


Joined: 06 Jul 2005
Posts: 2616
Location: Cumberland, MD

PostPosted: Sun Sep 04, 2005 2:40 am    Post subject: Reply with quote

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
View user's profile Send private message Send e-mail Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Goto page Previous  1, 2
Page 2 of 2

 
Jump to:  
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