Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
I'm a big fan of an incremental approach to writing reports in Report Builder. Start slowly, get something working, and then add things to it. So right now we do have this report working and it's drawing information from three different tables, but there is a little bit I could do to this. Total Due is basically being output right now just as a money column. There is no currency formatting and it could do with some. I don't really need FirstName and LastName to be separated into two columns here. So I'm going to go back to the homepage of Reporting Services and then select the drop-down button to edit this in Report Builder.
Changing the formatting of a report and changing the entire layout is very easy, even after you've saved it for the first time. So again, back to this report, selecting any of these cells here will highlight the field that we're using and allow us to tweak it, allow us to change the font, allow us to change the background color. However, some of the options can be a little bit deceptive. If you're used to working with things like Visual Studio or other programs like that, you're quite used to looking at the Properties panel here. But in fact that's not what I want.
I've got highlighted the Sum(TotalDue) here and there's nothing down here that immediately shows me how to show it up in a different format like currency. But that exists. It's up here on the ribbon. You'll notice that every time I highlight somewhere there is a dropdown here. That's set to Default normally but you can change it. So I can just select that cell and say I want this to be Currency. Just to prove that it works we'll click the Run button. We don't have to save it backup to the server yet; we just need to test it.
And that looks correct. Okay, what else can we do? Well clicking back on the Design what I'd like to do is kind of combine these two cells into one. Now there is a couple of things I can do here. I can drag across and sometimes it's little tricky to drag and get both of them and then I can click right- click and get Merge Cells. The problem is that's kind of collapsing them into the first one. So now that all I've got here is first name and it wasn't quite what I was after but we take it step by step.
Here's how the cells knows its value. If you go over to the Property section there is a Value area, which says "Well right now I'm drawing my information from Fields!FirstName.Value," but if I click the drop-down there is little Expression Builder. This is fairly simple stuff, but what it allows you to do is click through some of the built-in fields that it knows about, Page Number, Page Name, Language, Parameters. You got to section which is all the fields that we declared in DataSet 1 which include First Name, Last Name, Email Address and so on.
We do have some common functions for Financial and Inspection and Math and a few basic operators for Comparison and Concatenation. That's what I want to do is Concatenation. I want to join FirstName and LastName together. I could have done that in the SQL statement but it's a little too late now, so let's just do it here. So I can actually use either the ampersand or the plus sign. I do want to separate them by a space but we're not in SQL anymore, so what I need to do is use the " " & and then I can just use the field in Data Set 1.
I could either manually type it or double-click LastName and now it's concatenating both of these together. Click OK and that should evaluate to the full name. That means that up here on the header I should see if I can join these two together as well. Again, sometimes a little difficult to grab hold of them but if you persist you'll get there. Once both of them are selected I'll right-click and say Merge Cells and we'll just change that to say Name. Run and we're starting to look a little better.
We're starting to experiment with the formatting and make this a bit more presentable, and when I'm done with my formatting changes, I'll simply hit the Save button to save it back up to the Report Server. We run it again from there and we can see the changes that we made.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 86726 Viewers
80 Video lessons · 136010 Viewers
59 Video lessons · 54738 Viewers
52 Video lessons · 68633 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
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.