Tip:
Highlight text to annotate it
X
{QTtext}{timescale:100}{font:Verdana}{size:20}{backColor:0,0,0} {textColor:65280,65280,65280}{width:960}{justify:center}
{plain}
[00:00:00.29] When you start to add parameters to
your report you can actually have multiple
[00:00:04.36] parameters and they will all show
up in this parameter area at the top.
[00:00:08.07] But most of the time you want something
more than just an open text box to type
[00:00:12.20] into, and we can change the appearance
of this based on the parameter definition
[00:00:16.49] in our Report Builder.
[00:00:17.79] Now, if you added the parameter through
the Query Designer, it will automatically
[00:00:22.37] show up in that section, but you
can also create your own parameters.
[00:00:26.48] I can see here that if I right-click on
the folder we can actually choose to add
[00:00:30.39] a new parameter, give it a name, set its values.
[00:00:32.89] But I don't need to do that right now.
[00:00:34.54] Either way, when I have a parameter, I
can actually double-click it in the Report
[00:00:38.49] Data section and I'll open up the
Property pages for this parameter.
[00:00:42.08] For example, it's telling
me this is ProductCategoryID.
[00:00:45.49] It's a data type of an integer.
[00:00:47.61] I have options here to change it to a Date/Time.
[00:00:50.02] If I changed it to that, we'd see it
show up as a date picker in that top
[00:00:53.67] section of the report.
[00:00:55.18] If I selected Boolean, it would show
up as a checkbox in that top section.
[00:00:59.44] But it actually is an integer.
[00:01:01.20] It is a numeric value.
[00:01:02.86] I just don't want to look at that.
[00:01:05.28] And I have other options
for what actually shows up.
[00:01:08.04] In the Available Values section here,
we've currently set it to None, which means
[00:01:12.81] we don't provide any values; the
actual user must type them directly in.
[00:01:17.04] I can choose to manually specify them
and create different values for both the
[00:01:21.19] label--meaning what the user will
actually--see and the value--what will actually
[00:01:25.30] be represented behind the scenes.
[00:01:27.75] What's even better is this option,
that I could get values from a query.
[00:01:32.50] I could create another query and get all
the category names and all the category
[00:01:37.05] IDs from a different part of the database.
[00:01:39.26] In fact, that's what I'm going to do.
[00:01:41.51] I don't have that data set right now,
so what I am first going to do is cancel
[00:01:44.81] out of this and add a
new dataset to this report.
[00:01:48.76] So I'll right-click my Dataset folder/
Add Dataset, and I want to create a new
[00:01:52.21] one called CategoryNamesAndNumbers.
It will be embedded in the report.
[00:01:57.13] I can just use the same data
source I already have defined.
[00:01:59.98] We are talking to the same
database. That's just fine.
[00:02:02.48] Then I jump into the Query Designer.
And all I need to do here is drop down into
[00:02:07.90] my ProductCategory table, which is right
here, and all I am interested in is the
[00:02:12.62] number of the ProductCategoryID and the name.
[00:02:15.23] That's pretty much it.
[00:02:16.33] To test this I can run the query,
and yup, that looks about right.
[00:02:20.20] Click OK, click OK.
[00:02:22.34] That dataset is now defined,
and I can use it in my parameter.
[00:02:26.57] So back into the parameter
definition, double-click on this guy.
[00:02:29.91] I am going to jump over into the
Available Values section and tell it to get its
[00:02:35.07] values from a query.
[00:02:36.85] It asks which dataset.
[00:02:38.06] Well, the one we just created, the
CategoryNamesAndNumbers. And then I simply
[00:02:42.24] have two choices. What are the
actual values you're interested in?
[00:02:45.94] What am I going to feed to the
product table so I can filter that data?
[00:02:49.91] Well, that actually needs to be the
number, the ProductCategoryID number.
[00:02:54.59] But being more friendly to the end user, what
the user sees can be the name of this category.
[00:03:00.01] I'm not going to go ahead
and change the default values.
[00:03:02.99] If I clicked here, it says specify values
as 35.
[00:03:06.59] That's because I defined in
the dataset. That's fine.
[00:03:08.83] I will just leave it alone.
[00:03:10.33] In the Advanced section are some
options for, do I always refresh automatically
[00:03:14.77] when this value is changed?
[00:03:16.16] I'm going to leave all of
that as is and just click OK.
[00:03:19.41] It doesn't look any different here.
[00:03:21.65] Well, that's not surprising.
[00:03:23.02] I can just go ahead and click Run to
preview this, and what I have up at the top
[00:03:27.46] has completely changed.
We no longer have a text field to type into.
[00:03:30.96] We have a dropdown list with all the
friendly names of the categories here.
[00:03:34.60] So I can select Jerseys, click View Report
to refresh that data, and see the Jerseys here.
[00:03:40.93] I can come down and click Mountain
Bikes and either click the button to View
[00:03:45.05] Report or press Enter to
refresh that data again.
[00:03:47.79] It looks like what I could do with
now is starting to apply a little bit of
[00:03:51.20] formatting here: Product ID doesn't
need to be as wide, Name could be a bit
[00:03:55.28] wider, and List Price
should be formatted as Currency.
[00:03:58.39] Again, doing that kind of
thing is pretty easy stuff.
[00:04:02.06] When you're resizing columns,
it's best to grab the gray bar at the top.
[00:04:05.85] If I want to change this Sum(List Price),
then what I do is actually click on
[00:04:10.23] the text itself until it's highlighted.
[00:04:12.12] I can see now here the Number
formatting section is showing up.
[00:04:15.80] Change that to Currency and run it,
and that's a bit more presentable.
[00:04:20.20] If you want to provide parameters in
your report but not always show them to
[00:04:24.16] the end user, that is possible to do as well.
[00:04:27.33] Jumping back into Design view and
editing that parameter, I will see that on the
[00:04:32.21] first page there are some options for
whether this parameter is always visible
[00:04:35.46] or whether it's hidden or internal.
[00:04:37.58] One of the reasons you might want to
do this is that you as a report designer
[00:04:41.62] may occasionally want to change the
default value of this or the parameter of
[00:04:45.93] this every week or every month, but you
don't want to give that option to every
[00:04:49.60] single user, every single
person who views that report.
[00:04:52.28] And there are even ways to make these
parameters conditionally appear depending
[00:04:56.72] on who's looking at it or
on other data on the page.
[00:05:00.60] But this is how we begin
to parameterize our reports.
[00:05:04.53]