Question About Generating Reports

Getting your data onto paper - or the web - Discussing the reports features of Base

Question About Generating Reports

Postby rhythmnation2004 » Sat Jan 26, 2008 4:50 am

Hello, I am new to this forum (and new to database programs in general). To begin with, I'm so confused. What I am trying to do is create a database to store customer information. Here is what I want to accomplish:

1. Have one master file called "Register of Customers", that includes the name, address, and phone number of every customer. So far I have accomplished this by inputting all customer information into a table and creating a report that has the style I want.

2. Aside from being able to generate a report including the list of all customers, I also want to be able to pull information on just one customer and generate a report with that one customer's information only.

If you're confused (as I am), let me offer this example:
Let's say the government has a "database" with all birth records in it. They could, if they desired, print out one report listing all recorded birth information for the year 2007. Or, they could issue birth certificates on particular births, that only include information on one particular birth.

Does this make sense?

Is there anyway to accomplish this using OpenOffice.org Base?
rhythmnation2004
 
Posts: 10
Joined: Sat Jan 26, 2008 4:43 am

Re: Question About Generating Reports

Postby wurzel » Tue Jan 29, 2008 12:35 am

rhythmnation2004 wrote:Does this make sense?

Is there anyway to accomplish this using OpenOffice.org Base?


Yes, and yes. However, your report needs to be based on a selection of one particular customer, i.e. you need to create a report containing the fields that you want to see displayed. It should be possible to then just select one row of data corresponding to one customer and have that fill the report. One way to do this would be, for example, to create a Writer document into which you'll have copy/pasted your column headers (field names) from your table. You can get the table to show up at the same time as the Writer document by pressing F4 and selecting the adequate table from the left hand column. Then all you have to do is select one of the rows corresponding to a customer and the data will be filled in automatically into the Writer document.

Alex
wurzel
Volunteer
 
Posts: 145
Joined: Sun Dec 09, 2007 10:39 am

Re: Question About Generating Reports

Postby JohnV » Tue Jan 29, 2008 1:42 am

Comments/additions to the prior post.
to create a Writer document into which you'll have copy/pasted your column headers (field names) from your table.
I believe he meant dragged and dropped your field names.
Then all you have to do is select one of the rows corresponding to a customer and the data will be filled in automatically into the Writer document.
The merge can be completed two easy ways.
1) Use F4, select a record and click the Data to Fields icon found above the field names.
2) Open document, click Print icon, answer 'yes' to the 'form letter' query, select the record.
For both methods View > Field Names should be off.
Both methods assume you have a database registered as a data source - if you see it when you press F4 then it is registered.

You do not want to try Tools > Mail Merge Wizard for a single record.
JohnV
Volunteer
 
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: Question About Generating Reports

Postby rhythmnation2004 » Wed Jan 30, 2008 3:57 am

OK, I've done what you guys have said. I drag-and-drop the field names into the form I want. But when I save the form, it just saves it as a Writer document, and the field name becomes text in the document. For example, when I drag the field name "ID" into the document, it says "<ID>". I can click the "fill fields" icon and it inputs the data into that space. But if I save that document as is and try to do the same thing later, "<ID>" is just text, and I can't use that same process again. Is there anyway to save this report as a template that I can use over and over?
rhythmnation2004
 
Posts: 10
Joined: Sat Jan 26, 2008 4:43 am

Re: Question About Generating Reports

Postby Villeroy » Wed Jan 30, 2008 4:01 am

Save it, close and open again. When you just open the report the data get loaded into the report. For editing you call "Edit..." from the report's context menu.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27899
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Question About Generating Reports

Postby wurzel » Wed Jan 30, 2008 10:28 am

rhythmnation2004 wrote:OK, I've done what you guys have said. I drag-and-drop the field names into the form I want. But when I save the form, it just saves it as a Writer document, and the field name becomes text in the document. For example, when I drag the field name "ID" into the document, it says "<ID>". I can click the "fill fields" icon and it inputs the data into that space. But if I save that document as is and try to do the same thing later, "<ID>" is just text, and I can't use that same process again. Is there anyway to save this report as a template that I can use over and over?


