Dynamic Ranges Overview

Dynamic Range is a powerful feature. When you have a range using a DR.GET function, Datarails will update the range and its formulas whenever the underlying data changes. This means that Datarails dynamically restructures your reports according to the live data.

Unlock the secrets of Datarails! Dive into our Online University and transform your learning journey today!

 

Example: You want the GL entries for a list of assets. You will create the formula using relative cell references for a couple of rows. When you click Dynamic Range, Datarails first creates a list of unique values (in this case, assets), then it copies and pastes the formulas from the first row across the whole range you have set. The result is a dynamic list of unique values (that can grow or contract) in the first column and a series of dynamic formulas that populate all the other cells.

dynamic_range_1.1.4_how_it_works.png

Once the file is submitted (saved back to the database), the next time you open the file and click Refresh, the cells will be updated with the latest data even if the number of assets has changed.

Getting Started

Step 1: Set Up the Template

dynamic_range_1.1.4_setup_template_starter_rows.png

  1. Set up the template with the layout you want.

  2. Create dummy text for where the unique list will go (#1).

  3. Create one formula using a DR.GET function. Use relative cell references so you can copy the formula to other columns and rows (#2).

  4. Copy the formula to all the cells for the first 2 rows. This is best practice. It gives you a chance to check that the relative cell references are correct.

  5. The template is now ready to set up the dynamic references.

Step 2: Set Up the Dynamic References

dynamic_range_1.1.4_gif_1.gif

  1. From the Datarails ribbon select Dynamic Range > Add. A new pane opens to the right.
    • Dynamic Range: Name the range. Click OK.
    • List Range: Highlight the starter text for the dynamic list and click the down arrow. The range shows in the right pane.
    • Formula Range: Highlight the cells with the formulas and click the down arrow. The range shows in the right pane.
    • Field Selector: Click Edit. This opens the Add Dynamic Range screen.
      dynamic_range_1.1.4_add_range_filter.png
  2. Select the field for the list of unique values and drag it to the Display Fields pane.
  3. Add one or more filters to limit the unique values as needed. For example, you might want only a selection of accounts in this report.
  4. Click Save.

Step 3: Create the Dynamic Range

  1. Next review the settings in the Dynamic Range pane.
  2. If all is OK, click Done. An update screen opens.
    dynamic_range_1.1.4_gif_2.gif
  3. The screen confirms the range and tells you how many rows will be added.
    • You can expand the width of each column to see more text as needed.
    • Don’t show again: Activate the checkbox if you don’t want the update screen in future. You can change your mind later by going to Help > Settings.
  4. If all looks good, click OK.
  5. Now Datarails creates the dynamic range adding the dynamic list and populating the cells with the DR.GET formulas.
  6. Click Refresh.
  7. When you’re done, click Submit to save the file with the dynamic range/s to the database.dynamic_range_1.1.4_range_complete_1_.png

dynamic_range_1.1.4_gif_3.gif

If the dynamic range is generated on the server, the function is automatic without any user input.

Post-Set Up

  1. After saving, Datarails will bring all the unique values from the underlying data and ask you to approve the changes that will be made to the report.
    dynamic_range_1.1.4_gif_4.gif

  2. After you click OK, the new items will be added, and the formula will cover all cells in the formula range.

  3. Each time you click Refresh, the report will update automatically. If there are new items or the underlying data has changed, the report will reflect those changes.

Best Practices, Use Cases & Tips

Best Practices

  1. By disabling the New Items notification. This means that your report will be updated automatically at each refresh.
  2. Make the total column to be part of the formula range.
  3. Your initial range should include at least two rows.

Use Cases

When I have a new line item and I want to add it to my report, I need to do this myself. Then, I need to fill the formulas down to update the report.

Let’s assume I’m producing a sales revenue forecast for the rest of the year, and a new sales rep has just joined the team. Her sales won’t be reflected in the forecast, unless I remember to add her to my report. Excel won’t tell me that a new line item has been added to my list. That’s something that I must remember to do myself. Another item for my ‘must-do’ list! Introducing Dynamic Ranges.

  • With Dynamic Ranges, you don’t need to worry about the line items. When a new item is added to the database, it will automatically be added to reports.
  • All you need to do is define the range as a Dynamic Range.
  • Do it once, and Datarails will do the rest each time you click Refresh.

Forecasting

Dynamic Ranges support forecast use cases. This is particularly helpful for those customers currently struggling with updating their monthly forecast with all the new items in their business. 

  • Only formulas (Excel & DR) will be copied. In the case of hard-coded numbers, the new items added to the range will be blanks to allow the user to insert comments.

  • The dynamic range ID in the dynamic range menu is included to help the user reference dynamic ranges directly in their formulas.

Can be used for a collection process where more than one party is participating in the data input.

Tips

Edit Filters

Dynamic ranges are truly dynamic. You can change your mind, refine the output, and let Datarails adjust the results accordingly. For example, you can add/change filters from the Datarails ribbon via
Filters >Manage.
dynamic_range_1.1.4_add_filter_to_reduce_rows.png

Select an Existing Range

The Dynamic Range pane has a dropdown so that you can switch between named ranges easily.
dynamic_range_1.1.4_select_a_range_dropdown_1_.png

Enable Auto Dynamic Range Update

You can activate this option from the Datarails ribbon via Help>Settings.
dynamic_range_1.1.4_enable_auto_dynamic_range_update.png

Use Right Click

dynamic_range_1.1.9_right_click.png

Dynamic Range Sort Order

  1. Click Select next to the Field Selector. A new screen opens.
  2. Drag fields to the Display Fields pane.
  3. Click the Sort icon next to Display Fields. This opens the sort order.
  4. Sort as needed. The sort options depend on the field type (text, date, boolean, number).

Turn Notifications Back On

dynamic_range_1.1.4_update_screen.png

If you elected to turn off dynamic range notifications by selecting Don’t show again, but now you’d rather than have them back on, this is what to do.
From the Datarails ribbon, go to Help>Settings and deselect the checkbox Disable automatic dynamic range updates.
dynamic_range_1.1.4_add_back_notifications.png



© Datarails Ltd. All rights reserved.

Updated

Was this article helpful?

0 out of 0 found this helpful

Have more questions? Submit a request

Comments

0 comments

Article is closed for comments.