[From Wrike] Customize and Analyze: Crack the case with columnar fields

Hi Community,

My name is Herine, and this is my last post from the custom field series. Having discussed limitations, formulas, and “classic” solutions, I’d like to finally jump to adding custom fields as columns (dimensions) on New Storage and show you why this solution is so awesome and so often recommended by your account team and Support.

This post will be useful for you if your account uses New Storage, and:

• You want to build formulas with date-type custom fields.
• You need to add non-numeric custom fields as columns to your pivot.
• You have several charts built on different fields, and you want them to filter each other.
• “Old school” workarounds don’t really work for your use case.

Adding fields as dimensions/columns: what does it mean?

To overcome the limitations described in previous posts, we added another table to the data model.

This is why we’re calling them columns: the first column is “Folder/Project ID” (or “Task ID” for the table “Task Custom Field (Columns)”), and all its custom field values are stored in one row, each one in its own column.

How can it help my reporting?

In contrast to a standard data table, this one is more flexible: there are no generalized columns like “Task Custom Field Name” – each custom field has its separate column. With this structure, there’s no need to filter fields by names: fields can be added directly from the data browser without conflict.

I can also use several custom field filters at once:

Since there can be several custom field filters (as long as they are from the “Columns” section, like “Task Custom Field (Columns)”), pie charts built with columnar fields can work as filters for other widgets, even if those filters already have custom field filters.

There’s another advantage to columnar fields: they store blank values as N/A. For example, if you need to count how many tasks do not have a custom field populated, columnar fields will do it. This will work if at least one custom field is populated in the task.

Note: by default, the tables “Task Custom Field (Columns)” and “Folder/Project Custom Field (Columns)” are empty. Custom fields can only be added there as columns by Support. If you’re on the New Storage and need some custom fields added as columns, please, reach out to Wrike Support.

It will also help you to create formulas with date-type fields! Remember the post on calculated custom fields? It covered numeric values, but not date-type values. With columnar dates you can build formulas using Date & Time functions as quickly as with system date fields.
 

Why not add all custom fields as columns?

Columnar fields are easier to use, and if there are just a few custom fields in your account, you can request Wrike Support adda them to your account and enjoy Wrike Analyze to the fullest.

However, it’s important to keep in mind that there are separate data tables for each level: “Task Custom Field (Columns)” and “Folder/Project Custom Field (Columns)”. That means that if a field is applied to both levels, it will be added to the data model twice (once for each level). As a result, if we add 50 fields, that will be +100 rows in your data browser.

To avoid potential performance degradation, we do not currently add more than 200 custom fields to the database. Therefore, if there are a lot of custom fields used in an analytics report, it makes sense to add only non-numeric custom fields as columns. As described in my previous posts, numeric custom fields can be easily filtered by formulas, so I recommend giving space to the fields that do not have that luxury.

Field types and the recommended method

Multiselect

Another important thing to remember is that columnar fields store all the values in one cell. Similar to Table View: if there’s a multiselect custom field, its values will be listed in the same cell, and not separated by rows as in the standard data table.

While in pivots it looks convenient, it doesn’t look neat in charts.

Instead of custom field options, slices display the values of tasks: all the combinations that are used. It can be customized with formulas if needed, but the general recommendation for this kind of widget is to use the “old school” way.

Checkbox

As mentioned earlier, checkbox custom fields show true or false values, but be careful: Wrike Analyze will see “false” values only if they’ve been manually added. For example, if the field was checked and then manually unchecked, analytics reports will show that as “false”. However, if the field has always been blank, it will be considered a blank value, not a false value, and hence it won’t be shown in Analyze.

However, if the field is added as columns, it will store N/A values. This is extremely helpful with checkbox fields: if you want to report on false values, you can filter your widget to both “false” and “N/A”. It’s not possible with non-columnar checkbox fields.

Thanks for reading this post, I hope this series answered all your questions about custom fields in analytics!

Herine Community Team at Wrike Wrike Product Manager Aprenda sobre as funcionalidades e melhores práticas em Wrike com o nosso Treinamento Webinar Online

Herine Wrike Team member Aprenda sobre as funcionalidades e melhores práticas em Wrike com o nosso Treinamento Webinar Online