Dashboard Design and Deployment Best Practices with CX: Part 2- Digitalize your Ideas

by Ryan Goodman

Create a mock up of the dashboard
Now that you have a concise plan of action for your dashboard, you will start working toward the first version to share with stakeholders and end users. You want to start by creating a dashboard mock up in order to quickly assess the success of your plan in a collaborative setting. Building a mock up is even more critical for stakeholders who have a passive mentality toward a new dashboard implementation within your organization. If you are using Xcelsius, you have the perfect tool in your hands to quickly asses if you are going to diliver value to the business user with minimal effort.

Creating a dashboard mock up will allow you to simulate how an end user will interact and navigate through business metrics and supporting analytics. When assembling your dashboard mock up, you will include the minimal metrics needed to communicate the overall end user experience for navigating and digesting information. This process will enable a rapid development path, which in turn will enable you to quickly make changes and adjustments without having to re-configure the logic and data connectivity.

Because a dashboard is an evolving process, you can expect the users and stakeholders to request major changes once they can actually see their information in your mock up. Once you have a final mock up, you will be able to re-use some of the work in your final version of your dashboard.

Design with the end user in mind
As you transform your ideas into your digitalized dashboard, you must design your layout and navigation to facilitate an end user experience that enables the quick assimilation and digestion of information. To ensure a positive user experience, you should:

• Utilize the screen real estate effectively by placing the most important information in the upper left or center of the page. Physical size and color can also be used to draw attention to important information on the screen.
• Add selectors to break up content logically or to enable a user to drill down into data, but not to interfere with the analysis itself. In creating a dashboard your want to facilitate analysis of multiple related metrics or trends without overloading your end user with too much information. It is a unique balancing act that must be dictated by the dashboard end users, so you understand the required depth and breadth of analysis.
• Understand the technical competency of your end users to ensure that you create an interface that requires minimal clicking to access information.
• Use color schemes that make values easy to read and easy on the eyes.
• Combine components and create layers of information that enable a natural work flow for accessing and digesting information.
• Use labels to identify all of the information so that a new end user could understand what information they are looking at without any formal training. With that said, you should always try to include some help text or pop up help icons.

Don’t get lost in the visualization sex and sizzle
While Xcelsius does provide a wide array of components, features, and graphical enhancements to spice up your dashboard, you want to ensure that you do not misuse or overuse these features and loose sight of the dashboard’s overall purpose. In describing “getting lost in the visualization sex and sizzle,” I elude to designers who get wrapped up in adding too much spice to the dashboard when it is not completely necessary. This entails using appropriate visualization methods for displaying quantitative information.

In the next part, we will look at some best practices to finalize your production dashboard.

by Ryan Goodman

With Xcelsius, you are equipped with components that will allow you to create a single parent SWF file that dynamically loads child SWFs inside of itself. The purpose of this “multi-layer” dashboard functionality is to enable designers to build large scale dashboard applications that are easier to scale and manage. One of the issues that will arise when building these applications is a need for the parent SWF to communicate with the child SWF file. While Xcelsius does not provide a means to do this, a workaround is possible for passing simple data sets from a parent to child on load.

By combining the slideshow component with a simple concatenate function, you can easily enable the parent SWF to dynamically stream single value or multiple value parameters to a child SWF (with the use of Flash Variables to consume the parameters). Below is a brief description of how I configured this example:

Download Source Files

1. Insert the slide show component and bind the source file URL to a cell (B3). In this case the name of our child SWF is “child.swf”
2. Create the parameter(s) names that we will pass on to the child SWF: “single_param”, and “multiple_values”
3. Decide where within the spreadsheet the parameters will be drawn from (user input, formula, insert in cell(s) for a selector): A1 & A2
4. Now we will concatenate a new URL so we can pass the parameters. The URL will end in cell B3, and will look like this: child.swf?single_param=A1&multiple_values=A2

In this case I created two input text boxes for each parameter and linked them to cells A1 and A2. Now we can configure our child SWF to consume the parameter values.

1. Go to File>Export Settings…, and click on the radio button labeled “Use Flash Variables.”
2. Check “CSV format,” and then click on the “Define Variables” button.
3. We want the first parameter, “single_param”, to be a single value so we name it accordingly and bind the variable selection to a single cell.
4. The second parameter, “multiple_values” will need to be configured to accept multiple values from the parent SWF so the variable selecton will be bound to a range. In this case we selected a column. In order for the SWF file to plot multiple values, they will need to be comma separated.

To illustrate the Flash Variables being populated in the model, I inserted a simple table component. Now you can place both the parent and child SWF files in the same directory and give it a try. This will also work inside of a PDF or PPT as long as the child SWF is in the same directory.

Connecting Xcelsius to an RSS Feed

by Ryan Goodman

For those of you who are not familiar with RSS, Really Simple Syndication or Rich Site Summary are web services for public consumption over the world wide web with no particular limitations on security or usage. Today, RSS feeds are syndicated from news organizations, general interest groups, blogs, and forums. The data is streamed as XML that can be easily consumed or remixed with any RSS reader or Aggregator. It is the lightweight nature and flexibility of this standard across multiple platforms that have allowed this technology to flourish. In fact, this Blog has a syndicated RSS Feed. I will save my views on re-mixing information and RSS related technologies for another article…

Since you need an RSS reader to consume XML data, why not use an Xcelsius dashboard to present context specific information. Let’s see how we can achieve this using Excel 2003 and Xcelsius to consume and present Google News RSS.


