Data Dynamics Reports And Twitter

Preface
Tips and tricks series continues! In this post I am going to explain how a Data Dynamics Reports report might be set up in order to show the tweets from arbitrary user's public timeline. The live demo is available here. I can imagine the real-world usage of this feature: say I am HR department manager and in the CV overview report I would like to see the last 3 tweets from the public timeline of the candidates for opened job position. Let's see how a developer can implement such a feature using Data Dynamics Reports.

Using XML Data Provider
Data Dynamics Reports includes XML data provider that can load the data from various sources, particularly it supports xml documents that are located in a web-accessible location. In other hand a user's public timeline can be accessed via RSS feed which is xml document that is located in a web-accessible location. I.e. my public timeline can be read on http://twitter.com/statuses/user_timeline/sAbakumoff.rss. RSS feed document has well known structure: each tweet is presented by <item> tag, its child <description> tag has the tweet text. So...displaying user's public timeline in Data Dynamics Report is pretty simple:
1) Add new Data Source in a report. Set Data Source type to XML.
2) Set the connection string of the newly added data source to "xmldoc=http://twitter.com/statuses/user_timeline/USER NAME.rss"(without quotes). Change USER_NAME to the real-world value.
3) Add the new Data Set in the Data Source.
4) Set the Query of the newly added Data Set to "/rss/channel/item"(without quotes).
5) Add the Fields to the newly added data set. Set the name of the field to "title", set the value of the field to "title".
6) Add the List report item in the report, set the DataSetName to the name of the data set that is added in step 3.
7) Drag the field "title" from the data explorer to the newly added List Report Item.
8) Preview the report, hey presto, it shows the user's public timeline!


Advanced techniques
I've used a couple of other features in the live demo:
1) To limit the tweets list for 5 last messages, I used TopN filter of the data set. I've added one more field to the data set, it's pubDate. The filter expression is "=Fields!pubDate.Value", the filter operator is TopN, the filter value is 5.
2) I parametrized data source query to allow set user name using the report parameter value, the connection string is set to
="xmldoc=http://twitter.com/statuses/user_timeline/" & Parameters!TwitterUser.Value & ".rss"
The complete report is available here.

Tips and tricks: Exporting to CSV using Data Dynamics Reports

General
This is the first in a series of posts that will highlight small and simple but useful undocumented features of Data Dynamics Reports and ActiveReports. I have several topics in mind and hope that more ideas about such features come soon. I will start with the answer to question we were recently asked - is that possible to export the report to CSV format using Data Dynamics Reports?

Tip: use Xml Rendering Extension to get CSV output.
That sounds weird, but it's really not. Let me explain. Data Dynamics Reports allows you to save the data that are shown by a report to XML format. The output XML content does not include the information about report items style, pagination, interactivity, etc., it only includes the data that are grouped, sorted, filtered and displayed in the report's data regions. For example say that a report consists of a simple table that has two columns, the header and the details:

The report output in the viewer would look like below:

But the output that is produced by Xml Rendering Extension looks like:

<Report Name="Ages.rdlx">
  <Table1>
    <TextBox2>Age</TextBox2>
    <TextBox1>Name</TextBox1>
    <Detail_Collection>
      <Detail>
        <TextBox5>30</TextBox5>
        <TextBox4>Jon</TextBox4>
      </Detail>
      <Detail>
        <TextBox5>27</TextBox5>
        <TextBox4>Michael</TextBox4>
      </Detail>
      <Detail>
        <TextBox5>32</TextBox5>
        <TextBox4>Bryan</TextBox4>
      </Detail>
      <Detail>
        <TextBox5>29</TextBox5>
        <TextBox4>Stewart</TextBox4>
      </Detail>
    </Detail_Collection>
  </Table1>
</Report>

The element names in the output XML file can be fine-tuned using the properties on the individual report items, more details can be found on the corresponding help topic. That's all good, but how to get the same data in CSV format? For example:
Name,Age
Jon,30
Michael,27
Bryan,32
Stewart,29
Xml Rendering Extension has XsltStyleSheet setting, you can specify the stylesheet that will be applied to the output XML before producing the resulting document. Therefore you can write the XSLT that transforms XML content to CSV content. It's of course not possible to implement the uniform XSLT for all the reports, but the example for the table-based report where the table has the 1-row header and 1-row details is shown below. It works for any number of columns:

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
    <!--Select the table header content -->
    <xsl:for-each select="Report/Table1/*">
      <xsl:if test="name(current())!='Detail_Collection'">
        <xsl:if test="not(position() = 1)">,</xsl:if><xsl:value-of select="current()" />
      </xsl:if>
    </xsl:for-each>
    <!--Insert the line break-->
    <xsl:text>
    </xsl:text>

    <!--select the table details content -->
    <xsl:for-each select="Report/Table1/Detail_Collection/Detail">
      <xsl:for-each select="current()/*">
        <xsl:if test="not(position() = 1)">,</xsl:if><xsl:value-of select="current()" />
      </xsl:for-each>
      <!--Insert the line break-->
      <xsl:text>
      </xsl:text>
    </xsl:for-each>
  </xsl:template>
</xsl:stylesheet>