(upbeat music)
You've arrived at module 5.
Now we get to talk about how we can visualize report data
in our report layouts.
We gonna talk about visualizing report data
and our initial focus will be on creating data regions.
And then we'll come back in the next video
and look at data visualizations.
Recall that it's on the Insert ribbon tab
where you can insert Data regions and Data visualizations.
So, what do we mean by Data regions and visualizations?
Well lets introduce that the data region is actually
what we call it a Tablix.
Tablix has variants of Table, Matrix and List.
That's where the word Tablix comes from.
[Chris] - True.
[Peter] - T, A, B from table,
[Chris] - Yeah, you had a really cool slide
to mix them together.
Yeah, I doesn't quite do that.
But if you can use your imagination
and you can merge all of those together,
you sort of come up with the word Tablix.
So, we'll focus on the Tablix initially.
It provides flexible grid layouts
and you've already seen the List in demo,
provided a repeat of template with a rectangle.
The Data Visualizations give us Charts and Gauges and Maps,
Data bars, Spike lines and Indicators
and we'll come back to these in a later recording.
So all data regions as I've just mentioned
are variants of the Tablix.
And the Tablix enables very flexible grid layout
and with groupings.
And when rendered, the Data Region
will expand according to the data and your configuration.
So, hence the interesting thing.
We've talked about page layouts
and the importance of margins
and sizes to ensure that it prints appropriately
and we don't have blank pages.
But depending on the configuration of your Tablix
we may not know what size it will be,
horizontally or vertically.
[Chris] - Correct.
Because it's based on the data
and of course the filters that are being applied.
[Chris] - Yes.
So the reason that Report Builder
distinguishes a group of visualizations as data regions,
is that these, we don't know at design time
what size they will be.
[Chris] - Exactly.
Whereas the Data Visualizations
are fixed in size like a chart,
it will not expand beyond
what you've configured at the design time.
All right, so we'll talk about the visualizations later.
The focus will be now to talk about
the variants of the Tablix.
So use the Tablix for flexible grid layouts.
The table is characterized by fixed columns
and dynamic rows.
Whereas the Matrix for those familiar
with Pivot tables in Excel,
they have groupings on columns and rows.
So while the table will grow vertically,
the Matrix can grow in both directions.
[Chris] - Yeah and you saw this during the wizard earlier
when we actually had to choose what were column items,
what were row items.
you have that ability in both.
[Peter] - Right so what happened in the wizard though
we didn't configure anything on the columns.
[Chris] - No.
[Peter] - So, therefore the wizard produced a table
but if we did add something to the columns,
it would produce a Matrix.
[Chris] - Yep, so you need to keep that in mind
when you're setting that up.
[Peter] - Correct.
The List you've already seen,
it is variant of the Tablix it's its simplest form.
Its simply a rectangle
and the rectangle will repeat for each of your groupings.
Now when it comes to the groupings of the Tablix,
there are these three types.
You've got Nested,
which if you think of drilling
into a hierarchy from country to state province to city.
What's interesting about the flexibility the Tablix
gives us is that we could also have Adjacent groupings.
So the same data could be represented horizontally
by different groups.
And then Recursive groups is supported as well
that if you have parent-child data.
And I think the classic example of this
would be an employee table
where each employee has a relationship to another employee.
Their boss, right?
[Chris] - Exactly right.
So we could also generate a parent-child hierarchy
by using a Tablix.
Lets see why its got a Tablix, stay tuned.
This is the reason why.
Do you see the way that what was a table
and what we had something of grouping of columns
has now produced a single grid
that has nested groups and adjacent groups.
Lets do it again.
Alright, table and matrix together on the left.
A table that has fixed columns on the right,
the Matrix that has a grouping on year.
Potentially they'll have 2020, 2021 in the future as well.
And yet we can produce a single grid to achieve this result.
[Chris] - And that's incredibly powerful.
[Peter] - It is, Tony's up to you as a report author
to express the data in exactly the way
that it makes sense to you.
Now this is something that Power BI report couldn't do.
No, and this is an area where a lot of people
look for this type of functionality
in their Power BI reports.
They're looking to do exactly this,
and paginated reports is the answer
for this type of scenario.
And also what's interesting to note is,
that there are render formats that have data formats.
So this can be a way of transforming your data
for export into other formats like XML and CSV.
[Chris] - Exactly.
[Peter] - And they can structure and transform the data
precisely to what is required.
So a discussion now about the grouping of a Tablix.
Remember there's a Grouping pane
that you see beneath the report canvas.
And I'll point out where we'd discuss
that variables were possible at reports,
so I'll just add now that variables
can also be defined for groups as well.
We won't go there in this training.
Now there's a Detail group that represents
the lowest granularity of data
being retrieved by your data set.
And it's possible then to add groups
that provide summarization at higher levels.
Now the Grouping pane also has an Advanced Mode,
when you see this shortly in demonstration.
And it provides you fine-grained control
precisely over how the rendering
of the grid will take place.
It's something that you might need to do
if your table is gonna overflow to multiple pages,
in fact our sales order report did this.
There might be a need to come in here
and configure precisely how on a new page
the groupings or headers should repeat.
Now, when you going to be working in groupings,
then you have to consider
how you going to summarize data at those grouping levels.
And Report Builder provides
a whole host of aggregation functions.
From Average to Count to First, Last, Min, Max, Sum,
even running values for the accumulation.
Now if you going to perform aggregations at higher levels,
you can do so in these calculations by providing a scope.
So let's look at this example.
Using the Sum-Aggregate function,
our expression is the value of the Sales field
and here we're requesting what the value is,
at the Country level.
So this will be useful for example
if you've got the sales people within the country
and you wanna know what the proportion
of that sales person is, over the country itself.
The denominator could use this expression
to reach to the grouping aggregation.
And there's one special aggregate function.
It's gonna play an important role
in the upcoming demonstration.
That if you're using the Analysis Services Designer
at the MDX, we made mention about the fact
that server aggregates were supported.
While the report has groupings at higher level,
it's not an easy matter of simply summing the detail rows
to get the grouping total.
[Chris] - That's right.
[Peter] - A good example of this is if you're requesting
the distinct number of customers that purchase
from you each month.
Now, if you get monthly distinct customers you can't
add them together to produce
what the annual distinct customer value is.
The only way to do this is that you must either
load all of the data,
the report must load it
and then produce the distinct
or we push it back to the model itself.
But that's actually the specialization of modeling
is that they perform summarization with measures.
So there's a special capability
that when you use the Aggregate function in a Tablix,
when the data set that the Tablix is using
is based on the Analysis Services Design with MDX,
it's going to push back and modify that query
to retrieve server level aggregates.
To me this is a remarkable piece of engineering
and it provides you accuracy
and it provides you high performance
and it means you don't need to repeat aggregation logic
in the Report design itself.
Alright let's take a closer look at those variants now.
We use a Table to present fixed columns with dynamic rows.
And essentially what you get is a template,
and what's interesting is that template is really
just a series of text boxes.
But those text boxes behave together in a specialized way.
So when you're formatting your Tablix,
mainly three are text boxes.
They bound however to a single data set
and then we get to control through expressions
and groupings about how that grid will be rendered.
So, for example, we can see here
that there's a Table header.
We've got a grouping on Country
and in the detail rows of the Salespeople themselves.
Note that the columns are fixed,
we would never expect this Tablix to grow any wider,
than what we know at design-time.
Vertically, according to the data on the groupings,
we don't know, it could be multi-page to render this.
[Chris] - That's true.
[Peter] - The Matrix, while its template looks
far more simpler, it's actually potentially
a very complex configuration.
So we got the ability to add groupings on rows
and columns, therefore what you're likely to do
at the intersection with data is use aggregate functions.
What is a summarization within months-countries-products.
Right, remember we don't know
until the report is rendered,
how wide and how tall this visual will be.
So therefore in fact I found it that rendering,
especially large matrices, is best done with Excel.
[Chris] - Yes.
Attempting to put it into a print format,
sometimes it's just luck that it's not gonna span
over multiple pages.
Yeah putting that into a PDF be sure, it's challeging.
So here's an example what a matrix would look like.
And I've included in this diagram the Grouping pane.
You can see on the row groups that we've got
nested groupings from category to subcategory.
And then on the column groupings we've just got Country.
And then at the intersection the values that you see
using the SUM function to aggregate the cells' values.
At last we come to the List,
which you've already seen in demonstration.
Its template's very simplistic.
Just a rectangle, but remember a rectangle is a container.
And so therefore you can decide whatever controls
you would like inside.
You can even nest of the rectangles
within the rectangle and achieve grouping.
And this is what, I think you've already seen,
the result of a particular list layout.
Alright, I think we're ready to see a demonstration
of the Tablix flexing its muscles.
This should be great.
Awesome stay tuned for the next video
and we'll introduce you to the Tablix.