Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Using functions in a report

From: SQL Server Reporting Services in Depth

Video: Using functions in a report

Databases are great for storing our raw data, but usually raw data isn't all we want to see. We want to see calculated values on our reports. We want to see the total sales for last week, or the average daily bug submissions for this quarter versus the same quarter last year, or perhaps the number of products we have right now with a status of Out of Stock. But this kind of information, these kinds of numbers, would not and should not be stored in the database directly because we can figure them out on the fly. We can calculate these numbers, and we will use functions to perform these calculations.

Using functions in a report

Databases are great for storing our raw data, but usually raw data isn't all we want to see. We want to see calculated values on our reports. We want to see the total sales for last week, or the average daily bug submissions for this quarter versus the same quarter last year, or perhaps the number of products we have right now with a status of Out of Stock. But this kind of information, these kinds of numbers, would not and should not be stored in the database directly because we can figure them out on the fly. We can calculate these numbers, and we will use functions to perform these calculations.

Common functions including totaling or summing numbers together, counting them, averaging them, minimum or maximum, first and last. So let's say I want to create a report in Reporting Services with the total sales for last week. Now, I could get to this total and add these numbers together in two different places. First, when I write the dataset or query or SQL I use a function that asks the database to total up all those individual amounts and just return with one value to the report, and then all the report does is spread that single value out.

But another option is I don't ask the database engine to do that calculation. I ask it to return all the values to the report, and I ask the report to do it, where the query itself is simpler. We get all the numbers back and in the report we total them up. So which way is better? Now you might think that it's a coin toss; it doesn't matter. Or if you are a developer, you're probably tempted to say it's best to do it all in the SQL, all in the query. Let the database engine do the heavy lifting. But actually, in Reporting Services it's often preferable to do this kind of calculation in the report itself, because we might want that report to not only show the total but to also be able to drill down into the individual data.

We can't do that if all we are fetching from the database is one number. But I am getting ahead of myself. So let's see an example. So I am in my Reporting Services Report Manager. I am going to click Report Builder and create a new report. Like most of them that I'm doing early on, I will just use the Blank Report option here. And the first thing I need of course is a data source. I am going to point this to AdventureWorks light. It will be, again, a connection embedded inside this one report. Selecting SQL Server, I'll use the Build button and point it to my SQL Server, which I am just going to use AdventureWorksLT.

And now create a dataset. What is the data that I am interested in here? Well for this example I am just interested in sales information, sales totals. So I am going to call this dataset SalesInfo. It would be embedded in the report. We will use the data source I just created and then I will jump into Query Designer. Now in the AdventureWorksLT database, that information I'm looking for would be in the Sales Order Header table, and for the purposes of this example, all I am really interested in is the TotalDue.

So selecting that one column, I could run the query here and we can see some of the results coming out here. Sure, normally I'd have more data than this. But right now I am staying super focused on using a few different functions, and I can perform several just from these results. I could get a total by adding up all these values. I could count all the rows returned to get the total number of orders, and I can average them. So I am just going to be done with this option. So I will click OK and my dataset is done. So now I'm going to do something with the reports. I will just give it a title here, of Using Functions.

And instead of adding a table, I am actually just going to add three pieces of information. First, what I am going to do is have a label just to show what they are. So on my Insert section of the Ribbon, I am going to grab a normal text box here and with that selected, I should have little crosshairs that I can come down and just draw an area by clicking and dragging on this layout. I am going to show the total sales, and I am also going to show the number of orders and the average order amount. Again, this right now is just a label.

I am also going to improve the font a little bit here, take it up to, say, 20. Align this on the right-hand side. I am going to drag it a little bit wider and as I do that, it will move out the actual size of the page, so we can see it properly here. In fact that font is a little big. I will just drop it down to 18 and that will work. Now, I need some actual values that would be calculated when this report is run. So where do I get them from? If I look over here in the Report Data section in Report Builder, I can see that my data set is showing up as SalesInfo with just one field here, of TotalDue.

So what actually happens if I drag that over, because you can drag from the Report Data panel? Now, do be careful if you are dragging over existing elements in the report layout, like text boxes here; it can often upset things, so it's often a better idea to kind of move it around so you don't move over the top of another element. So I am just going to let go. And it's dropping in a textbox here with this little Expr for expression. So what? What does that actually going to do? After all, if I just dragged on one column name, is it going to repeat them all? We could have a thousand different orders so what will this show? Well let's find out. I am going to go up here and click Run.

Now actually here, it's showing here the Total Sales 956303.5949, and that actually is the total amount of orders. It's adding all those total due amounts together. The question is, why? Why did it do that rather than just write out the first one or right out the last one or just write out some random single value? Well, let's find out. Going back into Design view I am going to highlight that. And there is a couple of ways I could get to this, but with that selected, I am going to come over here to my Property Pages and open that up, because here is where we get the easy way of looking at the value.

It's saying it's getting it from an expression. And if I click the little Expression Builder button here, I can see that we have got this value being written in this expression, this calculation, which says =Sum(Fields!TotalDue.Value, "SalesInfo"). Okay, the format might look a little weird here, but what we are basically saying is add together all the values in that Total due column for the dataset called SalesInfo, and it's this word, Sum, that is summing them all together. This is Report Builder's best guess of what I probably want, seeing as I'm not using a table here to repeat multiple columns.

So this actually works in this case. Well, what if I wanted something else? What if I wanted the average instead, or the count of them? Well, I can always use that as a starting point. So I am going to grab the TotalDue again from my Report Data section and drag it over here and put it beside, say, Number of Orders. Now, right now this will do exactly the same thing. If I clicked into the Run mode, we'd see the same number. But I can use this as a usual starting point. I'll grab this one, open up the Property pages, and open up the Expression builder for the value. And instead of Sum, I'm going to just change that to Count.