I take it you did save the document as a template (OTT and not ODT) before filling it with the data from your db ?

Alex
wurzel
Volunteer
 
Posts: 145
Joined: Sun Dec 09, 2007 10:39 am

Re: Question About Generating Reports

Postby JohnV » Wed Jan 30, 2008 4:53 pm

If you are saving the file to .doc format then a field value will be converted to text. MS Word does not understand OOo fields so the conversion to that format has to to use the text value.
JohnV
Volunteer
 
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: Question About Generating Reports

Postby nicegreetings » Wed Jun 11, 2008 9:04 am

Hi,

The merge can be completed two easy ways.
1) Use F4, select a record and click the Data to Fields icon found above the field names.


I tried this process, but I have the problem that the icons Data to Fields and Data to Text are greyed out.
Any idea on this?

I'm working with Windows XP, with the Version ooO2.41

best regards,

Marco
OOo 2.4.X on Ms Windows XP
nicegreetings
 
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

Re: Question About Generating Reports

Postby wurzel » Wed Jun 11, 2008 10:00 am

Hi,
Please start again by telling us exactly step by step what you are doing, otherwise it is difficult for us to say whether it is because you are doing something wrong or whether there is a problem with that particular combination of OOo, database, database driver, etc. Anything else would be just wild guessing on our part or at least making potentially false assumptions.

Alex
wurzel
Volunteer
 
Posts: 145
Joined: Sun Dec 09, 2007 10:39 am

Re: Question About Generating Reports

Postby nicegreetings » Wed Jun 11, 2008 10:53 am

Hi again,

thanks for your response.
I'm trying to manually generate a report. I'm working with a HSQL-DBE - already generated the hole Database with diffrent tables, forms and queries.
I started creating a report and I'm able to show diffrent text fields in my report showing some content from a table.
Pressing F4 shows me all my tables and queries I want to refer to.
Now I tried various methods to insert a hole coloum of a query. Drag and drop from the menu above and I'm able to insert the information as fields for example, but the information seems not to be dynamic. So if I change the data in the table, my report is not beeing updated, it seems to be static, showing some old stuff.
Because of that I wanted to try the way over the Icon "Data to fields", but that one is greyed out.
So what could be wrong there?

Thanks in advance

Marco
OOo 2.4.X on Ms Windows XP
nicegreetings
 
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

Re: Question About Generating Reports

Postby wurzel » Wed Jun 11, 2008 12:00 pm

nicegreetings wrote:Hi again,
I'm trying to manually generate a report.


Are you working with a Writer document ? A spreadsheet document ? A previously created Word document that you have imported ? Please specify.

nicegreetings wrote:I'm working with a HSQL-DBE - already generated the hole Database with diffrent tables, forms and queries.
I started creating a report and I'm able to show diffrent text fields in my report showing some content from a table.
Pressing F4 shows me all my tables and queries I want to refer to.
Now I tried various methods to insert a hole coloum of a query.


You can not insert a whole column of data in this way, at least not into a Writer document, because it needs to know how to proceed to the next tuple or data set of your query and it can't do this automatically (at least not to my knowledge). If you want to insert whole sets of data into a single document then you are better off using a Calc spreadsheet.

nicegreetings wrote: Drag and drop from the menu above and I'm able to insert the information as fields for example, but the information seems not to be dynamic. So if I change the data in the table, my report is not beeing updated, it seems to be static, showing some old stuff.


You did save your original document as a Writer template document (OTT extension) and not as a normal OpenOffice.org Writer document, didn't you ? What you describe is what happens when you save the original document containing the dragged fields as a normal OpenOffice.org Writer document. Also, the report you have created in this way is not a dynamic report, so the data won't be updated automatically in this scenario. You have to reselect and redo the mailmerge operation for the data to be updated.

Making dynamic reports on one's own from scratch with Writer documents is possible in OOo, but you need to use macro magic to do so, so that each time you open the document, it knows that it is supposed to initiate a connection to the data source, fetch an array of data, and then insert that data into your document. With Writer, you need to master cursor placement and Next record controls in order to be able to tell it to insert the Next record at the correc t place. Otherwise, your reports will only ever be static, that you will have to update manually (by redoing the mailmerge operation) each time you want to print them out. The same is true of the Label functionality (which is just another form of mailmerge).

