MS SQL Reporting Services Tips And Tricks

all right it's three o'clock let's get started got a good crowd here today try to make every minute of it worth it for you this is tips and tricks for building rich report written reports in sequel server 2012 reporting services my name is bob myers i'm a senior program manager on the reporting services team and there are a lot more of you here today than there are of me and i actually can't see some of you very well through this big screen so i apologize you're just going to have to enjoy the screens on the side that's probably better looking anyway so let's get started objective for this session after this you'll be able to design reports that encourage immediate understanding and make insights pop so that's what i'm going to help you help you learn about today i'm going to do four things to try to accomplish that first thing is to talk about how to use the table X to organize data when you're presenting it to in your report then we'll talk about using graphics and colors to emphasize data then we'll spend a little bit of time talking about choosing the right visual to help depending on what you're trying to communicate and finally it's just a handful of additional tricks that I think you'll find handy so we'll we'll get to those near the end so let's get started today's sample data will be about lettuce this is courtesy of the United States Department of Agriculture this is real data it's all about lettuce production in the United States over the last 50 years or so so if you've ever wondered about this today you're going to learn about it alright raise your hand if you like lettuce oh that was a lot of hands I should have picked Twinkies or something I don't know never liked Twinkies I don't know my wife's been real big on lettuce lately so that's what I picked all right so let's get started on how to use the tables to organize data tables is a grid structure that you can use in reporting services to organize and present your data the most common structure of a table it's is a simple table so like any tables this one has rows and columns a bunch of rows a bunch of columns the first row is a kind of a head of road she's got some labels in it and then there's these other rows we call it a row group and they have it all have the same structure as the top one but then it just repeats showing different data values okay so there's a row up top and there's a row group below it the data in each row of this table may correspond to individual rows that were returned from your query or they may be aggregated you can see this example we have one row for Arizona and it's showing totals sums or averages or something from multiple rows in the query results so that's pretty straightforward so let me just demo that real quick we have a report builder there is my US lettuce production in the 1960s we'll just look at that decade here's my connection to my lettuce data source and here's my data set I've got a bunch of data about lettuce here so I'm going to do things the old-fashioned way you can use the wizard if you want but I want to make sure everybody here knows have this a 300 level course I want everybody here to know how to do it this way and because there's a lot more options and flexibility once you learn how to work with it directly so here's a table and I'm just going to drag in state and then reduction now before we even try to run this let me just take a look and see what happened here so you got a first the first row here it's just got some labels in it then the second row has this orange bracket on it right this is a row group and what's it showing it's showing details you can see down here that of the row groups it says this is going to show details and this little three dash icon so that means it means you're going to see every row and the result in the result query results here so let's go ahead and run it and waiting that's exactly what we get a bunch of rows about Arizona showing production data from different years okay so we didn't actually want to see all the individuals we wanted to aggregate this so let's look at how to do that I'm going to right click up here and delete this column that has stayed in it and I'm instead of dragging state into the table I'm going to drag it down here the row groups and create a group outside of the details here so now first thing you notice is that there's a new thing down here called state and that there's an extra bracket out here now and you can see there's a kind of stack of stuff going growing out here there's a bracket around it and then the details on the inside you can see that there's two of those brackets in here there's also this freeway kind of dotted lines here that's because there's two different areas of the table looks that we're working with here the right side where we started is the body of the table X and on the left side is what we call the row grouping area so over here you get some interesting things you can do and I'll show you that right now this is a spanning cell that will end up spanning all the rows that come back for that inside that row group value so all the rows for Arizona get one cell that spans spans if that's the layout and the look that you want then this is what you use the row grouping area to do that so for my next trick let's um we saw it was spanning they're trying to get rid of the details right so let's let's go down to this details thing and just delete it now we don't want to delete any of the rows and columns we just want to get rid of the group itself so you see that little bracket just went away right there and it's not it's gone down here so now when I run it I've got one row per state now that's good except my production values is that that's not being aggregated at all yet it's still just it's actually just picking the first value which isn't what we want either so I'll click right there and right click on the placeholder this placeholder that's there for the data values I'm going to change it to summarize by sum now you can see it says some production instead of production so now when I run it we get what we're looking for here production summary production across the different states so what if we wanted to have multiple levels of these grow groups we could actually just add in year here drag it down right below state and it would get inserted you see where the freeway lines move to it got inserted into the row grouping area and you also see the bracket is and now when we run it it's going to break out this the production by year within each state and what if we wanted to see the totals well that's pretty easy too we could just right click here on this year thing and say add a total or you could go down here do the same thing add a total there it is there's will get totals for each state across that across the whole decade of the 1960's so that's just a little bit about how to work with the tables how to work with the tables when you're just creating a simple table so let's go back to presentation here and talk about what else you can do it at a box well table tables is also a matrix and this matrix we see it has the same structure same row structure as the table we were just looking at but now it has something analogous on the column area there's a fixed column and then at this row group that repeats for each of the years in this case we one column for each here but that's just the beginning you're not restricted to this particular structure you can just as easily add more columns more column groups on either side or in between and build out the structure that you want for instance we could just add three more columns in here to the left side of the column group and now you've got something that's a combination of a table in a matrix so pretty much any grid like structure that you can imagine you can create with the table it's just by adding rows and columns and paying attention to where you have your row groups to find your groups to find it so let me show you how to do that hold on just a second all right so I'm building matrix I'm just going to delete this table here and start over again you see when I insert a matrix now there's no details thing anymore because matrix by default is just going to sue mure trying to aggregate so that's a quick way to get things started that way if that's what you're going to do I'm going to drag State in here on the roads see States those up down there and we'll drive year over here to columns then I'm going to grab production put it in the data in the body you can see those freeway lines going both ways and now I have a matrix let's just make this bold just for fun and we'll run it and see what we get there it is broken out by state on rows and by year on columns you can see it's providing all the production values in this big grid actually going to make this column a little narrower because if we don't really need it to be quite that wide okay now just to prove that there's not really needed magic going on here when you start with a matrix I'm going to delete this one and recreate the exact thing from my table I'm just going to undo back back back back back to my beautiful table remember how we made this there was nothing going on in columns here right so I'm going to delete this right here because we're going to put that somewhere else yeah you can go ahead and leave this at two I believe this total row here that I had and then I'm just going to drag year down over here I've got a year group now my labels are kind of in there funny places so I'm just going to control X cut that paste it up there delete that delete this column so you're just working with the grid adding rows and columns deleting rows and columns and now voila I'm back to my matrix so I just made that out of a table so it's the same thing you're always working with the same thing no matter which one you start from and you can always have all the the options there that you're looking for so let's add a few things to this I'm going to add dragon planted right here say I wanted to deal with those extra columns I was describing if I add this right here what it's going to do is create two columns that will repeat together underneath this column group that's not actually what I want I just wanted to by itself right so let me undo that what I'm going to do is insert a column outside the group to the left okay now you can see that it's not inside the bracket right so this is just alright itself now I can go here don't know if you don't I'll use this little guy it's very handy just pick a field from it and put in planted now you see it it added the aggregation by default because we're not showing details here at all so when you add in a numeric field it'll just automatically summit just because it assumes that's probably what you're trying to do so let's add another column in here let's just go ahead and drag it in this time and this is harvested what if I wanted to do a little ratio let me show you how to do that so let's make this harvested percent this is the amount number of acres that were harvested in that state relative to what was planted so they might plant a hundred acres and harvest 90-93 right so let's right click here and go to this expression and all we got to do is just divide by something else but just to be safe I will use my cheat sheet and paste it in some of harvested / some have planted and now since that's a percentage value I'm going to hit the % button to display it as such I would like an extra decimal point please and let's run that so you can see the different states have different ratios of how much they harvest add relative to how much they plant that could be due to weather or you know bugs pests something like that but there's definitely some variation there let's add another column here this one I'm just I'm going to add yield in and let's look at I'm sorry let's look at the I'm going to add production and again and this time I'm going to do a percent of the total this is always a common thing people like to do and this is really easy to do and reporting services so go down here and modify this expression this one's even easier what you do is divide this little formula by itself except the second one you want the total at a higher scope and in this case you name the scope which is table looks 1 that's the I want to grant this percent of total across the whole table looks that I'm billing so that's the name of my scope now when we do that again this should be a percent we can scrunch that down a little bit let's run that and see what we get all right now we're seeing something interesting looks like California in the 1960s at least produce 61 percent of the lettuce that was consumed in the United States Arizona is down at 23 and everybody else is under 3% looks like so back then we got an awful lot of lettuce out of California turns out that's California in Arizona about the only places that produce it and now more recently but back then they were there a good 10 15 states that would produce lettuce okay so that's it for building matrix and a little bit about creating some simple formulas that are really common and useful let's go on now and talk about the next thing let's talk about how to use graphics and colors to emphasize data now just organizing and presenting it at one thing but helping people get out of it what they want them to get out of it is another thing so here's our beautiful table that we just created I'm going to use this to illustrate a number of tips and tricks that can help you emphasize the data and make insights really pop so as after we've talked through them that we'll go back and demonstrate how to do each one of them so we're going to go for a little bit I'll just talk about them first so the first thing we want to do is just get rid of the fluff that's rule number one this thing's having trouble when I start using something else to move the next slide no fluff right gets in the way but what the people you really want people to focus on is the data so some things that I've done here is use the thin font for the labels to keep things clean I'm using spaces space instead of lines to separate things space is actually a very good visual separator and that it's much cleaner than using lines so for the most part you can just get rid of all the lines just make sure you have enough space I did the labels because the labels are just for reference and I mostly want people to look at the data and I'm only using one accent color I could these lots of color for lots of things but I'm going to keep it simple and not do that the primary reason is not that I hate color it's because we want to use color later too to help make those insights jump out the screen for us okay so suppose there was a rule about harvested percent that said anything over 95 percent is good and anything from 90 to 95 is okay anything below 90 was bad okay so how could we visualize that this is one way to do it and these little colored circles are called indicators and they help you visualize a rule so some of the things to remember about visual visualizing rules is stick to the simple icons there's a lot of choices of what icons to use but the simple one these color like colored circles are usually the best so I generally recommend that now suppose you don't want to visualize an actual rule that people are aware of already and want to see visualize on your on your report suppose you just want to convey the position that value has along the spectrum from good to bad to do this you can use a color scale so color scales are similar to indicators but the color changes gradually instead of abruptly at specific predefined thresholds so here I've applied a color scale to a small rectangle next to the numeric value and an easy way to do this is using the code from the link that you can't read at the bottom of screen but if you download the PowerPoint you will be able to read it and you even click on it and go and get that code it's just a little bit of code you can paste into your report and it lets you do color scales so some tips to help you do it oh just for example this is what you end up putting in your your formula to say to create the color scale value so things to remember is keep the area small sometimes you'll see people use a lot of area for the color but really doesn't take much to convey the meaning so keep it small like this sometimes you'll see a color apply like this to the whole cell and that's okay but just remember that it tends to dominate the overall visual so you got to have a reason for doing that this approach is most often used over a large area of a table to create what we call a heat map so this is an example of applying a color scale to the whole matrix side of this table and you can see how it makes trends and outliers and other things just really jump out a heatmap can be very effective a third option is to apply the colors to the values themselves so you see the numbers are now colored instead of the background behind them too our quick tips that will be relevant later as well remember to use wider colors for fills and darker colors for lines and text they may have thought I was using the same reds and greens here but I wasn't when I was using fills they were lighter colors and when I color the text here it's actually darker red darker yellow dr. darker green okay enough about color let's talk about graphics you can use oh no not enough black color this is just an example of bad bad usage of color you can see that if the background is too dark then you can't read the text or if the text is too light then you can't read the text so you got to be careful with those fills in and lines okay then we're enough about color let's talk about graphics right so this table has a lot of data in it and it's it can be intimidating to consume and to try to find what's interesting in there the insights aren't really just jumping out at you immediately are they so there are some ways that we can do this the heat map can help with that another excellent approach is to use what we call a spark line so this is a spark line and you can see that's the that's exact same data just presented visually in a different way and you can see how immediately you can you can see trends and movement over time and compare one state to another it's very incredibly dense and very effective visual a nice addition when you're doing this is to make the sparkline expandable so you can see all those individual values if somebody wants to look drill into it it's also nice to throw it in a total or an average on the far side just to to give you a feel for the overall number so some tips if you're showing data over time you should definitely consider using a sparkline that's what they're great up I can use simple colors grades are usually just fine make them expandable as long as you're doing an interactive report people going to be consuming interactively this is a great option should think about whether you want to align the vertical axis or not in this if you uh if you do align them then the people visually will be able to compare the magnitude of these little spark lines and not just the trends in movement over time in this case I actually did not choose to align the vertical axes so each little chart has a different scale and that's because California and Arizona are so much bigger than all the rest of them that if they all have the same scale the rest of them would just be little flat lines and you couldn't see anything right so this you can choose with a sparkle I'm do it or don't do it but just think about it and decide which one makes a little sense for the for that what you're doing so let's look at another type of graphic called a data bar I'm going to do this I've added a new column called yield which indicates how many hundreds of pounds of lettuce are produced per acre in that state underneath each value is a small bar that shows the magnitude visually notice how quickly you can scan through and compare the values for each state data bars are really good at this I also took the liberty of using a color scale for the bar itself this isn't showing anything new it's the same it's colored based on the same value that the bar is our length is tied to but it just helps to reinforce not just the magnitude of the value but whether it's good or bad so I have this dark reach green color for high yields and in California and Colorado and I have this sickly yellow color for Florida which for some reason down here lettuce doesn't you get less than half the lettuce out of an acre than you do in some of these other states now an insight that kind of popped out to me when I looked at this one was how did Florida compete in this market when they're getting less than half the lettuce out of an acre that these other states were getting I have no idea but it's certainly an interesting thing to go look into so some things to remember with data bars consider using color like I did to help reinforce the meaning of the data not just the size or the magnitude of it data bars always have their axes aligned within my default and so it's important in this case data bars don't make any sense if you don't have this so don't turn it off they need to be aligned all the way down so that the comparison is meaningful there's also the option to use a stacked or or 100% stacked data bars which are well so it can show you multiple data points data values within a single visual and even those can be really great to show like ratios or mix you know as almost like a little slider between you know certain values so here's another visual you can consider using I'm not sure if there's a name for them but I call them SPARC pies right there to the right side of the percent of total I have a simple purpose they do straight contribution to the whole or progress toward a target or something like that they're very easy to scan visually and they're effective at that that one thing that they do so a couple of things to remember when using these little spark pies is again use simple colors same as same advices for the other visuals and you'll probably want to tweak them so that they start at 12 o'clock or straight up because by default pie charts always start at you know straight out to the right and you have to tweak something I'll show you how to do that so this table structure is great for comparing the states to each other because you can you can look at the values for each column run down the down the column and see how they change for different states but what if that wasn't really the goal of your report what if your goal was to help people focus on a single state and look at all the metrics there and just think about that for a minute not so much comparison between States a card is what we call a card structure might actually be more appropriate for that and this is still at a box except it's very easy to create something like this you just kind of add some more rows and move things around and and I'll show you how to do that but it's what it gives you is that kind of one-stop compact everything you want to know about X kind of visual in this case everything you want to know about lettuce production in a particular state so a few things to remember when using this card a card structure it's really helpful to use a picture of some kind to strengthen the visual identity of each card here I use the map that's auto centered - on the state that we're showing and that helps visually identify the state also use what I call butterfly alignment you can see that that the labels and the values are the labels are right aligned and the values are left aligned this keeps them tight together and it helps people navigate visually this the all the values on the card it's not so important to keep numbers right aligned like you usually do here because the ones next to each other aren't really comparable anyway so it's usually better to use this kind of butterfly look use some extra space between the between the cars to help people see the separation and again an accent color is nice here but only use one and if you want it if you need to do some light and dark variations of it just mess with the saturation or the lightness but don't change the hue just leave it on the same same color value there so I'll show you how to do that too another final example of how to emphasize data is to go black some people love this some people hate it anyway try to figure out who's going to look at your report and decide which one which kind of person they are but you can tell you one can't say one thing the data certainly jumps off the screen when you when you do this see somebody shaking their head eyes no no no yeah it might be bad to print this I don't know what happened if you did that but might get a bill for the ink or something all right so let's show you how to do all this stuff I get my mouse pointer over here okay so here's our basic table so the first thing we're going to do is get rid of the fluff right so I'm going to select all my rows and say I do not want any borders we don't like borders anymore except we're just going to get rid of bold here right instead we're going to do that we're going to put one border I'm going to choose cornflower blue here is my accent color and I'll put one border just along the bottom of the hetero and then let's also make dim the labels like I said we should right I'm going to make these gray and that's about it there we got rid of all the fluff I need to pull that in again that comes way too wide okay much better so what about indicators status indicators let's do that for harvested remember you said that rule was that when you're visualizing a rule this one was example one was 90 95 and 100 right are the boundaries so what I'm going to do is insert a column to the right and then I'm going to go down to this cell right here and sway down here insert indicator and here's all my choices that I said you should mostly ignore and just choose this one right here and you can shrink that way down now make a nice little dot when you click on that thing you can see it doesn't have a data value yet so we're going to pick something we're going to pick harvested except we need to mess with it because it's not exactly harvest we want percent harvested so I need to go in and do that same same same formula again see I'm really good at typing and then we need to set up with rules so I'll click on those properties here and go to values and states now these aren't going to be based on percentages of the set that I'm looking at it's just hard fast rules that are predefined so I want to choose numeric as the measurement unit I'm just going to put in some values here so from 0 to 0.9 will be our read from point nine two point nine five will be our yellow and from point nine five up to one will be our green and that is it oh there's one more thing comes with a border got to get rid of it all right tada there you go that's how you added indicator to your report so now let's go back and scratch that and put in a color scale instead so the way I'm going to do that is actually I'll just delete the indicator itself right there I'm going to put in a little rectangle here and I'm going to add another one out here this is the one I'm just going to drop inside my little little square is going to be have the color to it okay make that really narrow there and then I can just pick that up and drop it should fit right in there control arrow to move it up a little bit line it up with the text okay now here comes the fun part how do you do these color scales well you have to add a little bit of code so you see where I clicked out there you have to click out here in the empty space to get this report properties thing and go here to your code I don't have any code in here right now but we can fix that I will go and get some code this is the code that is available on the on the blog that I have linked in the slide deck I'll show you what's in here a bunch of color scale something values there's so much basic one some basic ones color scale in college gale three they give you two value and three value scales based on colors that you pick and there's a bunch of predefined one that actually match the set that you get in Excel 2010 when you do similar kind of conditional formatting so here's for your handy use is the assembly reference that you need to add so that can recognize color names you just add an assembly reference and paste that in and that's that now what we can do here now we've got that code is we can go to the properties of this little rectangle we added and make the color the fill color be an expression that expression that I'm going to use is this so I'm going to use the red yellow green function I just put in most of this here is just the same value that I told it to use and then I just put in the low and the high values that's it it'll do a radio green color scale between point eight and one point oh and it's going to eat it's going to the value it's going to be based on is this percentage harvested percentage that we have shown here now unfortunately it's not going to show anything interesting here but when you run it it will tada okay so that's how you color scales you can use create these little rectangle any cares now if I go ahead and I'm going to delete this now and just color the format the background of this one instead I'm sorry I painted that I need to go to the text box properties and change the fill color here I could paste in the same expression or because I happen to be on a text box that has is already showing this value it's even easier I can just do meetup value so you can't be hard can't be much easier than that and now when I run it you'll see that the text box about text box itself has the color scale as the background color so it said that was kind of a unusual thing to do unless you're doing a heatmap eye but you can see that the heat map you just do the same thing over in this over this one over here you could use the red white blue like I did in the in the picture in the slide deck or some other colors that you felt were helpful for conveying the meaning you want to convey so I'm going to show you though here how to instead of using let me change this background color back to nothing and I'm going to color the text this time so I click on the little placeholder here and go to placeholder properties and I'm going to change the font color we're over here in this case I am going to use a different color scale because remember when we're using text and lines I don't know why I did that sorry we're using text and lines we need to use darker colors than the defaults that we use for fills otherwise it won't be able to read it very well so this is my new and improved I'm going to use the color scale 3 function and just pass in the colors I want and just plain old red orange and green actually work fine for for text so now when I run it the text is colored it almost looks yellow doesn't it orange of thin stripe of orange looks pretty yellow and it actually works just fine even though people are thinking mentally yellow it's actually an orange color right okay so that's enough about color scales let's do a sparkline so I'm going to insert a column to the right here I'm going to go into the cell and say insert a sparkline and I'm going to choose this one I like these that have the area underneath them that's if if you want the magnitude to be interesting at all if the magnitude is interesting at all the area usually makes it better actually let's just call it trend okay I'm going to show the trend over time it got a border let's get rid of it let's put some data in here it's going to show production values and it's going to that it's going to group by ear so it's going to show the production by a year over time right so I just click on here and choose a year and then you got to make sure that our fill and line colors are what we want I'm going to go in here to the series properties and make my fill color water spills supposed to be light or dark fails are supposed to be light okay so we're gonna go is light gray and our borders are supposed to be dark so we're going to use regular gray all right there is a beautiful sparkling now you might be tempted to make these big but they actually are almost just as effective with they can be very effective even a very small space so don't be afraid to scrunch your spark lines there they are now we said it was fun to make these expandable if you have an interactive report that people are going to be viewing you know in a browser or something so let's go ahead and do that what I'm going to do is add a row above here outside the group this is just going to have a text box that's going to have the little toggle button in it so I'm going to call this show details and then we have to the way we're going to make this into the toggle for the group we have to know what the text box name is so I'm going to give it a good name text box 44 is not a good name so I'm going to call it show details and then what we're going to toggle with that is this year group so what I'm going to show and hide it right so I'm going to go over to the group properties go to visibility and say I want to hide initially how did it initially and toggle it using the show details cell okay now there's actually this doesn't fit and I want to make it span across there so what I'm going to do and I said this is a good idea to do anyway is to go ahead and add a out of total for this and here it's it doesn't know what to do unless you say row or column because it's you can see those orange highlights the little brackets it's actually in a rope scope and a column scope so doesn't know what totally one if the cheese one and here we want to call them total so that's what it does for us we're going to call this a production and change that placeholder to summarize using average and then I'm going to make sure I can merge all these cells across so I can get to see all that text all right let's try that tada oh I need to format that number that's kind of ugly great so here's the little Expando button now you can see the details can be you can show and hide the details by clicking that little toggle alright so I got it because I copied this inserted a wrote copy the formatting of it and I didn't actually want it to copy that line so I'm just going to get rid of that okay so that's a sparkline and also the expandable sparkline so let's show a data bar we did a data bar we're going to add that yield field here I'm going to stick it right in here and I want this to be an average average yield per year and now I'm going to show you a bug here I usually don't demo bugs on stage but I'm going to show you the bug so you know how to get around it okay so what I was thinking of doing was inserting a row inside the state group below the current row and sticking my little data bar right in there right insert data bar okay there's my data bar and I want it to be a little sliver of a thing so I'm going to scrunch it way down here and then I'm just going to copy it's going to have the same exact values this other one so I'm just going to copy that expression and add it oops got to select the going to go this expression right here just paste it in okay so remember it's going to synchronize the axis by default so you don't have to mess with that I have to do is just tell it what value to show and off it goes except you can see that it didn't make it skinny for some reason and that is a known rendering bug and we apologize profusely for it but I'm going to show you how to get around it I'm just going to cop cut this text box out and replace it with a rectangle and then paste it right back into the rectangle now what that does is it's going to allow me to put more stuff in that cell see that one's just staying right there it's not expanding with the rest of it and that other stuff I'm going to put in is this data bar so I'm going to copy and paste that in there maybe a little control arrow to Snug it up a little tight there and then should be able to shrink that back down and get rid of this other row okay now this time we should get a nice skinny databar and we do accept Oh nobody told me that I wasn't putting it on the right column you guys aren't helping me out here it's supposed to be the yield cone don't you guys know what my demo is supposed to be I better fix it I'll be fast let's see I do the same trick here we're going to need it later so that's why I'm fixing it insert the rectangle page that bad I can grab it out of here a little more room here and fix that one back up I think I even put the wrong expression into it yeah that was kind of silly I didn't need to do all that so I'll just click here and change it maybe I won't it's too small let's do yield let's do a rich all right there we go right here tuck that right back up and now it should be much better now notice that the data bars naturally go from left to right that's kind of how people like to read them and so it's actually common when you're doing this to change the typical alignment of the of the field that's showing the value and bring it over left aligned as well just makes it a little bit easier to read that things copying the color from the other one we don't actually need it to do that we just want a nice black value right so that's data bars let me show you a spark pie insert a column to the right and what you answer is nothing called spark pie in here but if you just go to spark line you'll see a pie so that's what you pick and there's my beautiful spark I let's drink it down a little bit and we're going to use production except we want to use this percent of total thing so I'm going to copy that expression here delete that and paste in the value I want it to show and we're gonna have a little fun here because all this is it's like a 1 value pie chart right so what is it grouping by it's not grouping by anything so we need we need to get rid of this and what I'm going to do next is add another one it's exactly the same except it's the target value or it's not the target value it's the difference between the first one and the target value so this is percent of total so the target is one so I'm going to do 1 minus the first thing so if I go here and grab my expression I can do that same thing here except put a little 1 minus in front of it and that will be the other part of the little spark pie okay so now we wanted to use simple colors too so I'm going to change the series properties for the the first one will be the actual value so this one we want to have a fill we'll use light gray for the fill and gray for the border and then we'll go here and use white for the fill for the remaining part and again gray for the border and that is almost everything you need to do for a spark pie you can see if you look closely here when I show you see the orientation of the pie where it starts starts off to the right this way for you and that what I really want to do is start off straight up so what I'm going to do is this is the one place in my whole presentation we have to go use the property paint but it's so well buried I'm sure you're going to remember this you need to click on the actual series in here and go down here to general custom attributes pie start angle could that be buried anymore 270 is what we want straight up it work see yeah that's pointing straight up there alright let's get out of there quick and there oh I bet I left the border on I did there almost was our beautiful spark pie there it is ok so let's say make a spark by a lot of different ways to emphasize data you probably shouldn't use them all in one report like I'm doing here Mike start to look kind of hokey but I just want you to have an all in your tool box so you can decide when you use them right so for the last trick here we're going to change this this whole thing into a card layout and I'm just going to need a bunch of extra rows so I'm going to start inserting them inside my steak group below insert below insert below insert below alright got lots of rows now I'm just going to want to get rid of my borders up here I don't need this blue border anymore and we start cutting and pasting all this stuff into their new homes so my labels are going over here cut paste cut paste of this over here this down here this whole rectangle we'll put it down here go a white person a total here that over there get my average production or there get that one over here whoops grab my spark line oops not there I wanted it here what happened for my spark line go there it is try again good paste that over here all right I'm not going to bring the spark pie along I just thought it was a little too much so we'll just leave that thing behind we can delete all that stuff yes and delete all this stuff yes now I got to do this butterfly alignment thing remember that I make all the labels go right come on sometimes it's hard to select the text box and not the text anybody else ever have that problem make this wide enough to see my labels and let's make sure all these are going to be left-justified and this one and this one also and that should be pretty good start now I'm going to use this space over here for other stuff so first I'm going to do is split it so I get the state up there and then I'm going to merge the rest of these down below and I'm going to make this bigger because when I'm going to card I just like the title to be big let's take a look at see what got so far start to look like a card right that little bar on the you can see why it's nice to have that little bar on the left side it gives it something that kind of hang it all together so I'm going to do that here and actually I need to do a little bit too much merging here whoops I need to merge those and those because I remember I need to leave a space down here below that will go in between each of the cards so this one will be these two right here will be my beautiful border and that's obviously way too much so I'll make it skinny and then this will be the space in between the individual cards okay so how do I get that picture it's a little tricky you could put spaced in images if you have images that or you can look up images use image URLs point to a picture I have some images of different types of lettuce just going to do a demo with that I can show you how to make that map thing it actually takes a few minutes so maybe I'll I'll probably put that one on my blog and you can find it there but it's kind of all you have to strip it all down and then kind of get it all centered on the state and stuff but it's a cool thing to do when you're trying to visualize these these state regions it's not really I useful and other things so I'm not going to demo it right now so in the end though you'll end up with something very similar to what we have in the in the in the PowerPoint deck so that is the end of our emphasized data section so let's move on and start talking about our third section here the third thing we want to talk about how to choose the right visual bending on what you're trying to communicate because different visuals do different things so first thing is just to reiterate if you have a choice between this and something with a little more pop to it obviously try to make your tables pop there's a lot of options to improve on the basic look and so go ahead and how to add something to it to help make the data jump out and be more understandable make the insights more obvious so make your tables pop here's a little quiz here's two different charts they're showing exactly the same data which one is doing a better job of showing the data the answer is the one on the left because what we're seeing across the bottom is categories there's really no reason to connect the dots between head lettuce and leaf lettuce or leaf lettuce and romaine and those the connection when you use a line chart is just meaningless and even distracting so when you're using categories when you're showing categorical data use bars not lines on the other hand here's some similar data except this time we're showing production by year so which one's better than bar or the line and the one that's more effective in this case is the line because this data is actually sequential and it makes sense to see the dots connected and that helps you visualize the trends in the movement you can see the slopes very quickly visually so you have a choice your song is just sequential data go ahead and use lines to help with that what about you have lots of lines sometimes if you see reports like this they can be tough to read you have to visually look at the colors and try to figure out which line is which and then there are kinda hard to see overlaying each other this is only about you know 10 or 12 lines I've seen worse but another option is to use spark lines generally speaking the spark lines are going to do a better job of communicating the same information that you see in that line chart the only downside potentially is that you don't get as much precision on the height of the arm you can see all the individual numbers here on the big chart but you can actually show a little scale in a sparkline so that may be helpful as well you can decide what you want to do there but often times the spark spark lines are a better way to present this kind of information than a line chart with series so consider at least using spark lines instead of line series about this one here we're trying to suit see the correlation between two metrics I'm looking at percent harvested and yield you know hundreds of pounds of lettuce per acre so is you think there might be some kind of correlation between those which one is it better more effective at making that correlation jump out well they're both okay at it but the scatter chart is actually quite a bit better look how easily is easy it is to notice that Hawaii up there on the top left is an outlier it just jumps out at you immediately to say hey this thing's bucking the trend something's unusual happening here I think it happens to be bogus data they Hawaii is claiming a hundred percent harvested where as none of the other states was actually hit hunter they're all close ninety nine something so it's probably just they weren't measuring that and so they didn't have any real data in that case so if you take that either art then you have you can see there start to be a kind of a diagonal trim there so use scatter charts when you're trying to communicate correlation all right so that's our short little trip about using the right visual there's lots of other stuff you can read about about when to use what and what's best at showing things there's lots of literature and blogs and things like that but these are some of the most important ones that we see a lot of that I hope will be most useful to you so let's move on to our last section and talk about a few tricks a few final tricks that I hope you'll find handy we're going to talk about how to add clipboard data using an XML data source how to integrate related data using a lookup function how to display short lists using a lookup set function how to optimize your report for output to excel because let's face it in the end that's what a lot of people want to do with your report right okay so clipboard data suppose you've been working hard on this latest research project and you find some great data out on the web about average sunshine percentages by state and you managed to get it into Excel and mess with it and you got an Excel table now how do you get it into your report well we'd really like this to be easy and it I'm I'm going to bet that it probably will be in the near future but that isn't in the product now and it does take a little bit of doing but it is possible and I'm going to show you how to do it it's not too hard the key thing is to change the rows that you haven't Excel into name value pairs which is pretty easy to do in Excel so let me show you then you'll do create functions like that and then you'll just paste that data in as your query and your query becomes your data and then you can use that directly in your report you have to wrap those with a little bit of standard goo on the on the top and the bottom but that stuff's pretty easy too so let me show you how to do that I'm going to open up my clipboard data report okay so let me show you what I got from Excel this is source I found this sunshine data ranking of cities based on percentage of annual possible sunshine in descending order blah blah blah so there's the data come I messed with in Excel I had to use this this cool text to columns thing up here to take the data and turn it into columns that was really helpful eventually though I got it into this table and then I just went something like this ctrl T and make it into an actual Excel table and then I'm going to add some extra columns that create the text that I need to paste in because I can't paste this text in directly so I'm going to do equals concatenate the header value with an equals symbol followed by a quote now you have to do quote quote to get a quote so that ends up being Koko quote followed by the value itself all by of course you have to end your quotes that ends up being quote quote quote and then a space and that gives you the first column okay so if you did that again for each of the columns you're trying to paste over and then you do the last column you add I'll show you the finished product here here I did the first column like that there's the second column and then the last one you just got to wrap it in something here I just use X just a little angle bracket and element name pick whatever you want and then close it at the end and then put all of those name value pairs that you want it in the middle then you take that whole thing all that and you copy and paste that into you I'm going to go ahead and do that you can see I've got some data from my lettuce data source and then I've got created a data source all it is is choose XML from this list and click OK actually you need to set this up here to current Windows user it sets too do not use credentials by default but anyway go ahead and change the credentials so that works and then here I pre-populated this with the goo that you need on either side and you just paste your stuff in the middle go back up to the top now if it's all text data then you can just remove this element path thing but if you have some numbers in there or dates something like that then what you have to set you have to tell it something about the columns that it's supposed to return because it's not going to guess them correctly so what I'm going to do is I have a number in here so I'm going to tell what columns to return I want to return state sorry state and Sunshine percent and then I put in parentheses the type and I can use decimal or date those are two options that you're most likely to use and that was kind of a pain but it's better than having nothing and it does work so when I drag this stuff into a new table over here my state and my sunshine percentage I can actually see the day that I had an excel is now just paste it in directly directly into my report okay so the next trick is about integrating related data now if all the stars align into the planets and the moon is in the right phase and it's Tuesday and you're holding your tongue right all your data for your report is going to be in one data source but if all those things aren't true you might have to pull some data from another data source or maybe another one or maybe a whole bunch of them and you need to pull all together because that's just what people want and how do you do that well here's a simple case you have same stuff I just saw in your state and yield data and you want to see this sometime percentage right next to it right next to that state right well and you can use the lookup function to do this and it's really straightforward you just type in the field you want to match on between the two data sets and then the two fields you want to match on the first one the one on this side and the one on the other data set is at two and then the field you want to return from data set to and then you have the name of the data set that you want to pull it from and that's all there is to that so let me show you how to do that I'm going to open up my integrated related data report now this is going to be fun because we're going to find some interesting correlations here I'm going to do this on a pie chart instead of on a table the same stuff here and oh I never pasted in that stuff here because I better do that right okay now if we just run it the way it is all it's just going to show me here is its showing average yield on the on this axis over here and it has doesn't have anything specified yet for the x value we want to be sunshine percent but that's coming from a different data set so we haven't been able to do that yet right so here's what it looks like without the extra data now let's go ahead and put it in just going to put in the expression that I need here and that is my handy cheat sheet lookup fields code value so the code is what I've got in this data set lookup state and the other data set the sunshine data set return the sunshine percent value and do all that with the sunshine data okay now when I run it hey look at that data from two different data sets in one visual again that's something we love to make easier even easier in the future but this is a really powerful and useful thing that you can do today and it's a frequent request and that's why we add it so that you'd be able to do that now the last thing I want to show you here is not about second last thing is how to create a shortlist so say you've got regions of the country and all the states within those regions and you don't really want the states all on separate roads you just want to see them all together on one row like this how do you do that well there is a way to do that and what you use is the lookup set function together with the join function which basically just puts the commas in between them right so look up set is going to go and look up all the states in the current region and join is going to string them all together with a common a between each one so let me show you how to do that oh and there's some extra code that you can that will be on my blog as well if the data doesn't happen to be sort of the way you want it you can use join sorted or if it has duplicates you can use join distinct sorted so let me show you how to do that one thing to remember is that you can only match on one field here so that will constrain some of the cases where you can use this but it's still definitely valuable in in the cases where you can so let me show you how to do that so here I've got my regions of my state codes you can see they're all coming on individual roads but that's not what I want I wanted to see all the states on one row so I'm going to change the label there and I'm going to change this expression to be this lookup set things so instead of showing the code want one code for each value I'm going to go and get a whole bunch of them and string them together join them together into one long value so there I'm going to look up the set of values by matching region with myself so I'm just going to look up to my own data set right region to region and return the state code value and this is the data set that I'm working with and then this argument right here just says put commas and spaces comment a space in between each one and when you do that we should make a little more room because this is going to be a little wider now that is not what I expected oh I bet I know what happened what we need to do is I've got a detail group in here that's getting the way because it's doing it over and over again I just got to get rid of that that should be a little better tada okay so that is how you can do short lists within a single cell for my very last trick we're going to talk about out to excel suppose you built this beautiful report as all great visuals in it everything's just all the insights are popping off the page people can't can't look at this without learning something but still they don't want to do it they just want to pull the data out and get to excel right so you finally cave and I'll decide you're going to go make that easy for them and you try it the first time you try it you get something like this and you think at first well maybe that's okay I'm not sure what's really going on here but then you look at a little closer and there's all these funny labels at the top and then there's columns with just text in them and then there's all these empty spaces all over the place and it's kind of a big mess right so you go back and figure out how to get this to look nice when you output it to excel first step is of course you got to try it see what happens if you have a very simple table then it'll probably be just fine but if you've done some of the fun stuff I showed you today it might not be just fine so one of the things you'll need to do is go through and set the text box names in tables because those end up as the column headers in the excel this this is when they export to CSV right or set this group names and charts same same effect if you have redundant visuals like I have a value showing and then add a bar that shows the same thing or a little spark pie or whatever you can turn off the output for those so that when they render they don't contribute anything to the excel output you do this in the in the property pane and that's where I'll show you how to do that and then if you're really ambitious you can actually add some extra hitting columns that only come out when they export do a data export like this and you can put some extra data in there that'll help them help them get everything they need out of that export then of course because they're hidden you would have to turn force it the output by turning the output on instead of leaving it on auto so that if I take that report that I was just showing you that table and I go through all those steps this is what I get which is much nicer and has all the data they're looking for so I've got 44 seconds I think I can show you that that fast here's a couple more points if they're actually going to do rich Excel export I might want to have it if you're using page breaks to create pages in Excel you can set the page name to be based on the data and you might want to even do some specific visibility of elements based on the fact that the rendering to excel that's up to you I'm not going to show you that but I am going to show you the CSV stuff before I tell you that I don't have any more tricks right output to excel here we go okay if I just ran this right now and exported I would get that ugly export right so what I'm going to do is go through and look at these value text boxes and rename them I just want that to be state I want this one to be average pointed you get the idea right so if I have one of these redundant visuals show you how to change make that go away just go over here to this data element output change that to no output do the same thing for the pie chart and for the sparkline and then by the time we've done all that and renamed all those other ones you should get I'll just jump right to the finish output to excel after see I actually added some hidden columns here they were hidden normally when they view the report but they only show up when you're doing a data output and I run this report looks the same but when they export to here they get goodness and I'll show that here in Excel there it is and that wraps up our session for today I am out of tricks all right hopefully you learned all this stuff and this was valuable for you there's some related content there's a few other breakout sessions it'll be relevant to reporting services stuff hopefully you'll get a chance to try the hands-on lab with our new Power View functionality and if you have questions for me I'll be outside right after this and also be down at the demo stations the database and business intelligence ones several times during the conference and thank you and I hope it will fill out your evaluations and there's the tag to do it thank you very much

Channel: Scott Bumgardner

Views: 46,113

Keywords: SQL Server 2008, Reporting Services, Tips and Tricks

Id: VBUi1B44q50

Length: 77min 13sec (4633 seconds)

Published: Tue Nov 05 2013