The Spotfire Community is moving to TIBCOmmunity and this forum location has closed. During the transition, you can still search the old forums but posting has been disabled. We encourage you to pick up the discussion at the new Spotfire community on TIBCOmmunity.
June 2010 - Posts - Tip of the Week

Tip of the Week

June 2010 - Posts

  • Embedding Google Charts inside TIBCO Spotfire

    In an earlier post, we discussed how to add Horizontal Bar Charts and other charts into TIBCO Spotfire using S+ or R.  In this related post, we will show you how to add Horizontal Bar Charts and other charts into TIBCO Spotfire using Google Charts.

    Google Charts is a simple yet extensive way to create a Web based chart. Google creates a PNG image of the chart, typically from data and formatting parameters in an HTTP request, although it also supports making the request in a SRC attribute of an image tag.

    Using a Text Area and Script Controls in TIBCO Spotfire, we can access the necessary data and send it to Google, to render one of the many chart types they have.

    Assume we have a data set that contains sales data for each salesperson.  We can gather the sum of sales data for each salesperson , using a Script Control, and can send it into the SRC attribute for the Image, like so:
    <IMG src=",1085,1393,345,2514,2128&chxt=x,y&chxl=1:|Alexander|Amy|Barry|Brenda|Collin|Dwayne|&chxr=0,2600,20&chds=0,2600&chco=4D89F9&chbh=35,0,15&chg=8.33,0,5,5">

    In the same script , we can then launch a new TextArea and set the content to be the image referenced above:

    from Spotfire.Dxp.Application.Visuals import HtmlTextArea
    from Spotfire.Dxp.Application.Visuals import VisualTypeIdentifiers
    from System.Text import StringBuilder

    sb = StringBuilder()
    sb.Append('<IMG src=",1085,1393,345,2514,2128&chxt=x,y&chxl=1:|Alexander|Amy|Barry|Brenda|Collin|Dwayne|&chxr=0,2600,20&chds=0,2600&chco=4D89F9&chbh=35,0,15&chg=8.33,0,5,5">')

    textArea = Document.ActivePageReference.Visuals.AddNew[HtmlTextArea]()
    textArea.HtmlContent = sb.ToString()

    Google Charts have many chart types. You can even modify the horizontal bar chart to act as a Gantt Chart.

    One limitation of this approach where we use the SRC attribute to specify the parameters is that there is a maximum length for the URL used.  This limitation is browser dependant but typically is around 2000 characters.

    If you run into this, you will have to create the Google Chart using an HTTP Post instead of an HTTP GET (the URL method). This can be accomplished, but does require the SDK. Inside the SDK, there is a sample visualization called Web Details, which actually embeds a web page inside Spotfire. You can configure the Web Details to use HTTP POST to display the same images as shown above..

  • Adding Categories to your Data

    TIBCO Spotfire Professional provides a variety of ways to add categories into your data above those that are included in the raw data table. Four methods we will discuss are Columns from Calculations, Tagging, Columns from Binning, and Filter Out/To.

    Tagging and Calculated Columns can both be used to create additional categories in your data. For example, I can create a tag to place rows into a new category with 3 values; low, normal, and high. The benefit of this is I now have a new filter and column created inside my data table that I can use to color by, size by, and perform other operations on.

    In the example below, we create a new category in our data table called ‘Utilization Type’ by tagging.



    Similarly, I can create either a calculated column or custom expression to do the same using either an if statement or a case statement (we will see an example in a bit).

    Which one should I use? Well it depends on your data and what your purpose is.

    Calculated Columns

    If you plan on replacing data, and you can easily define your ‘categories’ using an algorithm, then I would choose to use a calculated column or a custom expression. Calculated columns and expressions both get re-calculated by default when the original data table is replaced automatically. This makes this option more efficient, so that you do not have to repeat performing the same tagging sequence.

    The following is an example of an expression created to do the same things as the tagging above:

    case when ([Utilization Type])=("Vacation") then "Vacation" when ([Utilization Type])=("Production Task") then "Production Task" when ([Utilization Type])=("Self Dev Task") then "Self Dev Task" when ([Utilization Type])=("Travel") then "Travel" when ([Utilization Type])=("Support") then "Support" when ([Utilization Type])=("Holiday") then "Holiday" else "Teaching" end

    NOTE: You will need to remember if you add more values in the category, that you will need to update your expression, otherwise it will be incorrect and new values will be added to the ‘Teaching’ value as that is after the else statement in the expression.


    Tagging is commonly used when you need to place your data into additional categories, that may not be easily computed using an algorithm. For example, you may be running through your analysis of stock data and you want to tag records as ‘buy, ‘hold’, and ‘sell’. Unlike the Utilization Type, your analysis for putting records into ‘buy’, ‘sell’, and ‘hold’ is more complex and involves some ad-hoc analysis, so tagging is the most efficient method. Since this analysis is used in more of an ad-hoc fashion, the tags are saved with the analysis but not automatically re-applied when new data is replaced or reloaded.

    You can still enable them to work when you reload data, but you need to specify a key columns to uniquely identify the records. This topic is described at the following URL:

    Binned Columns

    Similar to Calculated Columns, you can also define new categories by using the New Column by Binning dialog. (available from Insert > Binned Column…). From this dialog, you can add a new column that places your data into predefined bins (categories), using a variety of methods.

    Methods include bins by specific limits, even intervals, even distribution of unique values, standard deviations, and substrings. The type of methods available are dependent on the data type of the source column.

    This capability is typically used when you have a specific algorithm you wish to apply to create your categories, but it is more advanced than simple if or case statements as shown in the earlier calculated column example. It is also very useful when you want to take continuous data and convert it to categorical data to perform further analysis.

    NOTE: You can still create bins using the expression language in a calculated column (see image below), but the Insert Binned Column dialog is an easier to use interface rather than entering the code manually.


    Filter to and Filter out

    Finally, if you are trying to create a column that contains only two categories, it is possible to do this by first marking the records of interest, and then right-clicking , and selecting ‘Marked Rows > Filter To’ or ‘Marked Rows > Filter Out’.

    Both options will create a new Filter. The first one will filter out every row except the ones marked, and the second one will filter out the marked rows only.

    This functionality can be used in a variety of ad-hoc analysis techniques, including removing outliers or other data points to see how the rest of the analysis changes. It can also be used, as we mentioned earlier, to create a column with 2 categories. The default name of the filter can be changed to something more descriptive by right-clicking in the filter and selecting ‘rename’ or by going to ‘Edit > Column Properties’.

    What to extend it even more?

    While the new columns are stored with the analysis file, you may want to persist this information outside the file so that it can be leveraged in other applications and other analysis files. One such solution is to use a combination of Information Services and our SDK to create a customized tool that will write back the new columns into the source database. Information Services can be setup on the Spotfire Server to run stored procedures, so you can setup a stored procedure which accepts values and performs an insert into the database. You would then need to use the SDK to create a tool , which the user can launch to specify the column(s) they wish to write back. The tool would grab the columns, and pass them into the stored procedure as input parameters. Alternately , you could use a Script Control inside a specific Spotfire file rather than writing a Custom Tool.


    To summarize, there are a variety of techniques for creating additional categories in your data table. While many of them can be interchanged, each has its own strength and weakness depending on the scope of the analysis (a quick ad hoc analysis or a persistent guided analysis), the number of categories required, and the algorithms used to create the categories.

    Any additional thoughts or information on creating categories, reply to this post with your thoughts.

  • Horizontal Bar Charts in TIBCO Spotfire through TIBCO Spotfire Statistics Services

    Ever want to use a  Horizontal Bar Chart in TIBCO Spotfire?  Sure, you can use the SDK to build a custom visualization, but that is alot of overhead.  With the ability to execute S+ and R from within the Spotfire platform, there is an easier way to accomplish this capability: using Data Functions. Ideally this should be setup as a detailed visualizations, but can be setup to be main visualizations as well.

    First, you will need to register a Data Function using Tools > Register Data Functions from within TIBCO Spotfire Professional.  The Data Function will be an S+ Script , which will generate a Horizontal Bar Chart and output it as a png image. The script is shown below.

    The script will take in two input parameters. Both are required and will be of type Column.  These will be the columns we want to display on the X and Y axis of the Horizontal Bar Chart.

    The script will output one parameter. It will be of type Value. This will be the Bar Chart , being returned as a PNG image.


    Once we have created the S+ Script, we now can include it in a given Spotfire Analysis File using the Data Functions tool from Tools > Data Functions.  Here, we must map the expected input parameters to values from the analysis document.
    In our case, the current analysis document has the following columns:

    We would like to have the Bar Chart display the ‘Total Amount of Purchases’ for each ‘Store Location’.  To generate this , we need to send in both of those columns as the input to the script.  First, we send in the Store Location column. Since we want to have this be a details visualization, we select to only send in marked rows to the script.

    Next, we send in the ‘Total Amount of Purchases’ column. However, this is a currency column in Spotfire and S+ does not support currency data types. To resolve this, we define the input as an expression instead of a column, and then create an expression that casts the ‘Total Amount of Purchases’ column to an integer.


    For the output of the script, we want to attach the image being returned to a document property of type Binary.

    Lastly we need to create a Property Control from within a Text Area to display the image.  For this we will pick a Label Property Type.


    Once done, we see the finished product.  Mark records in the Scatter Plot and then a Horizontal Bar Chart will be displayed as a Details Visualization.

    Everytime you update the marking, the Data Function will be re-executed and an updated Horizontal Bar Chart will be displayed. This works since we checked the 'Refresh function automatically' button.

    Interested in learning more about Data Functions? Take our course specific for learning Data Functions for either S+ or R: TIBCO Spotfire Data Functions : Executing S+ and R code from TIBCO Spotfire. The course can be taken using our blended learning model for only $600 per person. Blended training gives you access to our online training portal to consume asynchronous materials including for 30 days. During those 30 days, you have access to an instructor to help answer any questions you may have.  When the 30 days are up, there is a live webinar typically lasting 2 hours to review concepts, do live demos, and also to answer any questions.  This model is well suited for adult learners as you can break up the learning over time to retain more and also have it fit into a busy schedule.

  • Cascading Property Control Drop downs

    Last week we discussed how to create cascading Property Control Drop downs using Data On-Demand.  This is done using Information Links. If you do not use Information Links, this week’s tip will show you how to accomplish this without using Information Links. All the data will be loaded into Spotfire at once, but you can use the drop downs to select what information to show in given visualizations.

    To do this we make use of Calculated Columns.  Let’s assume we have the same Data Table from the previous tip, which contains ‘Region’, ‘State’, and ‘City’, along with other Columns, and we want to allow the user to select which Region to load via a Property Control, and then display States from that region in a second Property Control.  Finally after selecting a State from the second Property Control, a visualization is updated to display Towns and Cities from the selected State.

    First step is to create a drop-down list Property Control to display unique values from the Region column.


    Next step is to create a Calculated Column called ‘Filtered State’. In the expression we should check if the Region Column value equals the value specified in the property we created earlier. If it does, it will output the value from the State Column.

    if ([Region] = "${whichRegion}",[State],null)

    Next step is to create the second Property Control drop-down. This one will display unique values in the newly created Filtered State Column.

    The final step is to create a second Calculated Column called ‘Filtered City’. In the expression we should check if the State Column value equals the value specified in the property we just created. If it does, it will output the value from the City Column.

    if ([State] = "${whichState}",[City],null)

    Now we can create any visualization and use the ‘Filtered City’ column to display the Cities and Towns from the selected State from the selected Region.

    Interested in learning more, take our 3.1 Delta training or our end user training courses either onsite, regionally, or using our blended online/webinar model.


Other Spotfire Blogs

©Copyright 2000-2011 TIBCO Software Inc | Privacy Policy | Terms of Use I Blog I Contact Us I Content Center