How to Use Control Sheets for Better Parameter Actions

Control any parameter actions scenario with a second data source

One drawback to parameter actions is the value you want to overwrite must exist on one of the views. Ryan shows you how to get around this by connecting to a second data source containing all your parameter allowable values!

Hi, this is Ryan with Playfair Data TV. And in this video, I’m going to show you how to make control sheets which are going to make your life a lot easier when you’re using parameter actions, regardless of which scenario you’re wanting to use those parameter actions with.

I’ve always loved parameters. I’ve always described these as my Swiss army knife because they have so many uses. I’m going to show you just a couple of my favorites.

I go into much more depth on these on other videos, so if you’re not familiar with this, I encourage you to check out the videos, How to Allow Users to Choose Dimensions and Measures in Tableau, and How to Use the Parameter Actions to Change a Date Part in Tableau.

I’ve got this line graph. There are two parameter controls being shown. The end user can choose from any of the five metrics that come out of the box with the Sample – Superstore dataset. When they choose a different metric, the axis gets updated. Obviously, the trend gets updated.

They could also choose from five different date parts. By default, we’re looking at days, but if I choose Week, the x-axis changes, in this case, to different date parts. So it’s smoothing out my line there, which I’ve now changed to the Profit Ratio measure.

Both of these tactics were done using parameters. These tactics have become even better with the addition of parameter actions which allow users to change both of the current values of those parameters just by clicking on different buttons on a dashboard.

However, the one big drawback with parameter actions is the value has to be somewhere on the dashboard in order to overwrite the current value in those two parameters down here in the bottom left corner of the Data pane. There’s a couple ways to get around that.

First of all, if you’re using measures it’s a little bit easier because you can just make a chart that has the values of those measures. You click on one of the measures, it overwrites the value in the parameter.

It’s not so easy with dimensions or anything that is string or text based because, for example, I don’t have a dimension with these five different dimension numbers in it to choose these different date parts. Well, that’s the trick that I’m going to show you today.

Because parameters are the only fields that go across data sources– so in other words, all of these parameters down here, as I connect to, say, a second, third, fourth data source, all of those parameters are going to be shared, or they can be shared across those multiple data sources. Because that is the case, I can simply setup an Excel file that has all of my scenarios in it, connect to this Excel file, and setup control sheets.

So for these two tactics that we’re using, choosing from those five metrics, choosing from those five date parts, this is what the entire data connection would look like. Note that I’ve got something called Parameter Value. That’s the field that’s going to overwrite the values in those two parameters.

The Display As can be a different text that will be shown on the sheet. These will eventually be the buttons that the end user can click on.

And then I’ve got column C here called Parameter Control. This is for me to filter out the appropriate values per control sheet that I create.

But that’s the entire sheet. You would simply add whatever scenarios you’re making. So any words that you eventually want your end user to click on, and any words that you eventually want to overwrite the parameter values with, should be in this Excel file.

Now that I’ve got this Excel file, back here in Tableau Desktop, I’m going to connect to a new data source. It’s Excel, so I’ll click on that. There is my parameter control sheet. So I’ll click Open on that to connect to it.

There are those three columns that I just showed you. And I’ll go to a new sheet.

Now I can set up my two different control sheets. So this first one– I’ll do date part first, so Date Part Control Sheet. And I’m just going to make a quick text sheet. I’ll put Parameter– I’ll put Display As onto Columns, and Display As onto Text because you can’t actually click on those column headers which is why I had to put this in both places.

Now, note, I’ve got the full list from that Excel file. That’s why I had that third column. If I drag Parameter Control to the Filters Shelf, I can limit this to just the Date Part’s selected values. Click OK. Now I’ve got this nice, clean control sheet.

I will put these in order just by dragging them. It’s a little more intuitive to my end user. So I’ve got day, week, month, quarter, year.

The user can’t actually click on these headers, so I’m going to just hide those by right-clicking on the pill and deselecting Show Header. And you can take the formatting as far as you’d like. I might center these, might add some vertical lines to make them appear more like buttons, but I’ll call this good for now.

