Monday, June 15, 2015

Make Dashboards' formatting consistent.

Has this ever happened to you?

In the course of normal events dashboards get built with a hodgepodge of styles.

For example, in the Workbook to the right the

  • default dashboard has not had any formatting applied, while the
  • formatted dashboard has been formatted.

It's sometimes desirable to have a consistent Dashboard look and feel without going through the tremendously tedious manual process of configuring them individually.

Tableau lacks the ability to enable this, either by setting the defaults you want, or by applying formatting to Dashboards in bulk.

But you can now do it, simply and easily.
There's a Tableau Tool for applying the formatting from a template dashboard to all of the dashboards in a set of workbooks.

Here's a dashboard with the formatting to apply to a selected set of Workbooks.

We'll see below how to make this happen–it's a pretty simple matter of running the appropriate Tableau Tool in the directory where the Workbooks are.

The tool will take the Template formatting and apply it to all the dashboards it's pointed at.

For the tool to work there are two important aspects to this Workbook:

  • The Workbook is named
    Template.twb
  • The Dashboard is also named
    Template.

Of course, if one wants to use another Workbook and Dashboard name, it's easy to reconfigure the tool to accommodate them.

The formatted Dashboards.

Here are the default and formatted dashboards re-formatted with the Template formatting.

Important points about the formatting:

  • Only the formatting shown above will be applied;
    there are other things one might wish to configure, but there are complications that go along with them.
  • All of the Template Dashboard's formatting will be applied, even if some part of it hasn't been configured;
    it's possible to implement finer-grained control of what formatting gets applied, but that gets complicated, beyond the scope of this initial formatting approach.

The tool: SetDashboardFormatAll.rb
is available on GitHub here, or can be copy/pasted from below.


  #  Copyright (C) 2014, 2015  Chris Gerrard
  #
  #  This program is free software: you can redistribute it and/or modify
  #  it under the terms of the GNU General Public License as published by
  #  the Free Software Foundation, either version 3 of the License, or
  #  (at your option) any later version.
  #
  #  This program is distributed in the hope that it will be useful,
  #  but WITHOUT ANY WARRANTY; without even the implied warranty of
  #  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  #  GNU General Public License for more details.
  #
  #  You should have received a copy of the GNU General Public License
  #  along with this program.  If not, see <http://www.gnu.org/licenses/>.
  
  require 'twb'
  require 'nokogiri'
  require 'csv'
  
  $templateTwb  = 'Template.twb'
  $templateDash = 'Template'
  $twbAppend    = '_styled_'
  
  puts "\n\n"
  puts " Setting Workbook dashboard formatting, using the formatting"
  puts " from the #{$templateDash} dashboard"
  puts "   in the #{$templateTwb} workbook\n\n"
  
  $csv = CSV.open("TT-FormattedWorkbooks.csv", "w")
  $csv << ["Workbook","Dashboard"]
  
  def loadTemplate
    return 'Template.twb not found' unless File.file?('Template.twb')
    twb  = Twb::Workbook.new('Template.twb')
    dash = twb.dashboards['Template']
    return 'Template dashboard not found' if dash.nil?
    style = dash.node.at_xpath('./style')
    return '  ERROR - no style available from Template dashboard.' if style.nil?
    puts "   Dashboard styling:"
    styleRules = style.xpath('./style-rule')
    if styleRules.empty?
      puts "\n\t  Template dashboard formatting is default style."
    else
      styleRules.each do |rule|
        puts "\n\t Element: #{rule['element']}"
        formats = rule.xpath('./format')
        formats.each do |f|
          puts sprintf("\t -- %-16s : %s \n", f['attr'], f['value'])
        end
      end
    end
    puts "\n"
    return style
  end
  
  def processTwbs
    path = if ARGV.empty? then '*.twb' else ARGV[0] end
    puts " Looking for TWBs using: #{ARGV[0]} \n\n"
    Dir.glob(path) do |fname|
      setTwbStyle(fname) unless fname.eql?($templateTwb) || !fname.end_with?('.twb')
    end
  end
  
  def setTwbStyle fname
    return if fname.eql?($templateTwb) || fname.include?($twbAppend + '.twb')
    twb = Twb::Workbook.new(fname)
    dashes = twb.dashboards.values
    puts sprintf("\t%3d in: '%s' ", dashes.length, fname)
    return if dashes.empty?
    dashes.each do |dash|
      node  = dash.node
      style = node.at_xpath('./style')
      tmpStyle = $templateStyle.clone
      style.replace(tmpStyle)
      $csv << [fname, dash.name]
    end
    twb.writeAppend($twbAppend)
  end
  
  $templateStyle = loadTemplate
  if $templateStyle.class == 'String'.class
    puts "\t #{$templateStyle}\n\n"
  else
    processTwbs
  end
  
  $csv.close unless $csv.nil?

