Allow the user to select which chart to see

I’ve often been asked to produce dashboards whose views change fundamentally based on the choices of the viewer.

For example, a user may want to select whether to show a line chart or a bar chart; or they may want to choose which secondary data source to use as the basis for reporting.

Often, such user requirements can be accommodated by being creative with your worksheet designs. And some of the posts here may help with some of that artistry. But sometimes, you may need to resort to presenting different worksheets depending on the user’s selection

This can be done with a few steps.

First, create two worksheets, one for each of the visualisations that the audience may wish to see: Chart 1 and Chart 2, if you will. Once you’re happy with these views, create a string parameter that allows the user to select which chart to show. And give it a list of values that it can take:


And now, create two new calculated fields in the primary data source being used in the two worksheets: Chart 1 selected and Chart 2 selected. The fields will be boolean, giving a TRUE/FALSE value based on whether the appropriate parameter value has been selected.

Chart equation

Now you can use this field as a filter in Chart 1, with a value of TRUE. This will only show the chart if the value of the boolean variable is TRUE, or else if the parameter has been set to Chart 1. (You cannot use a parameter directly as a filter, hence this interim step.)

If you play with the value of the parameter, you’ll see the content of the worksheet appear and disappear. Repeat the above steps for the second chart, and the two worksheets will work in tandem with one another.

Next up, we need to get those charts into a dashboard.

First, they need to sit together in their own container, so you can select either a vertical or horizontal one, either is fine.


And now you can drag each of the worksheets into that container. Click on the dropdown at the top right corner of either chart to show the parameter control. By playing with the parameter value, you’ll see the respective charts appearing and disappearing.

Now unfortunately, the title of a worksheet appears regardless of whether the content of that worksheet is present, so you’ll need to hide the title on both sheets to ensure that the hidden worksheet fully disappears. If you want a title that’s consistent for the two views, then you can put this in as a Text component (see the above selector) above the container you’ve just created. If you want one that varies depending on the chart chosen, you can use a similar technique as this one to create automatically changing titles that sit in their own container.

And there you have it. The user can select a parameter value and the chart that they’re presented with will change in front of their eyes.

Leave a Reply

Your email address will not be published. Required fields are marked *