Embed Xcelsius inside of Salesforce.com

by Ryan Goodman

Salesforce.com Inline S-Control Analytics with Xcelsius
With the Winter 07 release, you now have the ability to assemble your own dashboards using the custom S Control, which serve as an IFRAME on any Salesforce.com page. While testing these new features last October before Dreamforce, I was able to quickly assemble a dashboard that can leverage the Salesforce.com API along with an inline S Control to extract information on the page.

The result was an embedded dashboard inside of every opportunity page that would display a visual history of the account. Though I am not sure how well it resonated because it was visually not that sexy compared to the full dashboard applications, the power of these nested dashboards for SFDC end users is extremely valuable if executed correctly.


Here is an overview of what I did.
1. Decide on a practical business case that would warrant a dashboard on every opportunity page:

In this case I wanted to embed a standard dashboard on every opportunity page that would allow the end user to visually digest the history of all other opportunities for a given account: won, lost, and pending. In addition, it was important to show the current opportunity in relation the historical view. Then the end would need to view high level details and navigate to any other historical opportunity with a single click. The underlying idea was to alleviate the mental juggling and repetitive clicking to access the same valuable information. I counted 10-15 clicks in some cases to obtain the same exact information.

2. Build a SAOP web service to pull down all opportunities and details from a specified account using the SFDC API. There is a good article on the Xcelsius Business Widgets Site outlining best practices.

3. Build a custom S control to expose the current opportunity information as variables, so they can be pushed to dashboard on-load, using Flash Variables. It is this S Control that will supply the information about the current opportunity that and will also define the Account parameter for our SOAP web service. Upon inserting the S control onto the opportunity page, I set the size to 1 pixel to hide it from the end user.
Example: Exposes the Session ID and Opportunity Name as variables.

sforceClient.setLoginUrl("https://www.salesforce.com/services/Soap/u/8.0");
sforceClient.init("{!API.Session_ID}", "{!API.Partner_Server_URL_80}", true);
sforceClient.init("{!Opportunity.Name}", "{!API.Partner_Server_URL_80}", true);

4. Build a custom S control to house the SWF file, and set the Flash Variables. You will also upload the SWF file to SFDC as part of the S Control.
Example: This example will load the SWF file, and define the Flash Variables (Param#). I also set the SWF background color to match SFDC.

‹html›
‹body›
‹object type="application/x-shockwave-flash" data="{!Scontrol.JavaArchive}" class="movie" width="100%" height="100%"›

‹param name="movie" value="{!Scontrol.JavaArchive}" /›
‹param name="bgcolor" value="F3F3EC" /›
‹PARAM NAME=FlashVars VALUE="salesforcesession={!API.Session_ID}&param1={!Opportunity.Name}&param2={!Opportunity.StageName}&param3={!Opportunity.Amount}&param4={!Opportunity.CloseDate}&param5={!Opportunity.Account}&param6={!Opportunity.Link}"›

‹img src="http://path-to/noflash.gif" width="100%" height="100%" alt="No flash installed" title="No flash installed" class="image" /›
‹/object›
‹/body›
‹/html›

5. In your Xcelsius dashboard, bind the Flash Variables based on the naming convention you used. In my case, I named them param1, param2, etc.


Hopefully, this has left you with some good ideas, of how a simple dashboard can really add value to your end users, specifically for SFDC.

I do have to give some credit to the guys at Salesforce.com, for helping me get my syntax correct for the first Inline S Control. Sorry guys, I can’t post the source files for the web service or Xcelsius file for this one. I can only help you along with how to build it.

Embed a SWF in Excel

by Ryan Goodman

Often I get questions about placing an Xcelsius generated SWF inside of an Excel file. If you go through this tutorial, you will truly appreciate the single click export option in Xcelsius. In covering how to import a SWF into Excel it is important to understand that there is no way to connect the SWF to the Excel data. When you generate a SWF from Xcelsius, it is completed self contained as is not tied back to the originated Excel file in any way. All of the data, logic (Excel formulas converted to Action Script), and connectivity mapping is contained inside of that SWF. With that said, here is how you can take a SWF file and import it into Excel, though the process is the exact same for PPT, or Word.
Download Source Files

1. Open the Control Toolbox by clicking on View>Toolbars>Control Toolbox
2. In the control toolbars menu, you have an icon called More Controls
3. Scroll down and click on the menu item called Shockwave Flash Object. This will insert a container where you will load and resize your SWF
   
4. Right click on this container and click on the menu item: Properties. Note that sometimes you may get a weird behavior with this container in order to get the correct right click menu. Reference the You may need to click in the empty space on your document before right clicking on the container.
   
5. Click on the top field, (Custom), then click on the ellipse button (…).
   
6. In the property page popup, you will need to fill in the absolute or relative path to your SWF file.
7. Before embedding the SWF you want to make sure that the path you entered worked. Click OK to temporarily exit the properties popup window.
8. Resize your container and your SWF should load inside, though it will not work quite yet. As long as you see the dashboard, it worked. Now you need to embed the SWF so you can move or email the Excel file.
9. Go back to the properties popup box and check Embed Movie.
   
10. Now you need to activate your SWF so you can use it. Back in your Control Toolbox, there is a design mode button. You will need to click on it to Exit Design mode, and now you can use your SWF.

Dashboard Design and Deployment Best Practices with CX: Part 3- Finalize and Connect your Dashboard

by Ryan Goodman

Follow Excel best practices
Now that you are transitioning from your mockup toward your final production dashboard, you will want to start incorporating some Excel best practices designed specifically for use with Xcelsius. These best practices will help you couple your excel based model with live data. Excel best practices will allow you to scale your dashboard as you add and remove metrics. Click here to view the Excel best practices whitepaper.

Utilize summarized data
Though it has not been stressed through the other best practices, connectivity is just as important as the visual dashboard itself. I many cases, the data will originate from a database or warehouse. When building a connected dashboard with Crystal Xcelsius, it is extremely important that the data streamed to the Xcelsius dashboard is summarized. While Xcelsius can do some calculating and summarization during runtime within the SWF, it is best to utilize the web services or reporting tools to do the heavy lifting (calculations and summarization). While the volume of data, complexity of calculations, and refresh frequency can dictate which connectivity method is used, it is important to understand that you want the final range of values streamed to the dashboard to be summarized as close as possible to the information you will visualize.
To ensure you can visualize all of the needed information, there are several ways to summarize the data:

• Use parameterized queries to stream data to the dashboard based on the end users’ selections
• Use reporting tools like Crystal Reports or MS Reporting Services to do the heavy calculations and summarization. Xcelsius supports both of these solutions as data sources.

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.