Docs: Category Rollup Report

Google Sheets Show & Tell
, , ,
1

Use our two category rollup reports to get a detailed, transaction-by-transaction view of your finances for a selected time period organized by type, group, and category— available in two report types:

  1. Category Rollup - use for line-item detail grouped by category across a specified period
  2. Budget Period Rollup - use for reporting against a Tiller-Money budget at the end of a month

Category Rollup and Budget Period Rollup reports present static views of your data. If you make changes to the transactions sheet (like recategorizing transactions or adding in manual transactions) after the report has already been generated, the report will not automatically update with those changes. You will need to re-generate the report from the Tiller Community Solutions add-on to update the report data.

Both reports organize your financial data based on the configuration settings you chose and then by type (income, expense, or transfer), group, category, and then the transaction details including date, description, amount, and note.

image
image4065×1530 506 KB

The Report Type dropdown (and Budget Period Rollup option) are only available if your spreadsheet includes a properly configured Tiller-Money budget driven off the Categories sheet. It is does not report on envelope budgeting spreadsheets.

Category Rollup report

Choose a period and quickly create a list of charges within that period organized by type, group, and category. The Category Rollup report is also great for itemized deduction reporting during tax time.

How to generate a Category Rollup report

  1. Launch or install the Tiller Community Solutions add-on
  2. Click on the “Tools” tab
  3. Choose “Create reports”
  4. Choose “Category Rollup Report”
  5. Choose “Category rollup” in the “Report Type” dropdown
  6. Configure the report for your needs
  7. Click Create Report

How to Configure the Category Rollup report

Transactions

Choose which transaction details should be included in the report based on the settings the report offers.

  • Include Hide from Reports categories: This option will include transaction details for any categories that are marked as “Hide” in the “Hide from reports” column on the Categories sheet.
  • Include uncategorized transactions: This option will include transaction details for any transactions that are uncategorized on the Transactions sheet and group them under an “Uncategorized” heading at the top.
  • Filter on tags: This option will include only transaction details for categories that are tagged as with the selected tag based on the “Tags” column on the Categories sheet. (Dropdown visible when there is a Tags column in the Categories sheet and one or more categories have been assigned tags.)

Time Period

Use the Period Preset drop down option under Time Period to choose a time period for which the report should pull transaction data. Use the start and end date fields to customize the date range to your liking.

image
image2758×1395 436 KB

Budget Period Rollup report

The Budget Period Rollup report, a variant of the Category Rollup Report, to run atop the Tiller Money Monthly- and Yearly-Budget templates. This report integrates the category-rollup report more tightly with your budget configuration in the Categories sheet by:

  • Showing only past & current periods configured in your Categories sheet
  • Reporting on on category budgets along side each category (with formula-driven rollups for groups and types)
  • Including conditional formatting for favorable & unfavorable categories
  • Including row grouping to better manage large data sets
  • Displaying a header row with budgeted, actual and net cashflow

How to generate a Budget Period Rollup report

  1. Launch or install the Tiller Community Solutions add-on
  2. Click on the “Tools” tab
  3. Choose “Create reports”
  4. Choose “Category Rollup Report”
  5. Choose “Budget period rollup” in the “Report Type” dropdown
  6. Configure the report for your needs
  7. Click Create Report

How to Configure the Budget Period Rollup report

The Budget Period Rollup report offers a few simple configuration options:

  • Budget Period: This dropdown shows past/completed budget periods (from your Categories sheet) up through the current month.
  • Sort By: You can choose to sort transaction line-items within each category by Amount, Date, Description or Note.

image
image600×750 51.1 KB

Troubleshooting

If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this template in the Google Sheets category.

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.

2

@heather Is there a way to get the transaction notes to show up in the category rollup? I noticed a “Note” field on the category rollup, but it doesn’t seem to pull the note from the line from the transactions. It would be helpful when reviewing the rollup, because I do make notes on some transactions

3

@rdy373,

I think that the column header in Transactions has to be just “Note” and it will pull it in vs plural “Notes” - it’s just pulling from a column called Note.

