CSV report output does not represent account tree structure

[benwebber]

I'm passing hledger's CSV reports through pandoc/LaTeX to generate attractive PDF reports.

I like how the tree view works in the text reports:

hledger bs -f sample.journal --tree --no-elide

Balance Sheet 2008-12-31

             || 2008-12-31 
=============++============
 Assets      ||            
-------------++------------
 assets      ||        $-1 
   bank      ||         $1 
     saving  ||         $1 
   cash      ||        $-2 
-------------++------------
             ||        $-1 
=============++============
 Liabilities ||            
-------------++------------
 liabilities ||        $-1 
   debts     ||        $-1 
-------------++------------
             ||        $-1 
=============++============
 Net:        ||          0 

However, the CSV output does not encode the tree structure:

hledger bs -f sample.journal --tree --no-elide -O csv

"Balance Sheet 2008-12-31",""
"Account","2008"
"Assets",""
"assets","$-1"
"bank","$1"
"saving","$1"
"cash","$-2"
"Total:","$-1"
"Liabilities",""
"liabilities","$-1"
"debts","$-1"
"Total:","$-1"
"Net:","0"

In the example above, one can't determine that assets:cash and assets:bank:saving belong to assets without prior knowledge of the account names and more complex parsing rules. It might be possible to work around the issue with aliases, but I haven't tested that yet.

I can think of two ways to encode the tree directly:

1. Indent each account name with a number of spaces, as we do in the text output. For example:

   "assets","$-1"
   "  bank","$1"
   "    saving","$1"
   "  cash","$-2"
   

2. Add another column representing the depth.

Technically, either would be a breaking change. However, given that there is no difference between the tree output and the normal output, there is no real reason to generate a CSV report with --tree right now. I can't imagine either change would impact many users.

[simonmichael]

That's odd, I thought we had a numeric "indent" column, perhaps in the past, but I don't see it.

The balance command at least provides the full account names. balancesheet seems to have changed (regressed?) in 1.20.3, showing only the leaf names:

$ hledger-1.20.2 -f examples/sample.journal bs --tree --no-elide -O csv
"Balance Sheet 2008-12-31",""
"Account","2008"
"Assets",""
"assets","$-1"
"assets:bank","$1"
"assets:bank:saving","$1"
"assets:cash","$-2"
"Total:","$-1"
"Liabilities",""
"liabilities","$-1"
"liabilities:debts","$-1"
"Total:","$-1"
"Net:","0"
$ hledger-1.20.3 -f examples/sample.journal bs --tree --no-elide -O csv
"Balance Sheet 2008-12-31",""
"Account","2008"
"Assets",""
"assets","$-1"
"bank","$1"
"saving","$1"
"cash","$-2"
"Total:","$-1"
"Liabilities",""
"liabilities","$-1"
"debts","$-1"
"Total:","$-1"
"Net:","0"

[benwebber]

Interesting. The regression seems to affect bs, bse, and is.

It would also be easy to detect how deep each leaf is if the account column contained the fully qualified account names. That is a better solution than indenting the account names with spaces.

[simonmichael]

It sounds like we just need to fix the 1.20.3 regression then.

[Xitian9]

Git bisect says this broke at this commit: 334c627.

[simonmichael]

@Xitian9: sorry but as I think about it more: Aren't we in effect making --tree have no effect on CSV output ? Is that fine, or should we adjust CSV output based on --tree, since we do this with some other report output options, and it would be easier to consume for someone trying to generate a tree-formatted report ? If we think that's worth doing, some more notes:

• we could add a "depth" column (1-based, same as --depth; it would also be present in list mode, for a stable schema)
• we should decide whether to mimic the terminal output's boring parent eliding
• again for ease of consumption and report formatting, should we always include both full account name and leaf name ?

I do believe the CSV included these things at one time (yes, dropped by me in 2018: de5d22a). I simplified it since nobody was using the extra columns and it seemed more useful to have a simple CSV that you could just drag into a spreadsheet and have it make sense right away. So these goals are a bit in conflict (simple human-readable CSV, rich formattable CSV).

[Xitian9]

Indeed, the behaviour just prior to this breaking was to basically ignore --tree for csv output. I don't have a strong opinion on which option is better. To me a csv file screams ‘independent of row order’, which is definitely not the case for your proposed format, but also we already violate that for balancesheet output anyway (it matters where those report subheaders go). Including an extra ‘full account name’ column as you propose would also solve this.

My weak opinion is that csv output should force --flat mode. Maybe we can make this explicit by throwing an error if you try to call both together, or maybe we can just silently ignore it as we do now. But I'll leave the decision up to others.

[simonmichael]

"CSV forces list (flat) mode" is the quickest and easiest way to get things on a clear footing again, so I'd be fine with this. Ideally we'd document it somewhere. There is a related problem: I haven't found a great place to document CSV output or output formats generally. Currently I mention them briefly at the end of each command's intro text, before the first heading. This is ok for a short mention but if we have to go into detail they should get their own heading. I have avoided this because it seems like the same heading would recur for each command, which causes unstable non-mnemonic urls.

[simonmichael]

PS and now I wonder how `json`, `html` and other future formats do/should handle these things. But I don't want to get sidetracked.

[simonmichael]

For now I'll merge this and add a note to the five affected commands.