[libreoffice-users] Re: Turning spreadsheet data into legible report?
Thanks everyone for the feedback. As I learn more about spreadsheets, I thought merging all the years together into a single sheet and filtering data so that the wordprocessor would only see a subset of it would solve the issue... but it's not such a great idea since some students will attend more than one year, so they'll end up having more than one document in their name :-/ I guess it's an illustration that using a spreasheet as poorman's database only works for basic, one-dimensional needs. -- View this message in context: http://nabble.documentfoundation.org/Turning-spreadsheet-data-into-legible-report-tp4102146p4102293.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Turning spreadsheet data into legible report?
Problem solved: Indeed, putting all the data into a single worksheet works OK. The only drawback is empty documents for kids who didn't attend a given year, but this could be avoided by adding some conditional loop in the Word mail merge document to avoid printing empty sections (eg. If Trimester1-2013 is empty, don't display list). Thanks all. -- View this message in context: http://nabble.documentfoundation.org/Turning-spreadsheet-data-into-legible-report-tp4102146p4102306.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Turning spreadsheet data into legible report?
Sorry about that. The spreadsheet 1) has too many columns to fit on a document, even in Landscape mode (she wants Portrait), and 2) data are spread on multiple sheets, one by year. She needs to print the data thusly: = Last Name: Doe First Name : Jane Date of Birth: 12/12/2000 Year 2012-2013 Trimester 1 Col1 | Col2 | Col3 Trimester 2 Col1 | Col2 | Col3 ... Year 2013-2014 Trimester 1 Col1 | Col2 | Col3 Trimester 2 Col1 | Col2 | Col3 = So she needs some way to turn the spreadsheet into the above *while keeping data in different tabs* (one per year) since the list of students is different every year. Ideally, it should be doable with a spreadhseet (Excel or Calc) and some programming-free tool, but Word can't handle more than one tab in a spreadsheet. Like I said, she can't work with a database. Incidently, this problem is very common since a lot of users rely on Excel as poorman's database. I wonder how they all manage once they hit this type of issue. Thank you. -- View this message in context: http://nabble.documentfoundation.org/Turning-spreadsheet-data-into-legible-report-tp4102146p4102153.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Turning spreadsheet data into legible report?
Here's a better illustration of what I mean: http://postimg.org/image/hw6kh9vx1/ -- View this message in context: http://nabble.documentfoundation.org/Turning-spreadsheet-data-into-legible-report-tp4102146p4102157.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
[libreoffice-users] Re: Turning spreadsheet data into legible report?
Le 19/03/2014 15:24, Gilles a écrit : Hi, In theory, a LibreOffice Database report should be able to do something like that, but : - if you bind a Calc or Excel file, you will not be able to write/edit any of the data in the bound Calc or Excel file, as these become read-only - in order to update the sheets, you would have to open them up separately in Calc, do your editing/updates, etc, and then re-open the ODB file; - binding Calc or Excel files as text file data sources within an ODB means you are limited to the column headers for your field names - anything else is just a value (text string, date, integer, etc); - if you import the data to an ODB file, via the data import wizard, you will have more flexibility in what you do with that data, but you will lose the direct link to the Excel/Calc file, so changes you make to the latter will not be reflected in the ODB file, until you reimport the data into your Base file - this might be too much of a hassle for most people. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Turning spreadsheet data into legible report?
On 03/19/2014 10:24 AM, Gilles wrote: Here's a better illustration of what I mean: http://postimg.org/image/hw6kh9vx1/ -- View this message in context: http://nabble.documentfoundation.org/Turning-spreadsheet-data-into-legible-report-tp4102146p4102157.html Sent from the Users mailing list archive at Nabble.com. Gilles, A non programming way is to manually generate a new sheet for each student which is a royal pain. I suspect this step will be very time consuming. Then use mail merge to link to each student's page and generate individual reports. I can not think of a tool that would work without some modification either to the data or the tool to do what you want. I suspect what most people do, is just bite the bullet and do it manually. Any nice solution requires using a database and possibly being able to write a script to automate the task which is well beyond the skills of most people. One potentially nasty problem is duplicate student names such as two Mary Smiths or Tom Jones and the program can easily differentiate between the two people. I did see a id column which has a unique id for each student such as msmith000, msmith001. This would require someone to prep that data for any automated tool. If you could transfer the data to Base, then the report builder tools might generate the reports automatically. -- Jay Lozier jsloz...@gmail.com -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Turning spreadsheet data into legible report?
How about using a single sheet for the data, and add an extra column for year? Something like: Year Last NameFirst Name... 2012-2013Doe John ... 2013-2014Doe Jane ... Then mail merge on that. I don't know about Word, but in LO Writer you can set filters during the mail merge, e.g. if you only want reports for records where Year is 2013-2014. Where you select the data source in the mail merge wizard, click Select Different Address List, select the database, and click Filter... to set conditions. Sorting on the Year column would group all the entries for each year together in the spreadsheet. Mark. Giles wrote: Sorry about that. The spreadsheet 1) has too many columns to fit on a document, even in Landscape mode (she wants Portrait), and 2) data are spread on multiple sheets, one by year. She needs to print the data thusly: = Last Name: Doe First Name : Jane Date of Birth: 12/12/2000 Year 2012-2013 Trimester 1 Col1 | Col2 | Col3 Trimester 2 Col1 | Col2 | Col3 ... Year 2013-2014 Trimester 1 Col1 | Col2 | Col3 Trimester 2 Col1 | Col2 | Col3 = So she needs some way to turn the spreadsheet into the above *while keeping data in different tabs* (one per year) since the list of students is different every year. Ideally, it should be doable with a spreadhseet (Excel or Calc) and some programming-free tool, but Word can't handle more than one tab in a spreadsheet. Like I said, she can't work with a database. Incidently, this problem is very common since a lot of users rely on Excel as poorman's database. I wonder how they all manage once they hit this type of issue. Thank you. -- View this message in context: http://nabble.documentfoundation.org/Turning-spreadsheet-data-into-legible-report-tp4102146p4102153.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Turning spreadsheet data into legible report?
Hi :) Would the optimal thing to do be to import that Excel tables into Base and then export into an external back-end? Then enter data using some Form in Base. For the reporting set-up a Report in Writer or Calc? It sounds like it would be a lot of hassle but possibly would end-up with something far more elegant? ie easier for the people who just want to see the lists (the reports). About once per year it would need someone to go into the Forms to add new students and delete old ones but most of the time people would be just viewing the reports in a fairly familiar program wouldn't they? Regards from Tom :) On 19 March 2014 15:45, Alex Thurgood alex.thurg...@gmail.com wrote: Le 19/03/2014 15:24, Gilles a écrit : Hi, In theory, a LibreOffice Database report should be able to do something like that, but : - if you bind a Calc or Excel file, you will not be able to write/edit any of the data in the bound Calc or Excel file, as these become read-only - in order to update the sheets, you would have to open them up separately in Calc, do your editing/updates, etc, and then re-open the ODB file; - binding Calc or Excel files as text file data sources within an ODB means you are limited to the column headers for your field names - anything else is just a value (text string, date, integer, etc); - if you import the data to an ODB file, via the data import wizard, you will have more flexibility in what you do with that data, but you will lose the direct link to the Excel/Calc file, so changes you make to the latter will not be reflected in the ODB file, until you reimport the data into your Base file - this might be too much of a hassle for most people. Alex -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
Re: [libreoffice-users] Re: Turning spreadsheet data into legible report?
On 2014-03-20 02:55, Gilles wrote: Sorry about that. The spreadsheet 1) has too many columns to fit on a document, even in Landscape mode (she wants Portrait), and 2) data are spread on multiple sheets, one by year. She needs to print the data thusly: = Last Name: Doe First Name : Jane Date of Birth: 12/12/2000 Year 2012-2013 Trimester 1 Col1 | Col2 | Col3 Trimester 2 Col1 | Col2 | Col3 ... Year 2013-2014 Trimester 1 Col1 | Col2 | Col3 Trimester 2 Col1 | Col2 | Col3 = So she needs some way to turn the spreadsheet into the above *while keeping data in different tabs* (one per year) since the list of students is different every year. Ideally, it should be doable with a spreadhseet (Excel or Calc) and some programming-free tool, but Word can't handle more than one tab in a spreadsheet. Like I said, she can't work with a database. Incidently, this problem is very common since a lot of users rely on Excel as poorman's database. I wonder how they all manage once they hit this type of issue. Thank you. Hi. I assume also that students will come and go, some could be in 2013-2014 but not in 2012-2013 or 2014-2015 and may not even have data for all trimesters. What would you start at the year the student first appeared or have blank preceding years. Steve -- View this message in context: http://nabble.documentfoundation.org/Turning-spreadsheet-data-into-legible-report-tp4102146p4102153.html Sent from the Users mailing list archive at Nabble.com. -- To unsubscribe e-mail to: users+unsubscr...@global.libreoffice.org Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/ Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette List archive: http://listarchives.libreoffice.org/global/users/ All messages sent to this list will be publicly archived and cannot be deleted
