Turning spreadsheet data into legible report?

users
#1

Hello

A friend is a school teacher and needs to publish a report from the data she
keeps in an Excel document. In addition, she only has access to Word/Excel,
while Access is not available (she doesn't know databases, anyway.)

I originally thought Word's mail merge would do, but without programming, it
only supports reading data from a single sheet, while data is kept in
multiple sheets (one by year). Here's a screenshot of a mock document I
re-created in LO:
http://postimg.org/image/v2au21zsh/

Unless someone knows of a good, programming-free, Excel-based solution, it
looks like the right way to solve this problem is to stop using Excel as
poor man's database, and write a real database application with tables,
relations, input forms and report output... but she obviously doesn't have
the skills, doesn't have internal resources available for help, and I can't
spend time on this.

I know it's akward to ask this in an LO mailing-list, but can someone
experimented confirm that there's no MS Office-based solution (either
internal or third-party), and that moving to LibreOffice Base is the way to
go?

Thank you.

#2

The real question is what sort of report she needs. It's all good and
well showing us a screenshot of a document with two sheets, but without
knowing what you want to do with that data, there's really no advice we
can give.

#3

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:

#4

Here's a better illustration of what I mean:

http://postimg.org/image/hw6kh9vx1/

#5

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

#6

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.

#7

How about using a single sheet for the data, and add an extra column for year? Something like:
   Year Last Name First Name ...
   2012-2013 Doe John ...
   2013-2014 Doe 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:

#8

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

#9

Hi :slight_smile:
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 :slight_smile:

#10

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.

#11

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.