Using Colors as Parameters to Save Time

William Belisle
A free video tutorial from William Belisle
Business Intelligence Master
4.1 instructor rating • 2 courses • 4,859 students

Lecture description

How to use Parameters to define a "theme" for our report. Using Parameters allows us to easily make changes throughout the entire report by only changing a single value.

Learn more from the full course

Learn SSRS SQL Reporting & Business Intelligence Essentials

SQL Reporting using SSRS gives you hands-on knowledge to make great SQL reports with Business Intelligence essentials

03:09:52 of on-demand video • Updated July 2016

  • By the end of this course, you will be able to create great SSRS reports.
  • You will learn how to extract data from a database
  • You will learn how to organize large amounts of data into concise reports
  • You will learn how to use graphical display objects to make reports that stand out
  • You will learn how to work smarter, not harder, by learning techniques that cut down on repetitive work
English [Auto] We have all of our components functional and aligned. But the report still isn't very nice looking. We need to use a unified color scheme to get this report to look. Now before you dismiss the cosmetic aspects of report building you should know this really does matter. At first it may seem silly but I've seen many times where end users are reluctant to use a new report because it's ugly. They say things like our spreadsheet charts look nice. So we'll keep using those instead of SS SSRI thing. They would rather keep their data in a manually updated spreadsheet than use a fully functional database because of how it looks. It's amazing I know but this is something you should be thinking of when designing reports if end users don't use your report. It doesn't matter how good it is. I'm also realistic though I know there are deadlines and cosmetics come last and I'm not saying you need to spend hours getting it pixel perfect with just the right shade of blue but there are things you can do to help speed up the process. Let's hold on control just like multiple cells at the same time and change their background color to red. Now with a preview of the report Well turns out I don't like red. Let's go back now it's like those cells again and change the background color to green. Let's preview the report again. Looks like I don't like green either. Doing things this way is tedious and takes forever especially when you get to the fine tuning. Plus we're just lucky our matrix right now. What do we want other things to match. Like some reports if only we could use CSX. Now unfortunately we can't do that. All we can do something very similar we can make parameters that represent colors. Let's go and do that now. Well create a new parameter and we'll call it Hetter or its default value we'll put in blue now select our. And we'll set their background color to an expression and that expression is going to be a parameter header that we just created. Now it's preview report Well the cells are blue just like we wanted but we also have this prompt up here called header. Let's change it to a different color and just like that. The report's updated we don't need to go into every single cell's properties and change them. We can just change the parameter. We can also type in our G-B values. This looks pretty cool. Now that we found this color I want to use it. Let's copy this and set it as the default value for a parameter. Now when we run a report this is the value they'll come up by default. We also want this parameter to be passed to us that reports first let's create all the parameters we're going to need. Now you can name these whatever makes sense to you. And you can decide how to partition them. How many different colors you're going to have. And what should be what color. All right now what makes something for the background let's make another one for a head or text you can see I have some colors in mind already and we'll make one final parameter and we'll call it chart again. These are just names that make sense to me for what I'm doing. Feel free to customize these so they make more sense to you. Now we have all the parameters we're going to need for our colors. Let's start assigning them first was like the body of our report and said it's background color to be our parameter background almost like all of our cells that have text in them and will set their color to be EDR text. We need to update our sales chart next. This is our SPARC line. Let's open up its report. And the first thing we need to do is add in our parameters. I happened to know the color scheme I'm thinking of. We're only going to need the head a parameter and the chart parameter we don't put any default values in. So I'm trying to make this video as quick as possible but in the real world you would probably put something in. Now we're going to church properties and change its full color will set this to be head a parameter. We'll also go in and change the fill color of the line and we'll set it to our chert parameter. Then we need to go into our report properties and make sure we pass in these additional parameters. If these drop downs come a point for you make sure that we select your report we want to pass over our parameters EDR and chart. Now let's see how this report looks. It's looking pretty good. And like our chart matches the rest of our matrix although I do think we need to do something about these great totals. They don't look good anymore. You can type in any color code that you want just make sure it obeys the proper format. You can't just type in zero for black. You need to type in 0 0 0 0 0 0 Let's change our chart color to bright red cool. Now we see it works. Let's change all of our total selves to match the rest of the matrix. Don't forget about this. Remember we put in a custom expression here to define the colors. This is what highlights our rows and columns. Right now we have these values hardcoded. It's easy not to change these to be parameters to there. Now everything in our matrix is taken care that's a much nicer looking matrix. Let's get this to June 2014 for monitors in play around the colors a little bit green really stands out. And then yellow really burns my eyes OK. This isn't a very good color scheme but I'm just doing this for demonstration purposes. You see our background and the selected cell of our matrix are the same exact color. If we wanted we could make another color parameter and separate these for what we're doing I think this will work fine. Let's update our employee sales report as well. We need to add in our chart parameter and will adjust the fill of our lines to be that parameter. Now we'll add in our head a parameter We'll set the fill of our chart to be equal to this one while we're at it let's also take our section gauge at Add in our parameters as usual. And again you may want to put in default values for this. Let's set the background color of our rectangle to be our head parameter not want to engage and set the fill color of our frame. We want this to be a chart color. Make sure to do this to both cajas now just the color of the pointer Well just it is to be a solid color and put in our chart parameter same thing over here. Now we need to go back to our sales matrix and pass the parameters over to some reports you might be thinking this is a little bit tedious and it kind of is. But you know what. It's better to do this once and set everything up and then be able to control it via a parameter than to go through this entire process setting specific colors that if you ever want to change you need to repeat the entire process over let's see what we have so far. Oh that's really nice. I like how these are all starting to come together and really fit okay. Last one are standard deviation graph. Add in the parameters as you know we'll assign the background color of our chart. To be your head parameter and we'll set our metric value line to be our chart color then we'll pass over our parameters. I also notice we forgot to use a black border around our matrix. Let's go and set that up now. Same thing for employees sales. Let's put a black border on this one as well. And last but not least we need to put the black border around our standard deviation graph. They're all the. Now let's see how this looks. Man this looks so cool. I really like how this looks. Doesn't this look much better now that everything matches. And oh by the way here's a quick reminder what it looked like before. Now doesn't this look nicer. Yeah I thought so. The cosmetics of a report do matter and you should pay attention to them. It took a little while to set everything up. But like I mentioned earlier all we need to do is change any of these parameters up here and we get a completely different color scheme if the boss ever comes back to us and says hey we're changing our company's colors. This needs to match those new colors. All we need to do is change his parameters. I want a copy in a few parameters I have off screen and we can see this report will look completely different. OK. And then the report will look good. I just said it looked different. This gives you an idea of how easy it is to radically change the look of a report just by changing the parameters. You can use this for fine tuning as reports running. You can change the color by 10 20 30 points without having to go in and edit every single property. This will really save you a lot of time. And once you find values that you like. All you need to do is set the default value of that parameter to that value and then hide the parameter. The end users will never even see it. They won't even know it exists at all.