Create reports in SQL Server Reporting Services (SSRS).
We will develop your skills, bit by bit.
You can also download a free version of SQL Server which will allow you to practice creating reports on your own.
We will design a report, implementing report layouts and add interactivity into your reports.
Instructor: So this gives us a bit of a dilemma, how do we actually change the background colour and the foreground colour? Normally when you open up Excel and so forth, you see all of these tool bars at the top. While there are two bars at the top in this SSRS device, the mo visual studio, none of them seem to be formatting. There's, if I click on this, you can see formatting for the table in terms of whether it's going to have borders and what it's going to have, but there are no colours here. Now the usual way is hidden and it's hidden in the same sense that the toolbox was hidden. It's this properties tab here. So if we click on this and then click on what we want to format, in this case this row, notice that this properties is quite dynamic. It will change to whatever you're clicking. So click first and then go to the properties. Another way of showing the properties pane is by pressing the button F4 on your keyboard. So you'll see that they are currently categorised. Alignment, border, data, fill, font, general, lists, localization, number, other, position, and visibility. What could we use to change the background? It's the fill and we have a background colour here. We could also use a background image as well. So if I click here you will see that there is a dropdown box, so click here, and we have a list of various colours. So let's go for aqua. Now just to show you that there is more to this than meets the eye, if I click on this dropdown box and click on expression, I can click on these colours and you'll notice at the top it actually is in plain English. Now quite often, places use hexadecimal like this to express colours. Here, we have the chance to just use a plain English blue, red, and that will be quite useful as you'll see later on. Now I also want this to be bold. So where do you think bold might be? It's in the font. Now there is a little expansion here that you can see that will enable you to edit the font itself, the size, the style, the weight, and decoration. It's the weight that we want. If you click here you'll see all sorts of different items from thin to bold to extrabold to... I'm not entirely sure why they have all of these options for text because it's either bold, normal, or default. So we'll click on bold. Also notice that the box that came up is resizable. Now if you change something so it's not the normal, in the properties it becomes bold. So for instance, the background colour is not normal. The normal is no colour. By changing it to aqua, it becomes bold. By changing anything about the font, all the font section becomes bold. So this enables you to see at a glance that something is different to the default. So now if I go to preview, there we can see it ina all of its glory. Now I said that this is a normal way of doing it and this gives you the most versatile way of doing it. However, if you right and click up here, we see all of the toolbars possibilities that we've got. One of the possibilities is a second report formatting box. Another one is a formatting. Clicking on either will get you a toolbar that will enable you to go bold or italic or underlined, allowing you to change the foreground, font colour, or the background. But notice that it's a bit more limited in the sense that it didn't have that expression box that we've got down there. We can also change whether it's aligned left, right, or centre and other stuff. So that allows you to do things fairly quickly. It doesn't give you all of the flexibility that these properties will give you and later on, as we get more advanced, you find yourself living in the properties window more and more. But being aware that you can do it using a simple toolbar is quite useful. If we wanted to centre the headings then that's in alignment. You can see the text align is currently default. Now what does default mean? It means that we can apply a style to the entire report. Maybe you'll load this from a template and it says that the default for all headings is italic. So what default would, default mean in this instance, is italic. But if you then change the style, and this was still default, then it would change as well. So if you change the style so headings are no longer italic but normal, then default will then be normal. So if I click here, you can see that we got general, left, centre, and right. So if we change it to centre, they'll be centred. Another thing I want to mention at this stage, apart from the borders, which you can edit if you wish, is in this general can grow, can shrink. Now can grow is the equivalent in Excel of wrap text. So in other words, if the text is too long for the column, it can expand the row so that it fits on and isn't curtailed. Can shrink is a bit like the opposite. You've allowed, say, three rows worth in that one row, but if it doesn't need it, then it can shrink down to the one or two rows that it needs. ToolTip, well, it's easier to show you. So if I go into this cell as opposed to the entire column, and click on ToolTip. So I say this is the product header, go into preview, and if I go over product name, you can see the ToolTip. So it's ToolTip because it's only applied to that one cell is only shown when I hover over that on cell. Also notice that if I highlight a range which has more than one of a particular type, that the properties windows tells us that by going blank. So let me show you what happens if I go for this entire column. If I go down to the general, the ToolTip is now blank. So that's a reminder, perhaps, that one of the cells does have a ToolTip and the others don't. If they all had a ToolTip and they were different, then highlighting this row would also mean that this ToolTip was blank. So just because it's blank, doesn't mean that there's no data there. It just might mean that there's differing data and it doesn't try to present all of the data. It doesn't try to say, this first cell has this ToolTip, this next one doesn't, this next one doesn't. If it's conflicting it will show blank.