Creating dynamic reports is best done with the Sun Report Builder add-on. This is an extension that you can install for free. It is also more flexible than the approach you have had so far.


Alex
wurzel
Volunteer
 
Posts: 145
Joined: Sun Dec 09, 2007 10:39 am

Re: Question About Generating Reports

Postby nicegreetings » Wed Jun 11, 2008 4:00 pm

Thanks for your efforts, but sorry, I couldn't follow you completely.

Are you working with a Writer document ? A spreadsheet document ? A previously created Word document that you have imported ? Please specify.


I thought that's clear, because I posted here: Board index ‹ Applications ‹ Base ‹ Reporting
I have an .odb, in there I have several tables, queries, forms and a report.
I created the report with the wizard, and now I want to add some data manually.
Is it possible to have the report in the wrong format in the databank?

You can not insert a whole column of data in this way, at least not into a Writer document, because it needs to know how to proceed to the next tuple or data set of your query and it can't do this automatically (at least not to my knowledge). If you want to insert whole sets of data into a single document then you are better off using a Calc spreadsheet.

How should that work with a spreadsheet?
What I want to do is a report which creates a technical documentation out of preconceived blocks. The users are able to choose these blocks with a check box and are able to modifiy these blocks in a form.

If I build a new report clicking on "Use Wizard to Create Report ...", I am able to choose one row of one query as fields in this report. Going one with the wizard brings me to the following entry in my report:
Benutzerfeld Sicherheitshinweise=Sicherheitshinweise (german, in engl. User field, Sicherheitshinweise is the name of the query)
Ut wisi enim ad minim veniam, quis nostrud exerci tation (wich seems to be a space for the data of the query.)

When I open the report by double click, the data is filled in correctly. I than have the data from one coloum of the query as a kind of list in my report.
That's exactly what I want to have. But unfortunaly I have no idea how to do that manually without the wizard. That's necessary, because I want to do that with the data from several queries all shown in one report.


Creating dynamic reports is best done with the Sun Report Builder add-on. This is an extension that you can install for free. It is also more flexible than the approach you have had so far.

Is there an easy to use manual for the report builder, because I have some start-problems how to use it?

Thanks

Marco
OOo 2.4.X on Ms Windows XP
nicegreetings
 
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

Re: Question About Generating Reports

Postby wurzel » Wed Jun 11, 2008 5:09 pm

[quote="nicegreetings"]

I thought that's clear, because I posted here: Board index ‹ Applications ‹ Base ‹ Reporting
I have an .odb, in there I have several tables, queries, forms and a report.
I created the report with the wizard, and now I want to add some data manually.
Is it possible to have the report in the wrong format in the databank?
[quote]

OK, now it is clearer to me what you have done. The problem is that there are several ways to create reports, and we started off by hinting that mailmerge might be the solution for you. What you have done is used the integrated Report Wizard to create a report. This wizard creates a document template which is stored somewhere on the system (by default in the OOo/user/templates directory I seem to recall), unless you specify otherwise. However, what you also get when the wizard complete is an ODT document that is also stored on your system, and the data in that is fixed (because it represents the merger of the template with the data from the db). If you want to add fields or modify the report by hand after using the wizard, then you have to edit the Writer document template that was created during execution of the wizard. Although this is doable, it is not for the fainthearted, because it involves tweaking the underlying properties (db connection properties and hidden fields of the template).

[quote="nicegreetings"]
How should that work with a spreadsheet?
[quote]

There are several ways. You can simply copy/paste the entire query (or part thereof) into an empty spreadsheet. Obviously, there is nothing dynamic about this way of doing things.