The Tool in action
Here's the code being run.

In this case the execution command specifies that only the single Workbook ExampleDashboards.twb will have its Dashboard(s) formatted.

Upon startup, the tool looks for the Template Workbook and Dashboard and, assuming it finds them, prints out the formatting found there.

It then looks for Workbooks, either all of them, or those matching the single command line parameter. Those that it finds have any Dashboards they contain formatted to the Template configuration.

It then looks for Workbooks, either all of them, or those matching the single command line parameter. Those that it finds are listed with the number of their Dashboards, if any, and have their Dashboards formatted to the Template configuration.

By default, the Template-formatted dashboards are written to a copy of the original, with '._styled_' appended to the name. This is a precaution, ensuring that the original Workbook isn't harmed in the process. Adjusting the Tool to apply the formatting directly to the Workbook is a small change, easily made.


 ...$ ls -1 ExampleDashboards*.twb
 ExampleDashboards.twb

 ...$ ruby "{path to}\SetDashboardFormatAll.rb" ExampleDashboards.twb  


  Setting Workbook dashboard formatting, using the formatting
  from the Template dashboard
    in the Template.twb workbook

    Dashboard styling:

          Element: table
          -- background-color : #fae7c8

          Element: dash-title
          -- font-weight      : normal
          -- color            : #000000
          -- font-size        : 14
          -- background-color : #f0d9b6
          -- border-color     : #b40f1e
          -- border-style     : solid

          Element: dash-subtitle
          -- font-size        : 11
          -- font-weight      : normal
          -- color            : #b40f1e
          -- background-color : #d7d7d7

          Element: dash-text
          -- text-align       : center
          -- color            : #b40f1e
          -- background-color : #e1e8fa
          -- border-color     : #1b1b1b
          -- border-style     : solid
          -- font-family      : Comic Sans MS

  Looking for TWBs using: ExampleDashboards.twb

           2 in: 'ExampleDashboards.twb'

 ...$ ls -1 ExampleDashboards*.twb
 ExampleDashboards._styled_.twb
 ExampleDashboards.twb

 ...$

5 comments:

  1. Hi Chris, looks great! Thank you.

    Quick question: will the script only apply a template to the layout of worksheets within the dashboard ? Or is it also possible to apply template formatting to the font and shading of headers, etc, within the worksheets themselves ?

    Thanks!

    ReplyDelete
  2. Hi Chris, looks great! Thank you.

    Quick question. Would this script be used only for tempting the layout of worksheets within a dashboard ? Or could it also be used to template the fonts & shading, etc, within the worksheets themselves ?

    Thanks!

    ReplyDelete
    Replies
    1. Hi Keith, and thanks.

      As it sits only the Dashboard's core format elements are in play. Partly because they're right there and easy to work with, partly because working with the worksheets introduces complications I didn't want to have to handle in an initial tool.

      Building the tool for managing worksheet formatting via a similar templating approach is in the queue. There are lots of things that are in the mix that affect what the tool does, and how it does it, e.g. does it apply the template formatting to all of a target workbook's worksheets, or only those that appear in dashboards? Either is a valid choice, and there are consequences to each: adjusting all of them is the simplest approach, but like all naive approaches may not be as universally desirable as it initially seems; adjusting only those in appearing in dashboards is more complicated, but is a finer scalpel; determining at run time which approach happens is better than only having one approach, but then the mechanism for determining which one is employed needs to be in place; and having a nice robust, fine grained control over what happens is best, but it also opens the door to needing a whole lot of choosing and control mechanisms.

      For background, I'm not all that big a believer in the "do-everything" approach. I tried that with TWIS and found that trying to build everything into a single app gets really messy really quickly, and eventually overwhelms to basic functionality with command and control complexity. So I've started building small targeted tools, largely modeled after the Unix many small tools philosophy. In this light I'll likely build individual tools for templating worksheet formatting - one for all worksheets, one for only those appearing in dashboards, and maybe one for only those appearing in stories.

      Which form of such a tool/tools would you think would be more useful, valuable, and easy to use?

      Delete
  3. Hey, great post.
    would you have something to automatically update the list of available parameters?

    Im thinking about writing something that finds the parameter I want on the XML file and populate the "member" list lke the one bellow where I manually added the "New Option"










    Thanks

    ReplyDelete