This blog is my personal space for technologies and techniques for presenting information in the form of visual analytics and interactive dashboard applications. I consider myself an Xcelsius evangelist, but most importantly an advocate of enabling technologies that make data visualization applications easy to produce. Much of the information posted on this blog will feature Xcelsius tricks and best practices based on my technical expertise. This is mirrored from http://ryangoodman.net/blog
Showing posts with label Xcelsius Tricks. Show all posts
Showing posts with label Xcelsius Tricks. Show all posts
The final part on my dissection of the Xcelsius OBJECT is a variable called CEWebServiceURL. This Flash Variable is used specifically with the Live Office component and overwrites the “Live Office Web Services URL. Regardless of what you enter inside the input box shown above, when the SWF is loaded inside of Infoview, the Live Office connector component will dynamically consume a webservice URL originating from the CEWebServiceURL flash variable. In this case, you do not need to do any configuration of Flash Variables for this to work, similar to CELogonToken.
What does this mean for Life Cycle Management? For quite some time, I have heard Xcelsius users complain about the inability to bind the Live Office component URL, and how that makes life difficult for migrating Xcelsius objects between BOE repositories. The good news, is with the CEWebServiceURL, the bindable URL is no longer needed. *Note, that this option is only available with BOE XIR2 SP2 and up.
What about QaaWS? The bad news is a Web Service connector is not configured by Xcelsius to consume the CEWebServiceURL. The good news, is that since the URL for this component is bindable, you can easily leverage the CEWebServiceURL to provide the system name. I have mocked up a simple Excel sheet that evaluates the CEWebServiceURL, then matches it to the correct system name. For those of you not using SP2, you could just as easily use the CELogonToken, since it also contains the server name. All you need to do is declare and bind the Flash Variable inside of Xcelsius then use similar logic that you can download here.
Another Option for QaaWS If you always plan on using your Xcelsius dashboard inside of BOE, you can drop the system name and port number from the URL. This will work, assuming you don’t use a reverse proxy or some non-standard application server configuration. I have used this method many times since it alleviates the need for any fancy logic or consumption of Flash Variables.
I hope that this 3 part series has helped you in gaining better understanding of how Xcelsius generated SWFs leverage basic Flash variables and BOE.
This week we are going to talk about a session token that an Xcelsius dashboard (SWF) consumes when it is loaded inside of Infoview or Dashboard Builder (Business Objects XIR2). The CELOGONTOKEN is the variable name that is assigned to a session token that BOE creates when you successfully login.
How does Xcelsius consume this token? As you notice, inside of the <OBJECT> tag, there is a Flash Variable called CELOGONTOKEN. When BOE renders the HTML page, it dynamically inserts the current logon token as the CELOGONTOKEN. Any SWF that uses a Query as a Web Service or Live Office connection will automatically consume the CELOGONTOKEN Flash Variable.
How is the CELogonToken used during SWF runtime? SWF files that contain a QaaWS or Live Office connectors will automatically search for the presence of a logon token when the connector is executed. With each transaction from the dashboard to BOE, this token is passed back to BOE. If the SWF does not detect a CELOGON token, (when it is loaded outside of Infoview or Dashboard Builder) it will display a login dialogue box.
So now that we have de-mystified the magic that is Xcelsius, let’s think about how we can leverage this knowledge. First, we use Flash Variables and the login token to solve the single sign on issue for multi-layer or nested SWF files within a dashboard. The second use is an Xcelsius dashboard using single sign with BOE outside of Infoview. While I have seen several implementations of this, unfortunately I do not have anything that I can share at this point. Hopefully those of you who are BOE SDK gurus run with this new understanding of how Xcelsius consumes the logon token. If anyone is interested in working together to put something together, I am more than interested to provide the Xcelsius knowledge…
Later this week, I will finish up this 3 part analysis of the Xcelsius <OBJECT> with the CEWebServiceURL and how it helps to improve life cycle management.
The first and most obvious observation that you make when reviewing this example code is the presence of all attributes twice; one set within the <OBJECT> and one set within the <EMBED>. <OBJECT> is used for Internet Explorer to support the Flash player, which is an Active X control. <EMBED> is for other browsers that support other Flash Player installs. There is a wealth of content on the internet about these methods and using attributes, but I will move on to the 2 most important one to us: SWF source file path and Variables. Below is the code I will be elaborating on…
SWF Source Path In this case I am referring to the physical location where your SWF file reside, relative to the HTML page that it is loaded inside. In this scenario where we are using of BOE XIR2, they use a relative path using “opendoc/documentdownload” which uses the same syntax as opendoc.jsp. The document download syntax declares an iDocID to identify the SWF source file. You can re-use this code when loading Multi-Layer (Parent/Child)dashboards inside of BOE.
Variables An Xcelsius generated file is capable of consuming variables from HTML using the Flash Variables feature. When loading flash variables you want to separate each variable with an “&” symbol, and individual values should be comma separated. If you look at the code below the syntax is slightly different in the <OBJECT> from the <EMBED>. The next tow blog articles will discuss not only how the SWF leverages these variables, but how you can take this new knowledge further when you plan your dashboard project.
Recently I worked on a project where we were required to use the list builder. I usually shy away from this component for 2 reasons…
It takes up a lot of screen real estate. While this is easily solved using dynamic visibility this is the minor issue.
The clumsy user experience. When you make selections, you are required to click Update Filters to commit your selections. In this user control there is no need to save the user from them selves. Because you have to manually commit the filters it only causes headaches for end users.
There is no ignore end blanks.
Here is how I went about addressing these issues to achieve the following result..
There are a few things you can do to help reduce how much screen real estate takes up. The first is to replace the Add and Remove labels and replace them with ">"and "<". The second is to simply reduce the size of all labels.
There is no way to trigger this functionality, but I did come up with some method of notifying the status of their filter usage. In this case, I simply put a label on top of the list builder stating how many filters have been selected. It serves as a reminder to click the update filters button. Hopefully one day we will see this component with the ability to auto-trigger the update filter functionality and save some screen real estate.
For the absence of ignore end blanks, there is nothing that you can do.
When using the chart drill down capability inside of Xcelsius, there is a limitation prohibiting the configuration of a multi-series chart to insert into the same range. In other words… Xcelsius will not allow a user to click on bars from 2 different series to trigger 1 action, which can be a problem.
To invoke this problem, enable drilldown, select an insert-in cell, change the series dropdown, and select the same insert in cell. You will get an error message that reads the following:
“Insert In Range overlaps with other series’ Insert In Rage/ Please select non-overlapping Ranges for each series.” There is a workaround to get this working using the “Insert Series In:” option. By capturing the series that is currently selected, we can perform a lookup to retrieve the correct data.
I have seen hundreds of dashboards that all look similar for 1 reason…People who use Xcelsius don’t want to spend a lot of time customizing the look and feel for their dashboard. So what happens when you assemble components on a screen and want your dashboard to take on a different look from the standard default Xcelsius look and feel?
Within Xcelsius there is a concept of skins, which simply changes the look and feel without effecting any functionality you have created within your dashboard. To change the skin, go to View>Change Skin. You are then presented with several skin names that will globally change how all components look. Note that there is a default color scheme for every skin, which can be changed manually or through Styles (View>Change Style.)
I will give a brief overview of each skin, and my personal opinion of how and when I use each one…
Aqua Aqua is a skin that emulates the Apple skin used for web browsing and an older version of the operating system. Aqua uses a consistent blue color scheme, which people seem to like, but sometimes the gradients and visual effects can also be a little too much to visually digest. I like using this one sometimes because the out of the box backgrounds layer together nicely.
Aero Aero is a aviator type of theme that shows the most when you look at the gauge component. The charts and components all have extra gradients and glare effects which may be a little over the top for some, but the default color scheme is vibrant and pleasing to the eye.
Elan Elan is one of the original Xcelsius skins which use a somewhat monotone blue color scheme. I see Elan and Aero as similar skins, because there are limited out of the box backgrounds, and the graphs look similar.
Graphite Graphite was released with version 4.5 and has a nice collection of backgrounds that can be mixed and matched. While the default colors are dark, the skin itself has nice looking charts. The only problem I find with this skin is some of the transparency for dropdown components.
Halo Halo is by far my personal favorite simply because it does not have all of the gradients featured in the other skins. I am a minimalist at heart, so this is as close as I get with Xcelsius, but still gives some depth within the selector controls. Most people hate the default lime green color but, once I change that, it always looks clean.
Here is the situation…You build your Excel logic, import into Xcelsius, bind all of your components, hit Preview for the instant gratification Xcelsius brings, and your model does not work right. With many dependencies between Xcelsius component bindings and underlying Excel logic, it could be a nightmare to reverse engineer your model just to find that a VLOOKUP is formatted wrong, or your combo box is inserting into the wrong cell.
Here is a little trick of the trade for troubleshooting your model using a feature inside of Xcelsius called snapshot. When you hit preview, navigate to the portion of your dashboard where your error occurs. While in preview mode go to File>Snapshot>Back to Excel. You will then name your Excel file (do not overwrite your original) and save it to your local hard drive. This Excel file will show the state of your model at the given point in time while you were in preview mode. This means all of your insert-in rows, logic, connectors, etc will have executed, and the Excel snapshot will show you what the underlying spreadsheet looks like. Since you should know your spreadsheet, you can quickly look to see if you inserted into an incorrect cell (this is where color coding would have saved you time), or if a formula is generating an error. As you may or may not know, when you are using Xcelsius you are binding to a copy of your original spreadsheet that you imported. In other words, your XLF is a combination of Xcelsius configured components and your imported spreadsheet snapshot. This is why making changes to the original XLS will not be reflected in your XLF automatically. Hope this helps!
When binding components to cells, specifically the table view component, it will inherit the cell formatting from Excel. This includes the font, color, borders, alignment, and values. Once those cells are bound there is no way to dynamically change that format during runtime. This is unfortunate, specifically for conditional color formatting and value format. In many cases users want to display some type of visual indication within a table view. There is however a way to inject special characters into a table view. If you tried this before you may know that those characters do not display by default. There is a modification to your XLF that will enable these characters to show up.
In Xcelsius, go to File>Font>and click Unicode Font Options. This will allow you to use device fonts instead of embedding the font in the SWF. By default, Xcelsius will actually embed the Verdana font in your SWF which adds about 60K to the SWF file size. By using device fonts, you will now have the opportunity to leverage special characters within a table view. You will also notice the fonts will look sharper since the Flash player will not have to render the anti-alias fonts. There is only 1 pitfall to using non-embedded fonts… an inability to render text vertically. This will affect the X axis in some of your charts when labels are long, or chart y-axis labels. Otherwise you can leverage characters like the arrows I demonstrate below. Download Source Files
Within a Adobe Flash application, or in this case Xcelsius, you can bind variables that are utilized through out the application during runtime. The benefit of Flash variables within an Xcelsius generated SWF is an ability to push values into the model when embedded inside of a HTML page. In most use cases, you would want to use Flash Variables to push global session variables that you may want to consume and re-use. Examples include: Session tokens, user IDs, and parameters passed from other embedded scripts or controls.
First we will define a variable within Xcelsius.
Once Xcelsius is loaded, go to File>Export Settings.
Click on the “Define Variables” button. In this case, I named my value sessionID.
Define your variable name and bind it to a cell
Generate your SWF. Now your SWF file is configured to consume a Flash variable
There are two ways to push the variable into the SWF.
The Adobe Flash player enables a SWF to consume data directly from the HTML for which it is loaded. In a scenario where you are dynamically generating your HTML page, you can pass variables into the SWF on load. You can also use Javascript to dynamically change the variables during runtime, enabling other HTML embedded controls to pass data into the SWF without reloading the HTML page. An HTML nested flash variable would look something like this within an OBJECT tag:
2. The Adobe Flash player also enables a SWF to consume data directly from defined parameters within a browser or path for which it is loaded. This works great for passing parameters from one SWF to another if they are loaded on separate HTML pages.
You can try the example below by entering a value, then launching another HMTL page where we consume the variable. You will be launching a URL that looks something like this: http://ryangoodman.net/blog/flashvar.swf?sessionID=test
Many times, you want your dashboard to query data based on the current quarter, month, day or year. Within Xcelsius you can easily calculate these values. The trickiest part is calculating the quarter since that is not a natively supported function.
First you will declare the current date using the function: NOW().
Next you will pull the month and year from this date using the MONTH() and YEAR() functions.
Now is the trickiest part dictating the current quarter, which is not an available function. ROUNDUP(4*MONTH()/12,0)
With the current quarter, you can now decide if you want to populate other quarters. In my case I want to populate a selector with the current quarter, last quarter, and the three previous quarters. In my example you can see how I took this idea a few steps further.
If you are working with dates within a connected Xcelsius dashboard, you can run into potential issues related to formatting. In most cases, you will require a specific format when you want to pass a date as a parameter to a web service.
Using the calendar component, you can insert and visualize a user defined date. Though you can set the cell format to modify the display of that date, Excel actually stores it using the 1900 date system. This simply means that the date Jan 1, 1900 has a true numeric value of 1, and Jan 1, 2007 has the true numeric value of 39083. The problem at hand is if you bind a web service connector component to a cell that is formatted, it will still use the true numeric value and not the formatted value that you see during runtime. Here is how you can remedy this:
Here is a scenario that many people have…. You have a connected model and the web service returns tabular data, but Xcelsius does not have the ability to lookup multiple rows of data with their selectors. Unfortunately, with Xcelsius 4.5 you have to rely on some Excel skills to assemble a layer of logic that will scale. The good news is I have put together a lightweight solution with the available supported Excel functions for Xcelsius. The bad news is that is painful to explain…
I did my best to point out the key components to make this model work. Your best bet is to download the Excel file and beat it up and look at the comments within the bold titles. I didn't feel that it was extremely important to build an Xcelsius model for this one, but if it would help, shoot me an email and I can put something up. Click here to download the Excel source file
Here is a simple trick that will allow you to reset a dashboard back to its original state when featuring what-if capability. The only limitation in design, is that all of your insert in cells will need to remain contiguous.
As you can see, the original values reside in column C. It normally, we would link our single value components to these original cells. In this example, we are going to link our single value components to our target range in column A. Finally we are going to use a selector to move the data from column C to column A. This way, every time you make a selection, it will affect column A, leaving your original data in column C un-effected. Hopefully this little trick will help you in building calculator based applications.
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:
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.
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.
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
In the toolbar go to Data>XML>XML Source…
At the bottom of the right hand side toolbar click on the XML Maps button.
Click on Add to add a new XML map
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.
With your XML Map added, click OK and you will notice the hierarchy displayed on your right hand side toolbar.
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.
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.
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.
Save your Excel file
Configure Xcelsius to Show the RSS Data
In Xcelsius you will first import your Excel file. Upon importing this file Xcelsius will automatically understand that the Excel file contains XML maps.
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.
Import a List Box selector component and a URL button component (located in Web Connectivity folder).
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.
For the URL Button: Bind the title to B4 and the URL to C4.
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.
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.
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.
Index cell: This index cell will be used eventually by a vlookup function once the rank is identified
Source Data: This is the original source data that we will rank.
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
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.
Rank Number: We will use this rank number as our “K” within the LARGE function.
Rank Lookup: This range uses the LARGE function to find the Nth value (#5) within our Lookup Row (#4)
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).
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.