Now, how do I know what this is going to do? What's the difference between Sum and Count? Well if you have been working with SQL, you are probably familiar with Sum and Count, but if you are not, Sum is the total. It will add all the individual numbers together and count will just add the number of rows together. How many Total Due values do we have? It's not interested in what's in each one; it's just going to count them all together. Now, if I look down below here in the Expression Builder, I can see that there is a whole bunch of different options. We have things like datasets that are available here. We have Variables.

Well, there is nothing in here, Fields, Built-in Fields like PageNumber, TotalPages, ReportName, and so on. And there is a list of common functions. The most typical ones we are using with data are in the Aggregate or Grouping functions. This is where we have the option for Averaging, Counting, First, Last, Maximum, Minimum, Summing, and so on, and even some examples here of how you might use them in the expression itself. You could type these in or you can actually double-click any of them and it will jump them up into this Expression Builder area.

We are going to see more about expressions later on, but for right now this will do. I am saying =Count instead of =Sum. I click OK, I click OK, and we run this again. Now it's saying Total Sales, 956303, Number of Orders is 32. Well, let's add one more to add the Average Order Amount. And instead of dragging and dropping, I will show a different way of putting this one the page. I'm going to jump over to my Insert section, insert a text box, and with that cross- hairs, I am just going drag in a place that I want this to be.

This is the Average Order Amount. And if I was confident enough in my expression building, I could actually just type in that expression directly in here. And if I click off, we are actually going to see that change the expression. Let's see if that worked. And that seems to be about right, regarding about 29894. So I can't type that directly in there, but again, I could just select that and use my Property pages to get to that expression if I wanted to construct it myself inside this window. And while you're learning how to work with expressions, it's often not a bad idea to drag something on here to use as a starting point and then change it rather than invent it all from scratch.

This is still looking a little bit ugly, so let's do a bit of alignment and reformatting here. I am going to grab these three text box areas and just move the font up a bit to, let's say 16. I may have drag them a little bit wider just so that number will fully appear. Also in the Home tab, I should be able to align the left-hand side of them and I will select to left align them as well. The main thing that we are missing here in terms of formatting is that the numerical ones here look pretty ugly here. We have got total sells 956303.5949.

This is meant to be some kind of currency. Well back into Design view, what I am going to do here is--grabbing this to get myself a bit more room-- is select that first one which should be a dollar amount and from the Home section of the ribbon, here I have got this Formatting section. How is this being presented? And right now it says Default. What I am going to select is Currency. I am also going to select that for the third one, which is the Average Order Amount. From Default to Currency. Run that again. And now we have something that's a bit more presentable.

Okay, I could tidy this up a bit more with alignment, but this should do the trick for right now. If I wanted to do more with the formatting, I could do a little bit more with any of these selected. If you start to mouse over the different buttons here, you can see things like placeholder styles. What that would change it from is this Expr to, for example, Sample Values, 12,345.00. And then I can start manipulating things like increasing the decimal point or decreasing the decimal point, and I am getting a little preview of how that would actually look here. But this should do the trick for right now.

As we go forward, we will start combining these functions with more complex report elements like tables and matrices.

Show transcript

This video is part of

Image for SQL Server Reporting Services in Depth
SQL Server Reporting Services in Depth

40 video lessons · 9859 viewers

Simon Allardice
Author

 
Expand all | Collapse all
  1. 12m 6s
    1. Welcome
      1m 9s
    2. What you need to know
      1m 36s
    3. Exploring SQL Server Reporting Services components
      4m 17s
    4. Reviewing SQL Server versions
      5m 4s
  2. 49m 41s
    1. The elements of a report
      3m 10s
    2. Creating a report with Report Builder
      12m 11s
    3. Grouping table regions on a report
      6m 1s
    4. Joining data from multiple tables
      4m 33s
    5. Formatting report elements
      4m 34s
    6. Using functions in a report
      11m 0s
    7. Displaying data in a matrix
      8m 12s
  3. 24m 9s
    1. Filtering data and adding parameters to a report
      5m 35s
    2. Customizing report parameters
      5m 4s
    3. Sorting data in a data region
      4m 7s
    4. Applying interactive sorting
      4m 57s
    5. Creating a drillthrough action to connect reports
      4m 26s
  4. 49m 57s
    1. Introduction to charting in Reporting Services
      4m 16s
    2. Creating a column chart
      8m 35s
    3. Adding a generated average to a chart
      4m 5s
    4. Creating a pie chart
      8m 19s
    5. Using sparklines
      6m 38s
    6. Adding a sparkline to a drilldown matrix
      14m 34s
    7. Adding data bars
      3m 30s
  5. 21m 48s
    1. Adding indicators to a report
      7m 52s
    2. Using and configuring gauges
      5m 30s
    3. Using maps in Reporting Services
      8m 26s
  6. 38m 14s
    1. Creating modular reports with report parts
      4m 36s
    2. Adding and updating report parts
      4m 37s
    3. Using subreports and nested regions
      4m 28s
    4. Configuring headers and footers
      3m 9s
    5. Printing and exporting reports
      3m 45s
    6. Using page breaks
      5m 37s
    7. Creating and using shared data sources
      8m 11s
    8. Creating and using shared data sets
      3m 51s
  7. 27m 20s
    1. Organizing reports in Report Manager
      3m 1s
    2. Adding users and configuring report security
      5m 24s
    3. Configuring subscriptions
      5m 13s
    4. Creating a linked report
      4m 8s
    5. Using Report Designer in SQL Server Data Tools
      9m 34s
  8. 1m 2s
    1. Goodbye
      1m 2s

Start learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ.

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed SQL Server Reporting Services in Depth.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

Get started

Already a member?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferencesfrom the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.