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.
March 2011 - Posts - Tip of the Week

Tip of the Week

March 2011 - Posts

  • Creating Custom Reports with Script Controls - Part II

    In last week’s post, we learned how to take data from table-based visualizations and create custom reports from them, using images and stylesheets.  In this week’s post we will look at how to take the non-table based visualizations and export them as images which we can use in our custom reports. 

    To do this, we will need to use a Script Control and call the render method off a visualization. This method will render the visual to a specified graphics context. It takes in two parameters: a System.Drawing.Graphics object and a System.Drawing.Rectangle object.

    The following code will create the Graphics object and populate it with the necessary information:

    from System.Drawing import Bitmap,Graphics
    bm = Bitmap(300, 300)
    g = Graphics.FromImage(bm)

    NOTE: The height and width can be passed in from Property Controls, allowing the user to determine the size of the exported image.

    We then need to create Rectangle object to render the graphic to.

    from System.Drawing import Rectangle, Point
    r = Rectangle(Point(0,0), bm.Size)

    Finally we need to call the Render method and save the graphic to a specific location. In next week’s post, we will post this to a known location for a complete export report. In this week’s post, we will show you how to save it to a temp file. This code snippet will take in both the Graphics object (g), Rectangle object (r), and Bitmap (bm)  we created earlier.

    from Spotfire.Dxp.Application.Visuals import VisualContent
    from System.IO import Path
    tempFolder = Path.GetTempPath()
    tempFilename = Path.GetTempFileName()
    fullPath = Path.Combine(tempFolder, tempFilename)
    vc = viz.As[VisualContent]()
    vc.Render(g, r)
    bm.Save(fullPath)

    The complete script is shown below:

    from System.Drawing import Bitmap, Graphics, Rectangle, Point
    from Spotfire.Dxp.Application.Visuals import VisualContent
    from System.IO import Path

    tempFolder = Path.GetTempPath()
    tempFilename = Path.GetTempFileName()
    width = 300
    height = 300

    fullPath = Path.Combine(tempFolder, tempFilename)
    vc = viz.As[VisualContent]()
    bm = Bitmap(width, height)
    g = Graphics.FromImage(bm)
    r = Rectangle(Point(0,0), bm.Size)
    vc.Render(g, r)
    bm.Save(fullPath)

    Applying Styles 

    With the image now saved as a png file, we can apply any style to it that we want.  Here are a couple of samples created using Cascading Style Sheets.

    The first example will overlay some text, maybe a title, or comments ontop of the exported image:

     

    <style>
    .basicOpacity{
    opacity:.50;
    filter:alpha(opacity=50);
    filter:"alpha(opacity=50)";
    }
    .imghover a:hover img { filter: alpha(opacity=50);-moz-opacity: .5;opacity:0.5; }
    .image {position:relative; width:172px; height:216px; margin:0;}
    .text {position:absolute; top:220; left:200; width:230; height:75; background:#fff; padding:5px;}
    .text {filter: alpha(opacity=70);-moz-opacity: 0.70; opacity:0.7;}
    .words {position:absolute; top:230px; left:200; width:230; height:75; background:transparent; padding:5px;text-align:center;}

    </style>

    <body>
    <div class="image"><img src="exportImage1.png"><div class="text"></div><div class="words">Outstanding Cases by Process Type<BR>03-21-11</div></div>

    </body>

    The second example will add a custom watermark image to your exported image:

    <STYLE>
    #watermark_box {
    position:relative;
    display:block;
    }
    img.watermark {
    position: absolute;
    top: 4px;
    left: 4px;
    opacity:.50;
    filter:alpha(opacity=50);
    filter:"alpha(opacity=50)";
    }
    </STYLE>

    <div id="watermark_box">
    <img src="exportImage1.png" />
    <img src="watermark.png" class="watermark"  />
    </div>

     

    In next week's post, we will combine what we discussed last week and this week to create a custom report generator using Property and Script Controls, which works in Spotfire Professional as well as the Spotfire Web Player.

  • Creating Custom Reports with Script Controls - Part I

    Last week we discussed how to take an aggregated table visualization, like a cross table, and export the raw data using a Script Control. We accomplished this by using the ExportText() method on a table visualization object (Table, Cross Table, Summary Table).  One other use of the ExportText() method is if you want to build custom reports, either to display directly inside Spotfire or to export as a standalone report.  This will be the topic of this week’s tip.

    We will go over three steps: formatting the report data,styling the report data, and then displaying the report either inside Spotfire or outside as a separate web site/component or as a printout.
     

    Formatting the Report Data
    There are a variety of ways you can format and display the report data.  If the table you want to display is not too long, you can use the ExportText() method and generate an HTML table which displays all the information.  You can then style the table to make it look visually appealing, and also use your companies branding and color schemes, including footers, headers, logos, links, etc… 


     
    From last week, we learned how to use the ExportText() method to export the crossTable data to a temp file, which we could then load back into Spotfire.

    writer = StreamWriter(tempFilename)
    crossTable.As[CrossTablePlot]().ExportText(writer)

    This week, we will use the same ExportText() method, but instead of writing to a tempfile to load back in to Spotfire, we will write it to a tempfile, then build a custom HTML table using the data from the tempfile as input.

    The text will be exported as a tab delimited data. Once we have our data in a tempfile, we can open that temp file in python using the file object, and then loop through it.

    #Temp file for storing the cross table data
    tempFolder = Path.GetTempPath()
    tempFilename = Path.GetTempFileName()


    #Export CrossTable data to the temp file
    writer = StreamWriter(tempFilename)
    crossTable.As[CrossTablePlot]().ExportText(writer)
     

    #Open the temp file for reading
    f = open(tempFilename)

    The first thing we want to do it output the column headers, so we can call the readline() method on the file handle (which will read the first line in this case) and split the line based off the tab delimiter:

    htmlReport = " <TABLE>\n"
    htmlReport += "<TR><TH>"
    colHeader = "</TH><TH>".join(f.readline().split("\t"))
    htmlReport += colHeader.strip()
    htmlReport += "</TH></TR>\n"

    We can then loop through the remaining lines in the file and build the body of our HTML table, one row at a time:
     

    for line in f:
            htmlReport += "<TR><TD>"
            htmlReport += "</TD><TD>".join(line.split("\t")).strip()
            htmlReport += "</TD></TR>\n"
    f.close()
    htmlReport += "</TABLE>\n"

    The complete script to do this is shown below. It assumes we have one parameter called cTable which points to our original cross table in Spotfire.

    from System.IO import Path, StreamWriter
    from Spotfire.Dxp.Application.Visuals import CrossTablePlot

    #Temp file for storing the cross table data
    tempFolder = Path.GetTempPath()
    tempFilename = Path.GetTempFileName()

    #Export CrossTable data to the temp file
    writer = StreamWriter(tempFilename)
    cTable.As[CrossTablePlot]().ExportText(writer)

       
    f = open(tempFilename)

    htmlReport = " <TABLE>\n"
    htmlReport += "<TR><TH>"
    colHeader = "</TH><TH>".join(f.readline().split("\t"))
    htmlReport += colHeader.strip()
    htmlReport += "</TH></TR>\n"
    for line in f:
            htmlReport += "<TR><TD>"
            htmlReport += "</TD><TD>".join(line.split("\t")).strip()
            htmlReport += "</TD></TR>\n"
    f.close()
    htmlReport += "</TABLE>\n"

    print htmlReport

    The output will look like the following: 

     

     

    If the data you want to display is not in a table visualization (table, cross table, or summary table), you can use either S+ or R to run some calculations on your raw data, returning the new data summaries as a table, and then use that table for the report. This makes a nice solution if you want to just show a summary of your data for a dashboard or scorecard.  Finally, if you do not have access to S+ or R, but you do have the SDK, you can also use the full SDK to build a generic calculation engine inside Spotfire which will perform the summary calculations and then you can access that data directly to display in the HTML table. If people are interested, we can explain how to do either or both of these concepts in future tips.

    Styling the Report Data
    We have our HTML Table now displaying either  the cross table data, the summary table data, or even just a regular table (if we create that table from calculations using either the SDK or S+ or R).  But since its HTML, we can apply some nice styles to it, including images, headers, footers, comments, and stylesheets.

    One option is to include an inline style tag for each table row and cell.

    for line in f:
            htmlReport += "<TR style='background-color: #CCD9FF;font-size:9pt'><TD>"
            htmlReport += "</TD><TD>".join(line.split("\t")).strip()
            htmlReport += "</TD></TR>\n"

    Another option is to link to an external style sheet to help render the styles. NOTE: This will currently only work if the output HTML file is displayed outside of Spotfire as external style links are disabled in Text Areas to support offline usage.

    Displaying the Report in Spotfire
    You may choose to display this report inside Spotfire to be viewed in Spotfire Professional or via the Spotfire Web Player (or the Spotfire Enterprise Player).

    You can do this by printing the HTML content to a Text Area. Assuming you pass in a given text area as a parameter called textArea, the script would look like the following:

    from Spotfire.Dxp.Application.Visuals import HtmlTextArea
    textArea.As[HtmlTextArea]().HtmlContent = htmlReport
     


    Below is another example which shows the exported summary data for a call center along with some gauges (also done via a script) to create a nice visual report/dashboard as the cover page to the analysis file in Spotfire. 
     

    NOTE: These reports are all dynamic, so if the script is setup properly, the author just needs to click on button and it will generate or update the report text areas or web details panels and it will work in all Spotfire clients, including the Web Player. 

    As we mentioned earlier, the approach listed above requires the styles to be inline. If you have the SDK, you can modify the Web Details custom extension which ships with it, to display this report in a Custom Panel in Spotfire Professional, and you can just link to a stylesheet (but this solution will not work in the Web Player). The image below shows a sample of the Web Details Custom Panel displaying our report in Spotfire.
     


    Displaying the Report outside Spotfire
    If you wish to display the report outside of Spotfire, you can have the script output to an HTML file. Assuming you want to write it to a local file, you can use the code below:

    filename = "c:/temp/testReport.html"
    FILE = open(filename,"w")
    FILE.writelines(tblHtml)
    FILE.close()

    This will allow you to link to an existing stylesheet rather than displaying all styles inline.  One advantage of this approach is you can define a style sheet for viewing and a separate style sheet specific for printing.

    With a little bit of Python knowledge, you can even have Python upload the HTML file to a known location on a Web Server, so that the users can just point to the Web server and always get the latest information.  This would make the integration of these reports into portals, like Sharepoint, relatively easy and automated.  At a minimum, you can host the report as a standard HTML file with its own URL and custom header and footers.   This would be similar to a reader-only version of Spotfire which just displays reports, but  would require someone with a Spotfire Professional or Spotfire Web Player license to trigger any updates.

    What's Next 

    In next week’s tip , we will extend this example,  to show how to export images of visualizations using Script Controls. Then, in the following tip, we will put it all together and include a custom report generator which will create a pixel perfect report, which includes stylesheets, comments, and a few other bells and whistles like which filters are being used. It can even be extended to create a WYSIWYG report generator interface.

    For help on how to build your own custom report generator using Script Controls, either consider our Script Controls training course, or hire our Professional Services group to do this for you.

    NOTE: A BI/analytics vendor ranking is slated to come out in late April/early May and we’d appreciate your help gaining awareness for Spotfire as an excellent tool for data analysis!

    Analyst Howard Dresner is currently surveying the market for his Wisdom of Crowds BI Market Study 2011.  This is a key third party, annual ranking of BI/analytics vendors, and we’d like your help in answering the questions and helping Spotfire get more awareness.

    Here’s the survey link you can visit to answer the survey:
    http://www.surveymonkey.com/s/woc2011-spotfire

    This collector link will be open from February 28th until April 2nd, 2011.  Thanks for anyone who takes the time to complete the survey!

  • Displaying Cross Table data as a new Data Table

    When using a Cross Table to display various measurements and calculations on your data, it is sometimes useful to be able to use the aggregated data elsewhere in your document.  This could be so you can use it in other visualizations, or so you can build custom reports with the data.  Using a Script Control, you can programmatically access this data and add it back into Spotfire as a new Data Table, which will then allow it to be used in other visualization types and other reports.

    Assume we have the following Cross Table:

    The following script will take in one parameter, which is a reference to the Cross Table above:

    from System.IO import Path, File, StreamWriter
    from Spotfire.Dxp.Data import DataTableSaveSettings
    from Spotfire.Dxp.Application.Visuals import CrossTablePlot
    from Spotfire.Dxp.Data.Import import TextFileDataSource
    from Spotfire.Dxp.Data.Import import TextDataReaderSettings
    from Spotfire.Dxp.Application.Visuals import TablePlot
    from Spotfire.Dxp.Application.Visuals import VisualTypeIdentifiers

    #Temp file for storing the cross table data
    tempFolder = Path.GetTempPath()
    tempFilename = Path.GetTempFileName()

    #Export CrossTable data to the temp file
    writer = StreamWriter(tempFilename)
    crossTable.As[CrossTablePlot]().ExportText(writer)

    #Add tempfile back into Spotfire using TextFileDataSource
    readerSet = TextDataReaderSettings()
    readerSet.Separator = "\t"
    readerSet.AddColumnNameRow(0)
    textDataSource = TextFileDataSource(tempFilename, readerSet )
    myNewTable = Document.Data.Tables.Add("CrossTableData",textDataSource)

    #If you want to change the data table to be embedded and not linked, use the following code
    settings = DataTableSaveSettings (myNewTable,False, False);
    Document.Data.SaveSettings.DataTableSettings.Add(settings);


    When the script is executed a new Data table is added, which is displayed on the top of the image below: 

    In next week's tip we will learn how to take this new Data table, or any Data table, and build a custom report, using colors and styles, and images, which will print out much nicer than the raw Data table would.

Syndication

Other Spotfire Blogs

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