Alternatively, you can set up your spreadsheet to point to a database range (look at the datapilot functions for example). The problem I have with this method is that it is impossible to know in advance whether your data range, the size of which may vary over time, isn't going to exceed the page size, and more importantly, if you use text fields in your database, then if the text of the field exceeds the space attributed to it in your spreadsheet cell, the cell won't adjust its height and width automatically and so when you print your text appears truncated (although it isn't). The only way around this limitation that I know of is through macro programming.

[quote="nicegreetings"]
What I want to do is a report which creates a technical documentation out of preconceived blocks. The users are able to choose these blocks with a check box and are able to modifiy these blocks in a form.
[quote]

I have seen questions similar to this answered in the shape of "use sections in your Writer document" that you hide and reveal as a condition of either ticking the box or the content of the text field (or a boolean operator). In that case, though, most people use either the Label wizard or the mailmerge wizard.

[quote="nicegreetings"]
If I build a new report clicking on "Use Wizard to Create Report ...", I am able to choose one row of one query as fields in this report. Going one with the wizard brings me to the following entry in my report:
Benutzerfeld Sicherheitshinweise=Sicherheitshinweise (german, in engl. User field, Sicherheitshinweise is the name of the query)
Ut wisi enim ad minim veniam, quis nostrud exerci tation (wich seems to be a space for the data of the query.)

When I open the report by double click, the data is filled in correctly. I than have the data from one coloum of the query as a kind of list in my report.
That's exactly what I want to have. But unfortunaly I have no idea how to do that manually without the wizard. That's necessary, because I want to do that with the data from several queries all shown in one report.
[quote]

Like I said above, to do this requires that you tinker with the underlying template that was used to create the report, and creating sections. I haven't invested any time in getting this to work.

[quote="nicegreetings"]
Is there an easy to use manual for the report builder, because I have some start-problems how to use it?
[quote]

I don't know of a manual for using the SRB, a person on the French user list has written some documentation with an example database, but to my knowledge it is only available in French at the moment (from fr.openoffice.org). Perhaps the wiki contains some more pointers for you.

Alex
wurzel
Volunteer
 
Posts: 145
Joined: Sun Dec 09, 2007 10:39 am

Re: Question About Generating Reports

Postby JohnV » Wed Jun 11, 2008 5:57 pm

You can not insert a whole column of data in this way, at least not into a Writer document


Sorry but this is not true. Open a Writer document, press F4 and open your table or query. Select all records with the upper left gray box and click the Data to Text icon. Put the bullet in Fields, move the desired fields from left to right and organize them as desired. Tap Enter once or twice after the last field to separate records and click OK.

In prior versions of OOo this would generate errors but they can be cured by clicking the Data to Fields icon.

This does not generate a dynamic report but you can use the report again by selecting the desired records and clicking the Date to Fields icon. If you add more records you will have to do this again or copy and paste some of the current field sets below the current ones.
JohnV
Volunteer
 
Posts: 1585
Joined: Mon Oct 08, 2007 1:32 am
Location: Kentucky, USA

Re: Question About Generating Reports

Postby wurzel » Wed Jun 11, 2008 6:22 pm

The point is, the OP is trying to create a dynamic report and the data insertion technique you refer to does not allow that - as you point out, it converts the data to text. Additionally, you mention selecting all of the data by clicking the uppermost left corner of the data set. If you just try and select one column of data by dragging and dropping (which for most users is apparently the natural way to do things) then that technique doesn't work, it will insert a field instead. You can of course select multiple rows of data using this technique, and then decide on the columns from which you want to extract the data, but you always get a static text table as the result.


Alex
wurzel
Volunteer
 
Posts: 145
Joined: Sun Dec 09, 2007 10:39 am

Re: Question About Generating Reports

Postby nicegreetings » Wed Jun 11, 2008 10:15 pm

I can't or I don't want to believe that there's no possibility to create a dynamic report.
Isn't it a absolutly necessary thing to give a databank tool a right to exist.
I now, it's free - ok. But I developed a complex database in Base and
now I'm not able to create a usable report at the end.
I haven't thougt about it at the beginning because it's such a normal thing in access.

Because there's no comfortable way back or possibility to change to another program I
have some further questions:

1.) What's behind the wizard, where there is the possibility to create a dynamic report. But unfortunately not for more than on table/query?
Is there really no way to create exactly this manually or generate a single report for each table/query and copy the generated content all in one report?

2.) Could anyone provide a code to insert content from a query or table into a report with a macro?

3.) Could the sun report builder be a way out? My first tests say no, not yet.
OOo 2.4.X on Ms Windows XP
nicegreetings
 
