Thanks Martin. My current use case involves a report from a limited set of accounts -- no need to see accounts with zero position if I'm running the report without the WHERE restriction. If nothing else, then perhaps I could export to csv and write macros in a spreadsheet to build the report.
--John On Sunday, November 24, 2019 at 9:14:38 PM UTC-5, Martin Blais wrote: > > On Sun, Nov 24, 2019 at 10:01 AM John Rakestraw > <john.a....@gmail.com <javascript:>> wrote: > > > > Hi, Martin -- > > > > I'm using bean-query. Here's one example: > > > > bean-query /path/to/beancountfile "Select root(account, 3) as Account, > SUM(Convert(position, 'USD', date)) AS balance, currency as Total FROM OPEN > ON 2019-01-01 CLOSE ON 2019-11-01 WHERE account ~ > 'account1|account2|account3|account4'" > > > > This gives me exactly what I want, unless the report is limited to a > time period in which there are no transactions in one of the accounts. If > there's no transaction involving account 2, for example, then the report > lists only accounts 1, 3, and 4. I'd like all of them to be listed, with > account 2 showing a position of zero. > > Hmm. > Let me ask you a question: If you did not have a where clause to > filter for accounts 1, 2, 3 and 4, would you have liked to have seen a > 0 for account 5 if there were no transaction for it? > I'm not sure I have a good fix for that. > The SQL interpreter is pretty generic; I'd have to somehow design a > special case to cover accounts with no activity. > I'm not sure how. I don't have a great answer right now. > > > > Is that possible? bean-query -f csv -q > ~/beancountfiles/accounts/accounts.beancount "Select root(account, 3) as > Account, SUM(Convert(position, 'USD', date)) AS balance, currency as Total > Should I try bean-report? > > Nah, I'd love to remove those codes eventually, I don't like them, > it's ugly code. > I want to move everything to the query language as much as possible. > I wouldn't rely on it. They'll likely disappear in the next major > revision. > > Your use case is a legitimate one that I need to think about a bit more. > > > > > (Great software, by the way.) > > > > Thanks -- > > > > John > > > > On Saturday, November 23, 2019 at 10:46:36 PM UTC-5, Martin Blais wrote: > >> > >> How do you produce your reports? > >> bean-report? > >> or bean-query? > >> > >> On Tue, Nov 19, 2019 at 11:44 AM John Rakestraw > >> <john.a....@gmail.com> wrote: > >> > > >> > Hi, all -- > >> > > >> > New user here -- apologies if information I'm seeking is in the docs > -- I've searched both the docs and this list and can't find the answer. > >> > > >> > I'm attempting to construct a report of monthly spending in a set of > accounts, but I'd like the report to include an account in a particular > month even if there is no spending in that account. I've found that an > account is included (with the spending amount of zero) if there happened to > be actual transactions that total to zero (when I spent $40 that was > refunded in the same month). But if there's no activity, the account is not > included in the report. > >> > > >> > Is this possible? > >> > > >> > Thanks -- > >> > > >> > John > >> > > >> > -- > >> > You received this message because you are subscribed to the Google > Groups "Beancount" group. > >> > To unsubscribe from this group and stop receiving emails from it, > send an email to bean...@googlegroups.com. > >> > To view this discussion on the web visit > https://groups.google.com/d/msgid/beancount/1962423c-41a8-40f4-a5f5-9854006440d1%40googlegroups.com. > > > > > > -- > > You received this message because you are subscribed to the Google > Groups "Beancount" group. > > To unsubscribe from this group and stop receiving emails from it, send > an email to bean...@googlegroups.com <javascript:>. > > To view this discussion on the web visit > https://groups.google.com/d/msgid/beancount/ce2a539c-b430-40dc-9f9a-ca704eea7fa4%40googlegroups.com. > > > -- You received this message because you are subscribed to the Google Groups "Beancount" group. To unsubscribe from this group and stop receiving emails from it, send an email to beancount+unsubscr...@googlegroups.com. To view this discussion on the web visit https://groups.google.com/d/msgid/beancount/362d7e1a-abbd-4a26-89e4-3dd10d2581c7%40googlegroups.com.