Download Source Files

For this article, I am not going to go into depth about XML Maps in Excel 2003, so for detailed info, you can download a great whitepaper on the Xcelsius learning center site: Connectivity Using XML Maps in Excel 2003

Consume the RSS feed as an Excel XML Map

1. In the toolbar go to Data>XML>XML Source…
2. At the bottom of the right hand side toolbar click on the XML Maps button.
3. Click on Add to add a new XML map
4. Instead of navigating to a file on your PC, you will paste your RSS feed URL. In our case we are using a Google news RSS feed. Lets make our default feed specific to Xcelsius: http://news.google.com/news?hl=en&ned=us&q=xcelsius&ie=UTF-8&output=rss. If you were to enter this URL into your browser, you would see the XML in your browser. It is this XML that we are going to stream to our Xcelsius model.
5. With your XML Map added, click OK and you will notice the hierarchy displayed on your right hand side toolbar.
6. In this case we are going to utilize 2 elements from this list: Under the Item folder, you will drag and drop Title and Link and place them next to each other in any cells. Lets use B5 and C5 as our cells.
7. Now you have mapped the elements to Excel. Lets see what the data will look like when it is returned. Right click on cell B5, hover over XML, and click Refresh XML Data. This will force Excel to query Google news and return the data based on the RSS feed URL we specified earlier.
8. Before we save and move to Xcelsius we want to ensure that this Feed reader will scale appropriately. To do this, you will need to expand the mapped range. You will notice a blue border around your mapped cells. Go to the lower right hand side and click on the handler to exapand the number of rows to B20 and C20. This will ensure that your reader will scale when there are more news articles.
9. Save your Excel file

Configure Xcelsius to Show the RSS Data

1. In Xcelsius you will first import your Excel file. Upon importing this file Xcelsius will automatically understand that the Excel file contains XML maps.
2. In the toolbar, click on Data>XML Map Options. You will see the same URL that you defined in Excel. We can change the URL or even bind it to a cell in our model. Click OK.
   * In the final advanced model that I provide on this blog, I leverage this functionality to dynamically generate the URL based on search criteria defined in Xcelsius.
3. Import a List Box selector component and a URL button component (located in Web Connectivity folder).
4. For the List Box: Bind the Labels to B6:B20; Use the Insert Rows Option; Bind the Source Data to B6:C20; Bind the Insert In to B4:C4.
5. For the URL Button: Bind the title to B4 and the URL to C4.
6. Vuala- You now have an RSS reader
   Important Note: Do not try to preview it inside of Xcelsius: It will not work. Go to File>Export Preview

Here is a more advanced example where I take the original URL, break it apart and add a text box in Xcelsius to drive the search parameter. Then I dynamically concatenate (http://www,ryangoodman.net/blog/) the URL back together and bind that cell to the XML Map Options window (see #2). Then I add an XML map refresh button which allows me to dictate when the query is triggered. I have provided all source files so you can reverse engineer and play around.

Download Source Files

Addressing Cross Domain Access to the RSS feed
If you download and run these files on your desktop, it will work perfectly. Because of Flash security settings specific to cross domain access, you have to normally setup a cross domain policy file. This cross domain policy is applicable to scenarios where you have access to the data source.

In our case we obviously do not have access to the Google News servers. For this scenario, we will need to look at creating a PHP proxy to allow for cross domain access. To use this proxy, you will specify your RSS url as a parameter inside of the query string:
“http://your_site/xml_proxy.php?=url=your_rss_feed”

So in the our Xcelsius model we would define the concatenate formula to generate the following URL and ensure that this URL is linked within the XML Map Options window (see #2). http://ryangoodman.net/blog/../xml_proxy.php?url=http://news.google.com/news?hl=en&ned=us&q=xcelsius&ie=UTF-8&output=rss.

Xcelsius Dynamic Ranking Logic

by Ryan Goodman

I often get questions about dynamic raking within Crystal Xcelsius models so I have put together some lightweight logic that will enable you to rank up to several hundred rows of data during runtime. First we will look at the basic functions needed; then we will combine them into a simple model. Finally, I have provided a more complex model to show you how to generate some more compelling real time analysis using these functions.
LARGE and SMALLMATCHVLOOKUP
With an understanding of how these functions work, let’s combine them to create our dynamic ranking logic.

1. Index cell: This index cell will be used eventually by a vlookup function once the rank is identified
2. Source Data: This is the original source data that we will rank.
3. Unique Identifier. The unique identifier addresses the issue of duplicate values. By carrying the value to the 100 thousandth place, this identifier will ensure that all values in your rank order are unique without affecting the values themselves. This is important because the logic will break down if there are duplicate values
4. Adjusted: The adjusted values will be the range that we will actually perform the LARGE function. Because we have summed the original value and the unique identifier, we know that there are no duplicate values.
   
5. Rank Number: We will use this rank number as our “K” within the LARGE function.
6. Rank Lookup: This range uses the LARGE function to find the Nth value (#5) within our Lookup Row (#4)
7. Match Row: Now that we know the Nth value we need to find the absolute row for which this value is located in the source data (#2).
8. Finally, with the absolute row identified (#7), we can perform a VLOOKUP of this absolute row to return any other information we desire. In this case, it was the name.
   Now that you have the basics down you can check out this more advanced example and reverse engineer the source file. Let me know how this works for you or if you are able to build on this.