[Tex/LaTex] Database style reports in LaTeX

automationdatabaseformatting

I see plenty of questions around that demonstrate that csv files or database tables can be imported and formatted (based on templates and existing knowledge of the data structure) using LaTeX.

My question: Is LaTeX a practical (or even possible) tool to import data from database and output it as a report? When I say report, I am picturing similar to the way a Microsoft access database report can be constructed based on the individual fields and sub reports (to generate separation and iteration between rows of the tables) and not a complete, rigidly produced table?

If anyone knows of any packages that may be useful, I will poke around with any MWE or package documentation I can find and update my question.

Preliminary research I welcome comments or feedback on:

  • http://ctan.org/topic/db-access (but no documentation that I have found)
  • SWEAVE
  • recommendations for scripts that parse the database and code themselves a tex file

I see this application permitting me to prepare templates such that would create:

  • conference programs – see example figure below
  • data and graphic reporting
  • Aesthetically professional CV
  • plain text unformatted version of a CV (for easy online type submissions which are common these days)

Conference program example produced using nested MS access reports and sub reports:

  • Header and footer formatting
  • sequential by time events
  • formatting common to event classification
    enter image description here

Best Answer

I've been learning how to write scripts to generate Latex reports and tables from data sources. Instead of using Latex directly to do this, the script written in your favorite language, which must have support multi-line raw strings, generates the Latex code on the fly as it is reading the data from external sources (files, XML, etc...).

The script formats the fields, builds the Latex tables, and does any computation needed in between, inserts these fields into the Latex stream and writes the final Latex code to external files.

The latex file is then compiled to pdf and HTML. Here is a small diagram of one report I've done this way (this happened to be a computer algebra related, where the input to the script that generates the reports and tables are plain text files with comma delimited fields). Some of these fields contain equations in Latex also generated by the computer algebra programs as result of solving integration problems.

There are over 70 PDF files generated this way, some over few thousands pages long, all from the same program. The program loops over the input, building the tables, doing statistics, and writing the Latex.

Mathematica graphics

The result can be seen here. These pages are all done in Latex, compiled to HTML using tex4ht, but the Latex was generated from a program. I found it much more flexible doing it this way, than using Latex itself for the logic and the processing.

There are few languages that supports multi-line raw strings well (sometimes called HERE-DOCUMENT). These are: Python and C++11, Ruby and Perl. (Perl 6 will be out this year!)

The need for multi-line raw string is important, it allows one to write the Latex code in the script, as freely as you would do in a Latex editor.

The only difference, is that is it much easier to do the logic and the programming, the database interface and any numerical computation in these languages than in Latex itself, and on the fly using this method.

Here is a typical python script that generates a Latex file with a formulated table:

 import os
  os.chdir("/home/me/data")
  my_file = open("py_latex.tex", 'w')      
  num = 6      
  x=r"""
  \documentclass{article}
  \begin{document}

  \begin{tabular}{|l|l|l|l|l|l|l|l|l|}\hline
  """
  for i in range(1,num+1):
    for j in range(1,num+1):
      x=x+'$'+str(i)+' \\times '+str(j)+' = '+str(i*j)+'$'
      if j<num :
          x=x+'&'
      else:
          x=x+'\\\\ \n'

  x=x+r"""\hline\end{tabular}
  \end{document}"""

  my_file.write(x)
  my_file.close()

When compiling the Latex file generated, the table is

Mathematica graphics

This link has small examples using Ruby, Perl, C++-11 and Python for generating small Latex files. If the raw data is in XML, once it is loaded (using XML API library, which those language all have one), then the same method can be applied as above.

Another option is to use LuaLatex, and use Lua code to do these things. But I found the interface between Lua and Latex a little harder to manage. I found it easier to just use a script and generate all the Latex code on the fly directly.

May be this gives you some ideas any way. This method might not work for everyone. It definitely needs time to get used to it.

Related Question