Reporting Services (SSRS) Part 10 - Gauges

welcome to this wise ell tutorial in this session we're going to teach you all about using gauges in Microsoft sequel server reporting services we'll start by showing you the basics of working with gauges including how to add on to a table talk to about scales ranges and pointers the various component parts that go into building up the gauge and then how you can link a pointer to a field in a data set when we've covered that we'll move on and show you all the various ways the modified gauges so I'll show you how to edit them before Matic age including how to add a conditional formatting we'll talk to you about how to add extra items to a gauge and then how to modify those and finally how to position items in a gauge which is one of the most tricky things you'll do in reporting services so let's get started in reporting services a gauge is really just a completely over-the-top way of displaying a set of numbers in this example we've got a radial gauge which displays the film's Oscar nominations against its Oscar wins on the scale there's either a green line or a red line which indicates how many films how many Oscars a film was nominated for and the point I the green or orange indicates how many Oscars that film won the scale and the pointer turned green when the number of nominations is met by the number of Oscar wins so whether down list but films haven't won as many Oscars as they were nominated for the scale is red and the point was orange in another example we've got a linear gauge and this one's designed to show us how long a film lasts in minutes it's very straightforward basic bar graph the point of this time there are two pointers in this example but they're designed to change color based on hitting certain thresholds so a film is less than 100 minutes long it's a green pointer where it's between 100 and 150 minutes it's an orange one and then any more than 150 is a red pointer I'm going to show you how to create both those two types of gauges in this video so for our first example we're going to show you how to create the radial gage which compares Oscar nominations against Oscar wins we've already set up the basic table to include all the data the next step is to add a gage to this blank cell in the table we're going to do that by using the toolbox and you'll find the gage tool sitting down towards the bottom of the list and all we're going to do is drag a cage into that blank cell on the table when you release the mouse button you'll be provided with a list of options these are all the various cages you can select from we're going to go with a basic standard rate your Gage and I'm going to choose okay and then the next thing you'll almost certainly want to do is to increase the width of the column and definitely the height of the roam so as you see and work on the gage a little bit more easily once you've inserted the gate into the table the next step is to choose which field the pointer will indicate so I'd like my orange arrow here at some point of the film's Oscar wins value to do that I need to select the cell containing the Gage and then I need to click once again to select the gage itself that will expose the gage data panel and I'll get a list on here of all of the pointers a gage you can have more than one pointer here we only have one and what it says unspecified I'm gonna seal that which field I'd like that pointer to indicate clicking on the drop down arrow I can choose film Oscar wins I'll always attempt to aggregate that feel so by default for numerical data it'll try to sum the sum of the fields we can modify that later if we need to but at this point if I simply preview the reports I should be able to see that the pointer is pointing and the Oscar wins value the next step now is to control all of the other things that I can modify about this cage which is quite a lot of effort the first thing that I'd like to modify about my gauge is this little red line area which is referred to as a range a range is used to indicate an important part on the scale of a cage I'd like mine's room from zero to the same value as the Oscar nominations field so to do that I'm gonna go back to the design view and then I need to select the path of the gauge that I'd like to change you can see at the moment that I've got the pointer selected it has this little border all the way around the outside of the add the orange arrow to select the range I simply need to click on it when I've selected the object I can right click and then find the option that refers to its properties when I clear this option I'll see the properties dialog box now for a range on the general tab I can choose what value the range starts at I'm gonna start it at zero I'm going to end it at the value of one of my fields I can do that either using the expression builder with it but with the FX button or I can simply select from the drop-down list some of film Oscar nominations you can gauge always try to tries to aggregate a numerical data because we're looking at a single row at a time deprecated isn't important we can just select that option and it will show the appropriate value for each record if I choose okay and then I preview the report again for some of our records it's a little bit tricky to see the information but I can just about make out the end of the range sitting below the pointer I scroll into a different page there we go we can see the range sitting between the value of zero and the Oscar nominations it's a bit disappointing that the range is kind of obscured by our pointer in most of the records so what we're going to do to tidy that up is going to change the position at the range so that it sits on top of the scale I'm also going to make sure that it doesn't increase in width as it gets further round the scale so to do that we need to go back to the design view and making sure that the range is selected right click on it and choose to view its properties again we can change all of these options from the general tab so at the moment the range is placed inside the scale at a distance of thirty percent from it I'm gonna make sure that the range sits on top of the scale so I'm using the option called cross and I like the distance from the from the scale to be zero and what's going to make sure that it's slightly narrower than it is at the moment so I'm going to change its start with from fifteen to ten percent and I'm going to make sure that it doesn't increase him with as it goes around so I'm gonna make sure its end width is the same as a start ten percent if I click OK you can see the preview already shifts to indicate the basics the the basic preview but if I ask you want to see how the data is represented I need to go back to the preview itself I can see now that I can clearly see the range for every film another thing that will really help us to see the range and the value that the pointer is indicating is if we change the scale so instead of going from 0 to 100 we can go from zero to a much smaller value say 20 for instance to change that I need to go back to the design view and I need to make sure that I have the scale selected I click onto the scale or on to one of the numbers I can see now that the border is around the scale if I right click then I should be able to view the scale properties similar dialog box to the range properties in that I get a general tab and various other tabs but here I can change the the minimum and maximum value of the scale if I change it to 20 and then choose ok and preview the results I can see that I get a much shorter scale which makes it much easier to see what values my pointer is indicating another useful thing that I can do with the scale is control how often the digits appear around it it's by default they've been shown every two digits I'd like them to appear every one in this example I can head back to the design view making sure I have the scale selected right click on it and choose scale properties on the general tab there's an option called interval which is set to auto if I want my digits to appear for every value of one I simply type in the number 1 click OK and then preview the report one more time and I'll now see I get the value for every whole number from 0 to 20 there are lots and lots of options available for modifying the way the pointer appears the main one that I'd like to show you is how to control the color in a conditional way so I'd like the arrow to change color from orange to green if a film wins the same number of Oscars as it was nominated for so to do that I can head back to the design view select the pointer right click on it and then choose point of properties now the pointer options tab itself has lots and lots of options available including the ability to change the type of pointer and then change the style just to quickly show you the sheer number of options available every one of those has an option for changing the size and position of it but what we're really really interested in is the pointer fill if I select that tab I can see that at the moment the pointer is shaded using a gradient pattern from white to orange what I'm going to do is I'm going to calculate the secondary color here by clicking the FX button I'm gonna delete what's in the expression builder dialog box so far have you seen some of our previous videos you'll be very familiar with the expression builder dialog box by now we're going to use from the common functions category the program flow section we're going to use a good old favorite if function I double click if you'll type in the syntax of that friend gym for me what I then like to do is compare whether or not me films Oscar nominations is the same as the wins I can do that I can insert the fields at least using the fields category here if I double click one of my two Oscar vales film Oscar nominations sorry wins I can then type in an equals sign and then double click the other Oscar feel this time nominee so I'm comparing if the Oscar wins is the same as the nominations I can type in a comma at that point and all I need to do now is select which color I want to use I can do that from the constants category I'm gonna select this lime green color just by clicking on it then I can type in another comma and the color that I'd like the the pointer to be if the wins is not the same as the nominations I'll go back to using that standard orange color like close around brackets at that stage and then choose okay choose okay again now in the design view the pointer will have pairs have no color or just a pale gray color if I go back to a preview however the color should be calculated correctly for each record so Jurassic Park has the same wins and nominations as a green arrow spider-man doesn't meet the nominations so has an orange arrow for the next example we're going to use a different type of gauge to show the film's running time in minutes so we already have the basic outline of the table setup what we're going to do next is drop a gauge from the toolbox into the next blank table cell this time I'm going to choose a linear gauge and I'm gonna use the one called bullet graph Isis alright it's hit and then click OK I lost something also need them to increase the width of the column and the height of the row so we can see the gauge bit more clearly so you can see it's quite different to the original radial gauge but it still composed of the same pieces if you like there's an outer gate panel there is a pointer the orange bar actually there are two pointers in this example this is the orange bar and the orange line there's a scale which in this case is shown as a percentage and there are several ranges as well the dark gray middle gray and light gray areas so that this Gator is quite different at the original one is still composed of the same parts and he's still treated and modified in the same way so the first thing that I want to do is make sure the pointers are pointing at the correct value so I need to do that by selecting the gauge and then selecting the gain the points within it the gauge data panel appears and it shows me here actually that there are two different objects or two different points is associated with with this gauge I'm gonna set both of them to refer to the film run time in minutes feels you can see the linear pointer one when that selected refers to the bar I select linear point two he's chosen either that's the orange line I'm gonna set this one to refer to the film's running time in minutes as well the maximum I'm gonna do is make sure the scale is big enough to display the the total value at the film's running time in minutes so I'm going to do that by selecting the scale right clicking on it and choosing scale properties on the dialog box I'm going to set the minimum from zero to zero and the maximum to 250 and what's going to make sure that the the gauge the numbers aren't formatted as a percentage so in order to do that I can head to the number tab and instead of using a custom percentage symbol I'm gonna go for the default format which will just display the numbers as numbers if I then select okay and then have a look at the preview I'll be able to see already the my gauge is showing the correct values one small downside of having extended the scale of my cage is that the range is no longer extend all the way to the end so what I'm going to do is add a couple of extra Rangers and also modify where the existing ones sit I'd like one range to appear for every set of 50 units on the scale so to do that I'm going to go back to the design view and I'm going to start by adding in an extra range to add anything to an existing gauge you need to select the cell containing the gauge and then again the gauge panel itself make sure the gauge data panel appears next to the gauge next to the table cell if I right click then into the background of the gauge I see a list of all of the items that I can add to this existing gauge I can add an extra scale if I wanted to another pointer or three and the one that I'm going to choose in a moment which is another range but I can also add things I can actually gauge within this one's like an embed one gauge within another things get rapidly quite messy of you do things like this so I'm gonna stick with a fairly simple example and simply choose to add a range now that I've added the range I need to modify his properties so that it sits in the appropriate place so you can see that at the moment it's sitting at C slightly above the scale where I see the ones are sitting on top of it and obviously it's in the wrong position I'll try and shift it so that it says right at the end between 200 and 250 units to modify those things I need to right click and then modify the range properties I can set a starting point at the value of 200 and I can 37-point and 250 I also want to make sure that his placement isn't inside this scale it's actually outside our Sobecki pardon it's actually crossing the scale so it'll sit in line with the other ranges that are already there finally and it's simply going to change the fill color so that it has a solid fill which actually quite a light to go I'm going for white in fact I choose okay I'm now seeing that range sits right at the end between 200 and 250 all I need to do now is modify the other existing ranges by right-clicking on them and choosing to modify their properties so I'll start each one selling a union spanning a set of 50 units and again the next one and finally I'll make the final one span a hundred units zero to a hundred so it fills up the entire bar I look at the preview now so you've got a nice effect for each set of 50 units is divided in the different color just like with the radial pointer from earlier on we can apply conditional formatting to this gauge so we can see the air that the sort of ranges of numbers that are indicated I'm going to change it so that the points at the orange bar in the orange line is in green if the film running time in minutes is less than 100 and then it will be orange if it's between 100 and 150 and any more than a little term red so to do that we're going to head back to the design view and I need to make sure that I've selected the orange bar part of the gauge so I select my can there we go the the point of one and a slightly easier way to do it would have been to actually select it from the gauge data panel I can then right click onto the bar and choose point of properties I'm gonna head to the point of fill tab and then I'm gonna change the secondary color from orange I'm gonna use an expression to build this so I click the FX button to launch the expression builder I'm going to remove whatever is in there at the moment and then I'm going to use the function called switch so I'll find it in the common functions list in the program flow category I can double click the switch function to type in the basic syntax all I need to do now is write pairs of arguments which would be a logical test and then what color I want to achieve if that logical test returns true so each test will be of this form I'm gonna check first of all if the film run time minutes is less than 100 then in comma and if that's true I like the color to be green so we can select that from the constants category here if I type in another comma I can check if the Filmland time minutes is less than 150 followed by a comma and if that's true I want to show orange finally for anything else I can simply type in the word true followed by another comma and then the color I'd like those films to be it's basically anything that's 150 minutes or more if I close around brackets because I'd like to make sure this condition also applies to the to the second pointer I'm gonna copy this to the clipboard at this point if I then select ok and ok again you'll see that the pointer turns gray on this on this designed you will generate the right color in preview I'm quickly going to select a linear pointer to as well although the sensible way this time by selecting it from the gauge data panel and I can also launch its properties using this little button in the top left hand corner I guess at the point of fill tab find the FX button next to the secondary color and then replace whatever's in the dialog box so far with what I had copied to the clipboard earlier on so that's okay so that's okay again I'm gonna make the the row a little less tall so you can hopefully see more gauges in one screen and if I preview the report at this point there we go I would see a nice combination of films with different grades of running times and one more thing that I can add to my gauge is called label an acid expert it allows me to add descriptive text to the gauge itself I'm going to add two to my gauge one that will sit roughly in the top left-hand corner which displays the film's name and another one's was a top right-hand corner which shows the film's running time in minutes this is one of the most fiddly awkward things you can actually do with a with a with a gauge particularly when you're trying to get your labels positioned accurately so I head back to the design view I'm going to increase the height of my row to make it a little bit clearer to see maybe the width of the the column as well and what I need to make sure I've done is selected the gauge itself if I click onto the gauge and then once again to select the gauge itself make sure the gauge data panel appears I can right click into the background and choose to add a label now first of all I'm going to move the label so that it sits in the top left-hand corner of the gauge itself if I right click onto this label I can choose to view its properties and the several options that you'll need to consider when you're positioning a label the anchor option basically specifies which object within this cell width is nestled on the table the label takes its cues from to be positioned so says it's taking its cues from linear gauge 1 as this gauge actually fills up the entire table cell there will be no difference between choosing that option and the only other available option now what she called none then the positioning of the herb the label itself is taken using a percentage so there's a top value which specifies how far from the top of the anchored option so how far from the top of the gauge is the top of the textbox if I change this to to a value of 0 you can see that the textbox rooms right up to the very very top of the air the gauge panel I also wanted a little bit further down from there so I'm gonna change it to sale as a 10% seed sorry tempers and and that fits slightly more neatly into the gauge panel itself then there's a left property which again was in a very similar way it's the distance the percentage distance from the left-hand edge of the gauge to the left-hand edge of the label I'm going to change that to something like five then I can modify the width and the height of the gauge as for the label in exactly the same way I'm gonna increase the height slightly to save 15% and I'm gonna change the width to fifty so I should give me enough space to display the film's title to actually set the title I can modify the text property here if I use the FX button to launch the expression builder I can replace the word text with a reference to the film name field if I think like okay that will display the film name field in the text box one final thing that I'm going to do is just quickly check the font size property that's on the font tab by default text in the text box or a label is set to auto size so the longer the film name the smaller the text will become I can be quite useful in certain situations but I'd like to fix my font size to a particular size so I'm going to uncheck this box and then change the font size to ten points if I then click OK and preview my report I should be able to see that the film's name appears on the top left hand corner for each gauge so there's one more label that I'd like to add and that's one that will display the film's running time in minutes at the top right hand corner so I'm going to go back to the design view and select the gauge and then right click into the background of the gauge and choose to add another label this time a lot of the properties have been changed for me already so you can see that the position where they wear the next label sets is different where the original one appeared which was in the middle of the gauge I'm going to right click onto this label and choose to view its properties I'm gonna make sure that it's positioning is accurate first of all so I'm going to change it's top property so that it's it's 10% down from the top of the look the gauge just as the previous label does I'm going to change its left property so it begins 75% of the way from the left hand the edge of the gauge finally I'm going to change the width property so that it's 20% of the width this will make sure if I start a 75% from the left-hand edge and the label fills 20% of the width it means that the right-hand edge of this label is 5% from the right-hand edge of the gauge I'm going to modify the text next so that I click the FX button to launch the expression builder and I'll replace the word text with a reference to the film runs eye minutes field I'm also going to join on or concatenate to the end of that the word minutes so I can use the ampersand symbol and then in a set of double quotes I can type in the word minutes I'm deliberately left a space just before the word minutes there if I choose okay I shall quickly check that the font is set to the appropriate size so it's not being auto resized it's set to a size of 10 points and I can also just quickly switch back to the general tab and I'm going to make sure the text is aligned to the right of the text box if I choose okay I can then delete these two columns completely from the from the gauge sorry from the table you know can increase the width of that cell maybe decrease the height ever so slightly and then preview the report to see the effects there we go a useful gauge with dynamic coloring and useful labels to display numerical information graphically if you've enjoyed this training video you can find many more online training resources at www why is alt code UK

Channel: WiseOwlTutorials

Views: 97,391

Keywords: reporting services, ssrs, gauge, pointer, scale, range, label, conditional format, wise owl

Id: 1LeUTpy4l2A

Length: 27min 2sec (1622 seconds)

Published: Wed Aug 22 2012