Markdown Reporter

Write your reports with Markdown
Let your database convert Markdown code blocks containing SQL queries to CSV data or Markdown tables
Let Pandoc convert your Markdown reports to HTML or high quality LaTeX PDF's with vector based inline charts generated by Pythons matplotlib on your CSV data

Three Main Components

A Oracle PL/SQL Package for preprocessing your data and communicating with the printserver
A Node.js based printserver, which is in fact a web based remote shell for the format converter Pandoc
A Pandoc filter written in Python, which intercepts the converting process and generates the charts based on the preprocessed CSV data

Currently supported is only the Oracle database, but the PL/SQL logic is very small and can be adapted to other database systems - contributors are welcome to do this adaption or to help by improving the Python filter to support more chart types.

How To Get Started

You need to install the following Open Source Software:

Pandoc & LaTeX
Node.js
Python (3.x)
- Upgrade packet manager pip: pip3 install -U pip (Linux/OS X) or python -m pip install -U pip (Windows)
- pip3 install matplotlib
- pip3 install pandas
- pip3 install seaborn
- pip3 install jupyter

Under windows you can get errors by installing seaborn or using numpy. These errors are normally because of missing compilers. You can find precompiled versions of the needed packages here - please choose the right ones for your Python and Windows version (32/64 bit): scipy, numpy. You can install then the files with this call: pip install localDownloadedFileName

If you are behind a proxy, then you can use pip3 install --proxy=yourProxyURL packageName. For the following Node.js installation you can also set the proxy in this way: npm config set proxy yourProxyURL and/or npm config set https-proxy yourHttpsProxyURL

Download and unpack Markdown Reporter
Call in the root directory (containing package.json) npm install
Then start the printserver by calling node app.js
In your browser call http://localhost:3000, play around with the Pandoc example form and check if everything is working like expected
As the last step install in your database the Markdown Reporter helper package (located in the subdirectory oracle_plsql) and configure in the body the printserver URL - don't forget to setup a network ACL for your new printserver
Optional you can install the APEX demo application located in the subdirectory oracle_apex, which has the APEX Mardown plugin preinstalled

Now it is time to check, if your system is working correct. It is strongly recommended to test your report functionality always locally first, especially the PDF format. If you use some extra LaTeX functionality then it could be, your LaTeX distribution wants to install the needed packages. It is also normal, that LaTeX needs more time to render on the first run, because it maybe needs to compile used fonts or packages. The same applies to Python by compiling machine specific code on the first run.

The directory structure of the Markdown Reporter:

|--data
   |--dev
      convert.bat
      convert.portable.bat
      document.md
   |--prod
      convert.bat
   |--templates
   |--userprofile
|--docs
   markdown-reporter.ipynb
|--node_modules
|--oracle_apex
   demo_app.sql
|--oracle_plsql
   markdown_reporter.pks
   markdown_reporter_body.pkb
|--pandoc_filter
   pandocFilterMarkdownReporter.py
   pandocfiltersFileBased.py

.gitignore
app.js
app-example-form.html
app-index.html
package.json
README.md

In data/dev you will develop and test your shell-command, which is called later on from Node.js. The shipped convert.bat can be the starting point for your customizing. If you are done you copy it over to the prod folder. The app.js is developed to run OS independend, but currently only tested under Windows. Thats is also the reason why currently a shell command for Linux/Mac is missing. If you plan to run under Linux/Mac, you have to modify the app.js and replace then the convert.bat with convert.sh or whatever you call your shell command.

The Pandoc option --data-dir is set to the data directory. You can put your custom templates in the subdirectory data/templates. Pandoc is searching there for the default templates. See also the pandoc readme

Node is creating for each conversion a temporary folder under the data directory. If somethings is going wrong during the conversion, the folder is not deleted - you are able to go into this folder and analyze the problem. The originally used shell command and options are prepended to the command.bat in the temporary folder.

If you have ideas for the Node.js backend or the conversion process please let me know and become a contributor to this project :-)

Be patient - the very first run can take some time, because Python is compiling the scripts and LaTeX needs also some time to cache the needed fonts. It is strongly recommended to test everything locally first with a shell command. If your system is working locally then it is time to check it over a HTTP connection from within your database:

SELECT httpuritype('http://yourHost:yourPort/pandoc').getclob() FROM dual;

Now try to generate your first report by executing this example Query in a SQL tool of your choice:

SELECT markdown_reporter.convert_document(p_format   => 'pdf' -- html, pdf, docx (with png's only)
                                         ,p_markdown => markdown_reporter.preprocess_data(p_markdown => q'[
---
title: Reporting Differently, Thank Markdown - Demo Report
author: Ottmar Gobrecht
date: 2016-11-05
lang: en
papersize: A4
geometry: top=2cm, bottom=2cm, left=2cm, right=2cm
fontsize: 11pt
documentclass: article
classoption: twocolumn
links-as-notes: true
---

*Some detailed explanation for your report.*

Lorem ipsum dolor sit amet, consetetur sadipscing elitr, sed diam nonumy eirmod tempor invidunt ut labore et dolore magna aliquyam erat, sed diam voluptua. At vero eos et accusam et justo duo dolores et ea rebum. Stet clita kasd gubergren, no sea takimata sanctus est Lorem ipsum dolor sit amet...

``` { .sql .chart .line caption="Population Development - New York, Rio, Tokio"}
SELECT 1940    AS "Population Development"
	 , 7454995 AS "New York"
	 , 1759277 AS "Rio"
	 , 6778804 AS "Tokio"           FROM dual UNION ALL
SELECT 1950,7891957,2375280,5385071 FROM dual UNION ALL
SELECT 1960,7781984,3300431,8310027 FROM dual UNION ALL
SELECT 1970,7895563,4251918,8840942 FROM dual UNION ALL
SELECT 1980,7071639,5090723,8351893 FROM dual UNION ALL
SELECT 1990,7322564,5480768,8163573 FROM dual UNION ALL
SELECT 2000,8008278,5857904,8134688 FROM dual UNION ALL
SELECT 2010,8175133,6320446,8980768 FROM dual;
```
]'))
  FROM dual;

You can see in this example, that Pandoc is able to read meta data from a YAML header. You can also place in this YAML header so called header-includes for LaTeX to avoid modifying Pandoc's LaTeX template.

Chart Development

Currently eight chart types are delivered with Markdown Reporter: line, area, area_stacked, bar, bar_stacked, barh (horizontal), barh_horizontal and pie.

For chart development it is recommended to install all software local on your PC and start then the Jupyter notebook server by calling the shell command jupyter notebook. In the Markdown Reporter subdirectory docs you will find an example notebook (markdown-reporter.ipynb) - you can use this as an starting point for your customizing and new chart types.

If you have created some new chart logic then it is time to modify the Pandoc filter pandoc_filter/pandocFilterMarkdownReporter.py and also to become a contributor for this project :-)

Changelog

0.2.0 (2016-11-12)

New chart types: line, area, area_stacked, bar_stacked, barh_stacked
More chart options: See demo report and demo report SQL version
Images in HTML format have now default styles - IE scales now correct and images have always a maximum width of 100%

0.1.0 (2016-10-22)

First public version