It’s every chart maker’s dream to have dynamically changing titles (well, that and a coffee table that looks like a giant floppy disk. Seriously; Google that one if you haven’t seen it. It’s pretty cool.) Any-hoo… To have a chart’s title change based on data you are, or are not, filtering by is a real boon to understanding the story that the chart is trying to tell. Take the following example:
The reader of the chart can look at survey data for four categories all at once as a combined assessment, or they can filter by a specific category and evaluate the ratings individually. If the chart were to be printed, the reader would not have much of an indication as to whether the chart was displaying all categories or focusing in on a single category.
Enter the DYNAMIC CHART TITLE!!!!!
With this little bit of scripting magic, you can have your chart titles telling the perfect story.
Here is what I would like for my chart to display:
- If a category is selected, print the name of the selected category as the chart title
- If no categories are selected, print “All Categories” as the chart title
To achieve this, right click on the chart and select “Properties…”
On the GENERAL tab, check the box towards the upper left corner that says “Show Title in Chart”
Either type by hand, or build in the Expression Editor, the following formula:
=IF (IsNull(GetFieldSelections(Category)),’All Ratings’,GetFieldSelections(Category))
What this formula does is it first deduces what items in the Categories field have been selected. This is determined by the GetFieldSelections function. If NONE of the fields have been selected (the ISNULL function detects this) then the formula displays the message “All Ratings”. If the GetFieldSelections function detects a selected category, the IF statement will display the selected category.
The result looks like the following:
“But what if the user selects more than one category?” That would take a bit more robust of a formula; and since I’m trying to keep this short and sweet, I’ll leave that for another post/another day.