Betreff: Workspace Tips and Tricks: Rolling Reports with fluctuating rows requiring data breakdowns

[#1]

This blog post was inspired by the following question:

Q: Is it on the product roadmap to update Table Builder to act more like Ad Hoc Analysis?  Where the breakdown items for Days can be expanded to more items?  For example, if I'm running a monthly analysis with daily breakdown, I'd want to see more than the first 5 days of the month.

So, this is a problem that I'm sure many (if not most) of us have faced. Table Builder is good for somewhat small reports, but it is a little limited... For this reason, I tend to take more time building out my reports manually so that I can have a little more control.

So, let's start with report that uses a daily breakdown, and generally these types of reports will set to a default date range, such as last month. Of course, we'll all aware that months range from 28-31 days; but more than that, sometimes your consumers will want to see a longer time frame... so I'm going to show you how to build this out so that it can apply your breakdowns to the maximum expansion available (and ensure that as your report rolls, that your breakdowns for each row continue to work).

First, I am going to increase my date range to be larger than a 400 day range, and create a breakdown on Day, this should allow us to see the maximum 400 rows of data (if you don't see 400 rows, make sure to select that from the rows configuration.

Now, before I forget, I am going to change the settings on the Day dimension to "Breakdown by Position". For those that don't know, this setting allows you to choose if you want your breakdowns based on the value of the row or based on the position.

To better explain this, look at the first row above, "Aug 1, 2021". With the initial settings (breakdown by position unchecked), if I were to break this dimension down, then change my date range or change the sort order, IF Aug 1^st remains in the visible rows, the breakdown will be locked specifically to "Aug 1, 2021". However, when I change the breakdown setting, my breakdowns will be locked to the row position, no matter what value exists there. This will allow your report to roll, but keep your breakdowns relevant.

This setting can be added later, it is not necessary to do before your breakdown, but this is something you don't want to forget to do, so doing it sooner makes sure you don't lose your work.

Now, let's start working on our breakdowns. Select all 400 rows that are available to us, then drag in the dimension you would like for your next level breakdown:

  Pro Tip: You can break down multiple rows at once by selecting multiple rows and dragging your breakdown dimension onto any selected row. All the selected rows will create a breakdown. 

The first thing you will notice is that all breakdowns are set to a default "5 rows", if this is sufficient, then great, but if you need to see more rows, then we must do the next step:

  Warning: This is a little labor intensive

Unfortunately, the same multi-row selection trick does not work when setting the number of rows to show, so if you do need to show more than 5 rows, you will have to do this per row (i.e. 400 times)

  Pro Tip: Since the table will continually resize itself as you add more rows, if you start at the bottom and work your way upwards, as the tables retrieve and add the new rows slowly you can move onto the next item while it's loading, without your next row to update moving out of your visible work area.

Hopefully you only need two dimensions of breakdown, and not 3 or 4... if you do... unfortunately you will need to repeat the above steps for the next level of breakdowns. Don't forget, if your second level dimension isn't a "standard list" of values like your Marketing Channels, you will have to change the settings on your second level dimensions to also "Breakdown by Position".

Once you have all your breakdowns set as you need, you can now change your report to your rolling report period (last month, or last 30 full days, or last 3 full months, etc.) and pull in the metrics you want to use.

So yes, most of the work you did is now hidden, but the report will both roll to show the proper breakdowns month after month, AND if the report date range is expanded to show more than the expected range, it will work for the maximum rows.

Now, as the author of the report, you will know how the report is being shared. If this is being sent as a PDF with no options to change the date range, or it is being shared with the lowest permission levels (preventing users from changing the date range), you may not need to do all the work for 400 rows... choosing a 31 day month to build the report may be sufficient for your needs.

  Pro Tip: Know your audience, know the usage... adjust to suit the needs required

Now, you might be wondering how to use this for an initial dimension other than "Day"... and yes this is possible. You just need to know how many rows of data that you want the report to show (if the current month doesn't meet that, change the data range until you have enough). Use the same breakdown by position, builf out the report, then when you are done, set the rolling report range.

Happy Reporting!

[#2]

thanks for sharing @Jennifer_Dungan - you have great pro-tips in the article!