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!