4

Finding this very useful, thank you!

Is there a way I can automatically e-mail myself a monthly report based on this sheet every two weeks?

5

Hi @galatic_incipient,

We don’t have that as an automated option right now.

If you’re comfortable in Google Sheets, you can try modifying the script documented in this blog post on the Tiller Money Blog to email you that specific sheet.

@jono wrote this up a couple years ago, and it’s very specific to a template that’s no longer available. We haven’t updated it simply because it was intended to be an experimental workflow for folks so you would have to tinker with it to get it to work for this category rollup report.

Otherwise, I’d recommend adding a feature request to have an option to email you a snapshot of a sheet via the Tiller Labs add-on.

Heather

6

Hi @galatic_incipient,
As @Heather mentioned, I did provide an example on how to email yourself a sheet in that post several years ago. That particular script would only work with a specific template but it shows the necessary Google Script code and process to send automatic emails.

As far as using it with the Category Rollup Report, there would need to be an additional step. Since this sheet needs to be manually generated and doesn’t automatically update, the script would also have to generate the report every two weeks and then email it. Google Scripts can certainly make something like that happen.

7

@heather - I’m getting an error message every time I try to generate a new report now since this function has moved to Tiller Labs add-on under Tools. It just says that a server error has occurred. I tried several times yesterday and again today. I’ve deleted prior category rollup reports to see if they were interfering for some reason. It’s one of my favorite reports. Any help or suggestions?

8

@max.coblentz, strange. I just tried it in Tiller Labs and it worked without issue. Are you trying to run it via Tiller Labs or the Tiller add-on (you should just being seeing a “this feature has moved” message if using the Tiller add-on)

Heather

9

@heather, I’m running it via Tiller Labs. I’ve even deleted past category roll up reports to see if they were interfering for some reason. Still giving me the same error message.

1 Like
10

That’s strange, @max.coblentz. I’m not able to reproduce this error either and I’m also not seeing anything suspicious in the logs.

Could you try two things:

  1. Unhide the sheet called Report Template: Category Rollup and delete it? (When you run the report generator, it will pull in a new version of this template.)
  2. Delete or rename a sheet named Category Rollup Report— if one exists. (The script should flag this and work around an existing sheet… but you can’t be too careful.)

Then re-run the report.

Hoping this helps.
Randy

11

@randy I tried that and am still getting the same result. I guess a work around would be just running a Profit and Loss report? But that doesn’t show all the transactions in each category which is what I liked. Any other suggestions?

12

I’m getting an error “Comparison method violates its general contract.” on this report. If I narrow the date range to a couple of weeks, it works. Perhaps a maximum number of transactions?

15

@jsstover, thanks for letting us know. I’ve pinged @randy on this one. We haven’t seen that error before as far as I know.

16

I saw that error in our logs, @jsstover, and implemented a fix a few days ago. I’m hoping it is resolved. Can you give it another try and let me know if it is resolved or recurs in your sheet?

Thanks,
Randy

18

Not being a sheets expert…an automatic collapse button for Category (Maybe also group) would be really handy to see a quick overview.
If not doable…is there a quick way to do this in Sheets without having to manually select ranges

19

Hi @stuart,

This is a great idea. There isn’t an automatic way to do this with the Category rollup report right now, but you can add groups to rows of cells. Just select a few rows and right click and then choose “Group rows x-x” and then it will give you a collapsing option.

20

It would be great if there was a checkbox on Labs side panel that allowed you to select “Always Overwrite Existing Category Rollup Report” so power users could avoid the pop up window.

1 Like
21

We’ve closed this topic for replies. If you have a question or need help first search the community to see if someone has already asked and if not click here to quickly post a question about this template in the Get Help > Workflows & Tools

Be sure to customize the title of your post with keywords about the issue or question so others can easily find the Q&A in search.

22

I don’t see the “Filter on Tags” option …

23

That dropdown only shows if you have a Tags column in your Categories sheet and there are categories with tags assigned, @walter.ren.