How To Use Row & Column Ranges

Overview

This video teaches you how to use column (and row) ranges to combine different slices of data into a single matrix using one underlying dataset query, instead of building separate widgets for each.

Facts to Remember

• Combining slices via ranges into one query is the optimized approach that improves load times.
• The same technique works identically for row ranges as it does for column ranges.

Transcript

Okay, hello everyone.

Today we're going to be covering how to use row and column ranges in Una's dashboard.

So let's get started.

So with the income statement example here, right now as we can see I have actual 2026 data for all of these accounts.

Now let's say I wanted to also include plan 2027 in another column for the same accounts, for the same rows.

How would we do that?

Using the most optimized way to query a data set.

Okay, so we're going to click, right click here, we're going to click edit, and we're going to click edit query.

So what we can do is we can add a column range.

So you can see this plus button here, add range.

And then now you will see we are in the first range.

So before we were at zero, it originally starts off at zero, and we can go to one.

Now these two are, now we're in a complete different. um query over here for scenario and period so for scenario i wanted to do i'm going to right click click delete i want to bring in plan and then for a period like we said we want to see 2027 let's click ok and then something you want to remember to do is when you're adding an extra column range or row range you want to go to the space section here and then

Topics include the Model Builder, Explorer, dimensions and hierarchies, data models, and importing data from flat files.

Topics include the Model Builder, Explorer, dimensions and hierarchies, data models, and importing data from flat files.

Topics include the Model Builder, Explorer, dimensions and hierarchies, data models, and importing data from flat files.

Bringing in a whole different new data set and a whole different new query in this exact dashboard for this exact widget.

So your widgets will load faster.

Basically, your whole dashboard will load faster, which is a benefit to all, right?

Now, let's say I wanted to bring in the children of 2027.

So I want to see Q1, Q2, Q3, Q4.

Let's quickly go through that as well.

So I'm going to go on Edit Query.

We're going to add in an other range.

So if I click Select Range, we'll see now there's 0, 1, and 2.

If I wanted to delete a range, I just have to select the one that I want to go on and then click this Delete button.

So if I select 1 and click Delete, it will delete the first column range that we created, which was Plan 2027.

So now I'm going to quickly go on to, well, there's two ways of bringing in the children of 2027.

I'll show you both ways.

So using column ranges, we can click two.

And then what I'm going to do is I'm going to go on period.

And instead of self, we're just going to click children, which brings in the children of 2027, which are the quarters.

So we're going to click OK.

And then watch, I'm going to leave.

But the data is not loaded.

I don't see 2027, right?

What went wrong?

I did this purposely because this is a trouble case that we are all going to experience if we all remember that we have to select whatever column range you created or row range you created in the column region section.

So I didn't select number two and it wouldn't have loaded.

So we have to load that one as well.

I'm going to quickly go ahead and quickly just change something and make this 2027.

It didn't actually just change for some reason, which is no problem.

There we go.

Perfect.

So now we have actual 2026, plan 2027, and the children of the plan 2027, so Q1, Q2, Q3, and Q4 as well, using one dataset query that we have made to the database.

Now we can go on queries, and as we can see, we only have one query to confirm this, and we can just call this actual.

Versus plan.

And then we can go on back to space, select the query, and we can see actual versus plan.

And there we go.

It works.

And it would be the same process if we wanted to use row ranges.

Same exact step, but for this case, we wanted to do column ranges, but they're the same exact steps.

And again, just to recap.

The point of why we do this is to optimize the calls that we're making and queries that we're making with the database.

And then the benefit of why we do that is because our widgets and dashboards will load way faster as we're just making one query to the data set and database instead of making multiple.

Related Articles

• Matrix Widget: spaces & layout — the written reference for ranges and template sections
• Matrix Formulas — adding calculated rows and columns alongside ranges
• Filters and Filter Sets — the other way to narrow what a matrix shows