(upbeat instrumental music)
INSTRUCTOR: In this demonstration
working with parameters,
we're going to enhance the salesperson directory
with various parameter scenarios.
Here's the report that was developed
in a previous demonstration.
Let's preview it to remind ourselves what it looks like.
Okay, so it presents an entire list
of the salesperson directory.
I see we have page one of five.
And yet what we're going to do is parameterize this
to allow the user to filter down
to what is of interest to them in the report.
So I'll switch back to Design.
We notice in the report data pane
that there are no parameters.
And so I'm going to right-click the dsMain dataset
and I'm going to open up directly to the query.
Actually no, let me open the properties.
Let's remind ourselves about the statement.
There is a WHERE clause that is simply filtering
where the salesperson flag is true.
Now what I'll do is open up the Query Designer.
And remember that we have support for parameterization.
The plan here is that we want to
produce the report for a single employee.
So I'm going to add a new filter, based on the employee key,
and I'm going to parameterize it.
As simple as that.
When I click OK,
look at the modification that's being made to the query.
Where EmployeeKey equals the query parameter EmployeeKey.
Now watch what happens when I click OK.
A report parameter was created automatically.
Let me reopen the dataset and its properties.
And take a look here in the Parameters pane,
where we see a mapping of the query parameter
to what is an expression to the report parameter.
So this is the way that we can filter through.
Let's test it.
Employee key.
Hm, can you remember an employee key for an employee?
I cannot. I cannot either.
This is a bad design,
but let's improve it in some moments.
I got a feeling that 298.
Nope.
289?
Yes, David Campbell.
All right, so, what we've got
is a report parameter value being mapped
to the dataset query parameter,
and there's phase one of the design.
Well, clearly there's a deficiency in the design,
isn't there, Chris?
Because we have to have an excellent memory
and in fact, an employee key is probably something
that most people wouldn't memorize.
Let's then move on and improve the layout.
Let's take a look.
We've got Employee Key sitting up here as a parameter.
So I'm going to open up the parameter properties
and first of all make the improvement
that the prompt will become just Salesperson.
All right.
And the next consideration will be for available values.
Now, we could define a static list of salespeople,
but that's not a smart design.
We'd rather retrieve them from a dataset.
So let me switch that back to None for the moment.
Click OK.
We see that the salesperson prompt
has updated here in the parameter pane.
And now it prompts me to in fact create
an entirely new data set.
All right, so what I'll do is this.
I'm going to right-click the data source.
And I'm going to add a dataset
and provide a unique name of ds.
And I guess I'll call this dsSalesperson.
Rather than type in the query,
for this demo I'm going to demo the import capability.
And so we'll find that we've got the ability
just to import from a SQL file.
So I've got my dsSalesperson_1.
And this query here is retrieving two columns,
the employee key,
the salesperson name that's been formatted
with an uppuler last name,
coming from the employee table
where the salesperson flag is true,
and it's being ordered by salesperson name.
No query parameters.
And now I have a second dataset added to the report.
And I come back and I edit my report parameter,
and I configure the available values to use that dataset,
mapping the employee key to the value field
and the salesperson name to the label.
Let's see if it improves the experience.
Now we have a drop-down list,
and I'm able to choose any of the employees
and view the report.
Now while we're at this point,
the report isn't terribly self-describing.
Remember that we have the subtitle textbox
and now is the time to edit
the expression for this textbox
and ask it to use format parameters,
the employee key parameter, not the value.
We don't want to see the numeric key,
we'd like to see the label.
And now when we run the report,
and choose any of the employees,
we'll see the subtitle is reflecting
the selected parameter.
CHRIS: Nice.
INSTRUCTOR: All right, so what do you think, Chris?
CHRIS: I think that's great.
I think it's a really helpful tip
in terms of that subtitle piece,
because that's something that often new report authors
aren't aware that they can do as a simple way,
especially when you think about the distribution scenarios.
INSTRUCTOR: Right, and the next improvement then
might be, well, why can't we just have
the report run immediately?
And that's where we would say yes, we can do this.
That's just the default for the report parameter.
So returning to the report parameter window,
the third page is for default values.
And again, we could have a specific value that could
be static or it could also be based on an expression.
Remember for time-based it's quite common.
You know, what date period do we want to look at sales?
Well then, we can use expressions
that are based on current date and time.
We can also assign datasets to default.
So let's see what happens if we use the salesperson dataset
and map the employee key to become the default.
Because there's a default,
we're gonna see the report runs immediately.
And do you notice that it's actually selected
the first item in the list?
I did. Well, the dataset is
actually representing all of those rows.
If you do that and it's a single select parameter,
then it will select the first one in the list.
CHRIS: Yeah, so you need to be careful there
especially if you're thinking
about using a default parameter.
It's a great way to actually have the report
run for users and make it simple for them to get started.
Be very careful that you're not using a set of parameters
that are a dataset that needs to be whittled down
before it will perform well for you
because I know oftentimes people
will put a default parameter in there
and want to look at a billion rows of data
and maybe it makes sense to have users go
and select some parameter values
so it only is bringing back the data you need.
INSTRUCTOR: In that case,
what they might be able to do is at least do this?
CHRIS: Yes, they can stop.
INSTRUCTOR: They could stop.
All right, but then it might be too late and it's like...
So just be careful.
Default values can be good, they can be bad.
It's just a circumstantial thing.
The next improvement might be
that selecting a single employee is a little strange.
Let's give them the option
to either have all of them, or a single one.
CHRIS: Yeah, this is a common ask.
INSTRUCTOR: So that has me return
to the salesperson dataset.
And I'm gonna import an improved query.
And that improved query will be, what do we have?
We've got four assets, Salesperson 2.
So here we see two SELECT statements working together.
Here's the injection, so to speak,
of an artificial row with a key minus one
and the label All Salespeople.
We UNION that to the original SELECT statement.
So in fact, there's no need to change the parameter.
We just see an extra row turning up at the top.
And it's defaulting,
but that's where we need to be careful
'cause there is in fact no employee with a key minus one.
So that has us return to our main dataset.
And I'm going to import an updated version,
which is the dsMain_1.
That's the one.
Here we go.
So originally it was give us all of the employees
that have a salesperson key of one.
Now we have this,
where the employee key equals the query parameter
or that query parameter equals minus one.
Let's see how it works.
Run the report by default.
Remember, the default is selecting the first item.
And we see that we've got five pages of all the salespeople.
And now if I just request a single, we get that single.
Nice. Yeah.
So it's a fairly simple arrangement to put together.
Now, let's move on to, I think,
the more interesting topic.
I just love cascading parameters.
They're almost in every report that I write,
they're invaluable.
And so what we're gonna do is adjust it now
that rather than presenting
an available list of all salespeople,
that they first get to choose which sales territory group,
and then we get to see the sales people from that group.
All right, so what I will do is
I'll edit the query for the salesperson dataset.
And I'm gonna import a revised version.
CHRIS: This feels like cheating.
INSTRUCTOR: Yeah.
(laughs)
Well, we had limited time,
both in presentation and I'm sure our viewers
have got better things to do,
like write awesome reports.
CHRIS: That's true.
INSTRUCTOR: So, I'm gonna bring in
Salesperson version three.
Okay, so let's have a bit of look at this.
What do we have?
We still have the unioning arrangement here
to bring in all salespeople.
We've got all salespeople but with a query parameter here
that is gonna filter by a sales territory group.
Now remember that when you update a query
with a new query parameter, watch what happens.
Here we see the addition of a report parameter, okay?
So we know that it needs some available values.
So before I configure that parameter,
I'm going to right-click the data source.
I'm gonna add a new dataset
that's going to retrieve
all of the sales territory groups.
Sorry Chris, I'm gonna cheat again.
CHRIS: I just said it feels like cheating,
I didn't say it was actually cheating.
INSTRUCTOR: So here I have a dataset query
for the sales territory group.
Very straightforward.
Just give me the distinct sales territory groups
from the sales territory table.
Note that we don't have a key column here.
There's no key column per group
and that's fine when we configure
the available values for the sales territory group.
So firstly, I'm gonna rename this as Group.
There's the prompt.
Come to the available values set that our new data source,
and that that sales territory group
is both the value and the label.
That's perfectly fine.
We make sure that they're in the correct order.
Sales territory group then employee key.
In fact, I might prefer, by dragging salesperson,
that they're running vertical.
Choose the group then you can choose the salesperson.
All right, this label might
need to explain what's happening.
Okay, so what I'd like to do is this.
I'm going to open up a snippet file for this lab.
And I've got an expression here.
Let me copy it in, and then I'll describe it to you.
So I'm going to modify the expression
of this textbox to do this.
Give me the sales territory group parameter's
value concatenate, and the Iif is conditional.
If the employee key value is minus one,
then return empty string.
Otherwise, return the label of the employee key.
Now I want you to notice that salesperson is disabled.
It has a dependency on Group.
I'm going to go ahead and choose North America
to see a list of the North American salespeople.
And watch what happens when I choose Europe.
There's a list of the European salespeople.
If I choose all salespeople,
we just get Europe as the label.
If I choose a single one, like Jae Pak,
then our expression returns both Europe
and the fact that we're filtered on the individual, Jae Pak.
Yeah, no, I really do love this topic.
CHRIS: I know, I can tell.
INSTRUCTOR: And I can see the paginated report bear is,
his fur is standing on end.
CHRIS: It's not my favorite.
INSTRUCTOR: (laughs) Well, have you ever noticed
that your report users are never completely satisfied?
You do these amazing cool things
and they're like, yeah, but what about multi-select?
CHRIS: Usually I take that as a good sign.
That the user likes it so much they want more
versus they just don't provide any feedback,
which means they didn't use it.
INSTRUCTOR: Well let's return to the report design.
And turning on multi-select is a trivial thing to do
from the point-of-view of the report parameter.
So I'm going to edit the employee key
and I'm going to allow multi-selection of the employee key.
All right, now remember that's going to
require consideration for our main dataset
because at the moment,
it's using this type of logic, all right?
Where the employee key equals a specific employee key or.
Now, this probably won't work well
if we've got an all salesperson in there as well, will it?
Probably not. Probably not.
So now that I think about it,
what we'll do is we will come to our
available values for salesperson.
And we'll remove that artificial one
because they can now multi-select.
They don't need that.
So what we're going to get back
is one or more keys of valid salespeople.
So in dsMain, what we want to do is adjust the employee key.
I'm not gonna cheat this time, Chris.
I'm gonna say it's in employee key.
And remember, the in operator expects a comma-separated list
and Power BI will parse through a comma-separated list
of the multi-selected items.
CHRIS: Oh, nice.
INSTRUCTOR: All right, but what does it mean
for the expression--
Oh, what's going on?
CHRIS: You have to provide a default.
INSTRUCTOR: I have to provide a default.
CHRIS: Or some sort of value because it's required.
INSTRUCTOR: Okay, but I think it accepted it anyway.
Let's see what happens.
What does it mean for our expression up here?
CHRIS: It means it'll change.
INSTRUCTOR: Well the expression
was expecting a single employee, not an array.
So let's see what happens.
Fingers crossed.
We go in here and say okay, give me North America.
And now look at this.
Multi-selection.
Why is it that all of the salespeople are already selected?
CHRIS: Because of the way you set up your default values.
INSTRUCTOR: Correct, 'cause the default
is mapped to the dataset.
And maybe that's a good thing, or not.
I don't know, but I'm happy to leave it for now,
in which case I can then easily subtract what I don't want.
In fact, it might be easier to use Europe.
And say I just want Jae Pak and Ranjit.
Let's go ahead and run.
Well, let's use this now as a way
that we troubleshoot errors.
And so it doesn't like the semicolon
that I've got within the query for dsMain.
Okay, well that targets the problem.
Let's see what I've done.
I'm a semicolon person,
so I've got a feeling it just doesn't want that.
CHRIS: You have to be very careful.
INSTRUCTOR: And let's see.
And it says I still don't like it.
Could not update a list of fields for the query.
You know, this might be why, Chris, I like to import.
(laughs)
'Cause I know that it works.
All right, so I think I need to come here and it's assets
and I'm doing something like, what would it be?
dsMain_3.
Well that looks pretty much like what I had before.
Let's see, it likes it this time.
All right, so where were we?
We were looking at European salespeople,
and I was gonna subtract one of them and then run.
CHRIS: I'm enjoying this exercise
simply because this helps me reinforce to my engineers
that I work with why people so enjoy the graphical designers
versus handwriting their SQL queries.
INSTRUCTOR: Well why is it that
we're getting an error here?
CHRIS: Well, you didn't change the expression
to properly reflect the change in the parameters.
INSTRUCTOR: Exactly, right.
So, the expression was expecting a single value
for the label here, okay?
Now where it's not a single value, it's an array.
And the way that we can work with that
is we can use the join function.
And the join will take an array
and we will tell it how to separate them
and build a single string.
So what I've done here is I've wrapped the label into join.
And I've added this parameter to say
produce a string that comma space separates them.
Let's see how it works.
Again, for Europe, eliminating one of them.
View.
CHRIS: Well done.
INSTRUCTOR: Okay, Chris, what have I done wrong?
Ah, we also have this minus one going on here,
so I guess that's my problem.
We can't be testing for this.
An array can't equal minus one.
So,
probably this.
CHRIS: That's better.
INSTRUCTOR: But I think I had a trailing parentheses
that I probably want to get rid of, as well.
CHRIS: You do love your parentheses.
INSTRUCTOR: Well, you know,
products like this are very specific.
CHRIS: Yes, what we talked about
in an earlier module. Not too many, not too few.
CHRIS: We talked about that in an earlier module.
You have to be careful.
INSTRUCTOR: And there we see it.
Comma-separated values.
What if I selected all of that?
And notice that multi gives us a Select All.
That isn't coming from our query.
When you enable available values for multi-select parameter,
you get an automatic Select All.
And it's really just a convenience.
On or off, on or off.
And if I select all of them,
you know, that's gonna become problematic for North America.
Let's see an example of a textbox that grows as it needs to.
So, the logic would probably be better using this snippet.
Have a look at this logic here
that says if the count of multi-select parameters
equals the number of rows in dsSalesperson,
then we don't need to comma separate them,
'cause we know they're all from the group, all right?
Otherwise, go ahead and join and give us a list of them.
So let me copy this snippet.
And then we'll finalize that design.
Okay, so I'll use North America.
If I select all, then I should just get North America.
If I should just remove one of them,
then I'm going to get the list of them.
That's working with parameters.
Chris, what do you think?
CHRIS: I think you kept me on my toes
during that particular session.
INSTRUCTOR: Here you go, so there's a lot of room
for creativity and for delivering an experience
that is most convenient and suitable for your report users.