Posts: 7
Joined: Mon Feb 04, 2008 2:11 pm

Re: Question About Generating Reports

Postby wurzel » Wed Jun 11, 2008 11:32 pm

nicegreetings wrote:I can't or I don't want to believe that there's no possibility to create a dynamic report.
Isn't it a absolutly necessary thing to give a databank tool a right to exist.
I now, it's free - ok. But I developed a complex database in Base and
now I'm not able to create a usable report at the end.


It is not impossible, just difficult, to build a customised dynamic report with OOo as it comes "out of the box", so to speak.

nicegreetings wrote:1.) What's behind the wizard, where there is the possibility to create a dynamic report. But unfortunately not for more than on table/query?
Is there really no way to create exactly this manually or generate a single report for each table/query and copy the generated content all in one report?


I might be a bit dim, but have you tried the mailmerge functionality, based on a Writer document template containing the fields you want in your report ?

The wizard uses java, it is coded in binary form, but you can always download the source and fiddle with the Java code if you know anything about it (which I don't).


nicegreetings wrote:2.) Could anyone provide a code to insert content from a query or table into a report with a macro?

3.) Could the sun report builder be a way out? My first tests say no, not yet.

[/quote]

From what I understood of it, yes, this allows the creation of dynamic reports (but I'm prepared to admit I'm wrong). How much functionality is accessible easily to the layman is, from my limited knowledge of it, hard to actually determine because there is so little detailed documentation on it within the OOo project. Like I said, look at the OpenOffice.org wiki under Base and see what's around (e.g. here : http://wiki.services.openoffice.org/wik ... rt_Builder, and here : http://wiki.services.openoffice.org/wik ... se#Reports


Try here too :
http://wiki.pentaho.com/display/Reporti ... t+Designer

You can also look elsewhere here in this forum or here :
http://www.oooforum.org/forum/viewtopic ... mic+report
http://www.oooforum.org/forum/viewtopic ... mic+report
http://www.oooforum.org/forum/viewtopic ... mic+report

Macro coding and dynamic reports using just Writer docs :
http://www.oooforum.org/forum/viewtopic.phtml?t=23190

HTH,

Alex
wurzel
Volunteer
 
Posts: 145
Joined: Sun Dec 09, 2007 10:39 am

Re: Question About Generating Reports

Postby Villeroy » Thu Jun 12, 2008 12:05 am

I use imported spreadsheet ranges as reports. Have a look at this one which can import any SELECT query with parameters from any registered source.
download/file.php?id=248
You can add additional calculated fields, page formats (landscape with headers/footers), you can create pivot tables from datasource, add charts and other things.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27899
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Question About Generating Reports

Postby seglea » Sun Dec 21, 2008 7:48 pm

nicegreetings, I don't know whether you ever managed to solve your problem. I think what you were trying to do is quite simple (probably simpler than some of the people trying to help you realised) - but Base can make it seem impossible.
The basic thing you wanted to do was to have a report that would be different depending on what data you selected in a query, right? That's what the Report Wizard calls a Dynamic report. It will work perfectly well if you base the Report on a Query (not a Table), and either by changing the data in the Tables underlying the Table, or by using a Parameter, select different data from the Query. BUT so far as I can tell, if you EDIT the Query, the Report stops being dynamic, and just freezes on whatever data the Query had fed it before the Edit - and the only way out of this is to rewrite the Report with the Wizard. At least, that's where I've got to. But there may be a way round this (or I may have misunderstood), so I have posted a question under the title "Getting a report to reflect edits in a query", and it might be worth your while to check whether that has collected any answers.
OOo 2.4.X on Ms Windows XP
seglea
 
Posts: 26
Joined: Tue Nov 18, 2008 1:04 am
Location: Exeter, UK

Re: Question About Generating Reports

Postby Villeroy » Sun Dec 21, 2008 7:57 pm

It may be just another bug in Base. Try this work-around: viewtopic.php?f=42&t=12719&p=59860#p59860
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04, no OpenOffice, LibreOffice 6.4
User avatar
Villeroy
Volunteer
 
Posts: 27899
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Reporting

Who is online

Users browsing this forum: No registered users and 1 guest