(Upbeat music)
In this module we will start to work with parameters.
Having learned how to connect to data
and to retrieve data and lay it out,
it's a common requirement that you'll have parameters
that are going to allow your users to interact
typically for purposes of filtering.
Let's introduce these parameters
and specifically the two types,
Report parameters and Query parameters.
And then in the next video
we'll work with these parameters in different scenarios.
Parameters are typically used to filter report data.
The two types are Report parameters and Query parameters.
Let's take a look at report parameters first.
These are the parameters that are used to prompt
the user report execution time.
Users will enter values,
and what you can do with them as a report author,
is map them to data set query parameters.
This way, when the report user filters by year,
fiscal year 2019
that would get passed into the query statement
and sent to the data store.
Or we did explore the ability for a data set to be filtered
so it's possible also that the report parameter value
could filter the data set.
Remember it could retrieve all years of data
and then apply the filter to just render a particular
year for that report instance.
Now, interestingly there are other uses of report
parameters beyond just filtering.
The obvious use, that really
now that you understand that expressions
can be dynamically assigned to many properties,
then there's a potential to do things like even formatting.
Yes.
Right.
So, for example, if you're going to highlight
you know, anomalies in red,
you could let the report user set the threshold.
Yes.
At what point would it become an anomaly?
Ah, you can also customize data regions
that we talk about in the next module
and even control sorting and grouping
through expressions that are controlled
through report parameters.
You know, one of my favorite examples of this
is you can actually choose different visuals to show.
You can have a parameter that says
'show a chart of the data' or,
'show a table of the data'.
Right, so it's sort of like bookmarking
Erm, kind of
In Power BI reports in a way
and that's the point that we've got
is another use of report parameters
is to hide or show report objects.
And they can also be used for configuring report
drillthrough actions and sub-reports.
Although, that's not a capability
that is currently supported but hopefully
it's something that is possible in a future release.
All right, configurations.
So, in the report data pane
you'll see the report parameter,
we'll take a look in just a moment.
Each report parameter has a unique name and a prompt
which is generally a more friendly name.
Bearing in mind the identifiers for the objects
in our report layouts don't allow spaces.
And they're quite strict on the use of certain characters
so the name is generally a name that will refer to
an expressions like 'employee keep' and yet,
the friendly prompt that we'd like to see the report author
work with, is something like 'sales person'.
The report parameter can also be defined in terms
of data type.
Does it allow blanks or nulls
and does it support Multi-Entry, that we will talk
in the next video.
The parameter could be hidden or visible, or internal.
Why would we have an internal parameter?
If you were using something, say, a username parameter,
you would want that to be something that is done internally
versus expose the user.
Right, so it's like a private concept that it couldn't be,
er, couldn't be interfered with or modified,
outside the report itself.
Stay tuned in module seven when we start to talk about
URL access.
Oooohhh
All right, so here's an example of, um a report parameter
that has been configured with available values.
If you don't configure available values,
then we just get a box, right.
That's correct.
Enter the text, enter the number or if it's a date,
you'll get a date picker, I think.
You do get a date picker, correct.
Now to provide a list of options, as you see here,
a drop down list saying 'these are the sales people'.
Then for the report parameter,
we can configure its available values.
The source of available values
can come from a static set of data or another data set
that you've defined in the report.
Yes, the static set of data er, you want to be very
careful with this, but for certain things like
the US States for example, that's something you could
use, say, Enter data for as a data set
or just type the values in.
It's gotta be something, you know,
absolutely isn't going to change.
That's right.
Or that if it changes you're happy to modify the report
and re-publish it.
That's true.
When you connect to a data set, um, as you see
on the presentation here,
you get to configure the name of the data set
and then you map fields to the value and label.
It's quite common in databases that we use Keys
as unique identifiers.
And so, in this case, you would map the Key to the value
and the label would be the human readable representation
of that value.
Default values come next.
Now, if you don't have default values
and the user is obliged to enter a value,
unless you are prepared to accept, you know, blank entry.
Uh-huh.
All right, providing defaults can be a good report design,
especially when you know
that common values are going to be set.
The other benefit of having defaults is
that if all parameters have a default,
when you switch to preview or open the report
in the Power BI servers then it will render immediately.
Yes.
Without doing this, you are requiring your report user
to go ahead and enter values.
So default values can be a great way
of providing convenience and faster access to a report.
And, don't over look at default values can also be driven
through data sets as expressions.
That's right.
So a good one is date ranges,
based on current date and time.
Yeah, often times it's the used case we see most often
with default values, adding the date of today
as the default.
Right, so that's the concept of default values.
We introduced earlier in the course that there is a pane
dedicated to report parameters.
This pane is sort of like a matrix, isn't it?
That's true.
It's sort of like a hidden rows and columns
that allows us to place our report parameters in there,
giving us a degree of control over what the user will see
in there parameter pane.
Um, it's simply a matter of drag dropping
to put your parameters where you would like them.
If you need to add a new row or a column,
just right click,
the context menu's are very very easy to understand
to produce the layout that works best
for your report design.
That's the topic of report parameters
and yet there is another type of parameter
that we did introduce when we described data sets.
Data sets have parameters too.
Yes.
This can be confusing for beginners.
So the query parameter is in fact an injection
into the query statement.
Yes.
All right, and so where we've got that we want the sales
order for a single, um, a report for the single sales order,
we would have a WHERE clause on the select statement.
Yes.
Where sales order ID equals a query parameter.
And this is where the query designer you pointed
you earlier can really help,
because often times people get confused
on exactly how to write that out,
so they do like having a graphical way to do that.
And yet,
there's an association between both parameter types.
That's true.
So to be clear, a query parameter
injects into the query statement, lets see an example here.
Go and retrieve all columns from the Dim employer table
where the employer key equals, and note the @ symbol,
Yes.
The @ symbol is the character for sequel server,
as in this is a query parameter.
Now what that will require the data set to do,
remember that this is the query of the data set,
is there needs to be a mapping
to the employee key parameter.
And the common mapping is, the report parameter.
Correct.
The report parameter prompts the user,
and then via an expression we assign their value
into the query parameter of the data set.
Or, remember we talked about filtering,
you might choose to take the report parameter
and map it to a filter over the data set, whereby,
you get re-use across a larger filtered set of data.
So they're the basics of parameters in your report design.
Report parameters to collect information from the user
and query parameters that are injected
into your query statements at rendering time.
In the next video we're going to explore various scenarios
working with parameters like Multi-Select,
working with defaults and available values.