But a very critical step to these control sheets, the parameter value needs to be somewhere on the sheet in order to use it to overwrite the value in that Date Part Selected parameter. Right now, my Parameter Value is not on the view anywhere.

The easiest way to add it to the view without changing the look of it is to drag Parameter Value to the Detail Marks Card. The sheet looks exactly the same, but now that parameter value will be available to me in the last step when we go to set up our parameter action.

So there is the first control sheet. Now that we’ve got that, it’s a lot easier to create the second one. I’ll just duplicate the sheet, call this my Measure Selected Control Sheet, change the filter to limit the values to Measure Selected.

I still like the look and feel of this. I’m pretending for now. We still need the Parameter Value on Detail and the Display As on Text. So everything’s in place. That’s our second control sheet.

Now, if I go to add all of these to a dashboard– so here’s my chart. I’ll throw my Date Part Control at the top and my Measure Selected Control at the top. And I’m just kind of flying through this. I’m not worrying too much about formatting at the moment.

I’m going to purposely leave the parameter controls on here for the moment, just to show you that this is working. But now that I’ve got my control sheets that were built by connecting to that second data source and that, by the way– again, just to repeat this, that second data source, its exclusive purpose, was to make those control sheets. Because parameters work across data sources, that dataset doesn’t have to do anything else. We’re simply using it to basically create dimensions that include dimension members with values that match the parameter values that we want to overwrite.

Now that we’ve got everything on a dashboard together, we need to create a Change Parameter Value dashboard action for each of the two control sheets. To do so, click Dashboard in the top navigation, and click Actions. I’ll do Date Part Control Sheet first.

You just click Add Action and then Change Parameter. I’ll call this the Date Part Control. And we will say that if you click on the Date Part Control, I want you to overwrite the Date Part Selected. And we’re going to overwrite the Parameter Value.

This is why it was so important to make sure Parameter Value was on the Detail Marks Card. If we had not put that field onto the view anywhere, it would not be available to us as an option.

And that will be important. If you ever want to use a different Display As value versus the Parameter Value, you will need both of those columns. And both of them would need to be on the sheet.

So there’s Parameter Value. Note that there’s no aggregation. The data type in this case is String or text, so you don’t aggregate. You don’t sum up or take an average of text. So we leave that as None.

Click OK. We need to do a second parameter control. The reason you can’t simply click on both of these is because you have to target one parameter at a time. So that’s one little tiny inefficiency here, is I need to setup individual dashboard actions to get each of these to work.

So this second one is my Measure Selected Control. And this time we’re saying that if you click on the Measure Selected Control Sheet, I want you to overwrite the Measure Selected Parameter. And then the value is the same. That’s the Parameter Value from my Excel data connection.

And again, the aggregation is None. Click OK. Those are now both in place. I’ll go to Presentation Mode and then we’ll see if this works.

So in the introduction, I changed the settings to look at Profit Ratio by Quarter. Let’s say I wanted to get back to Sales. I click on Sales.

Note that by clicking on that control sheet I clicked on the word Sales. It overwrote the value in the Measure Selected parameter, and now the y-axis is looking at Sales. I can also tell it’s working because the default parameter control switched to Sales.

Let’s see what happens if I move this to years. So note, the default parameter control says Quarter. If I click on Year, it overwrites that parameter control. It now says Year and I’m looking at Sales by Year.

I no longer need these parameter controls. So that’s one of the advantages to parameter actions, is you can essentially format these controls however you would like. Instead of having these, in my opinion, clunky dropdown menus, you can make a nice, elegant, web-like experience that allows the user to change how the view looks just by clicking on buttons.

You no longer need these. So I’ll get rid of them. And just to show you a couple more scenarios, let’s switch it back to Month and look at Profit instead of Sales.

So just a quick tip. If you’re ever using parameters and/or parameter control specifically, which I really like, if you can connect or just put in all the scenarios that you would like in a separate data connection, connect to that data connection. It’ll make your life a lot easier when you’re making control sheets for those parameter actions.

This has been Ryan with Playfair Data TV – thanks for watching!