How To Create Drilldown Silverlight Graphs in SharePoint with Visifire

In my post "How To Add Awesome Graphs To SharePoint with Silverlight" I explained how you can create Silverlight graphs in SharePoint with help from Visifire. A very cool feature of Visifire is the ability to create so called drilldown graphs. For an example of what a drilldown graph is have a look here.

One question that was asked at the Visifire forum is how to create drilldown graphs based on SharePoint lists, and that was for me the trigger to give it a shot. And it actually worked out great!

So what did I do?

Step 1: First of all I downloaded the drilldown sample application from the Visifire website. In this sample application, the chart can be drilled down till 2 levels. The default chart would show yearly sales of a fictitious company for the period 2006-2008. On click of column representing the sales for an year, monthly sales for that year is shown up. Further it can be drilled down to find product wise sales for the selected month.

The sample uses Javascript API to generate charts. The complete application can be downloaded from here. The code is completely commented and explained wherever necessary. Read the blog post if you need more help.

Step 2: Create a new Custom List in SharePoint that will hold the sales data. I called my list SalesData. The list should have the following fields: Year, Month, ProductX, ProductY, ProductZ. After that you can fill it with data.

Step 3: Create a new page in SharePoint that will hold a Data View Web Part. You can use SharePoint Designer for this, with a Web Part Zone selected, from the top menu click Data View -> Insert Data View. From the Data Source Library in the right hand task pane select the SalesData list and click Show Data. Then select the Year, Month, ProductX, ProductY and ProductZ fields and add them to the Data View. After adding the Data View Web Part just make sure you do not have selected any paging option. Select display all items.

Step 4: Unzip the Visifire sample application that you downloaded in Step 1 and grab "Visifire2.js" and "SL.Visifire.Charts.xap" and add them to the same location as the page you just created in your SharePoint site.

Step 5: In the SharePoint .aspx page that you created in Step 3 you have to locate the following peace of code:

<xsl:template match="/">
 <xsl:call-template name="dvt_1"/>
</xsl:template> 

This code you will replace by:

<xsl:template
match="/"
xmlns:x="http://www.w3.org/2001/XMLSchema"
xmlns:d="http://schemas.microsoft.com/sharepoint/dsp"
xmlns:asp="http://schemas.microsoft.com/ASPNET/20"
xmlns:__designer="http://schemas.microsoft.com/WebParts/v2/DataView/designer"
xmlns:SharePoint="Microsoft.SharePoint.WebControls">

<script type="text/javascript" src="Visifire2.js"></script>

<!-- Code from Step 6 will come here -->

</xsl:template>

Step 6: You just copy all the JavaScipt code from drilldown.html (this file is part of the example from Visifire you downloaded) and place it in the xsl. The same goes for the actual display of the graph. Because it is far to much code to display here I will just show the important stuff. For more details you can have a look at examples that display a simple Visifire graph in SharePoint here and here.

When you look at the JavaScript code in the example you will notice a large array called "sales", and that is were we have to change something to display the data in our SharePoint list. You have to replace the JavaScript code that generates the array with data with the following XSL.

<xsl:text disable-output-escaping="yes"><![CDATA[
<script type="text/javascript">
        var sales = [
        ]]></xsl:text>
<xsl:for-each select="/dsQueryResponse/Rows/Row">
 <xsl:text disable-output-escaping="yes"><![CDATA[{year: ]]></xsl:text>
 <xsl:value-of select="./@Year" />
 <xsl:text disable-output-escaping="yes"><![CDATA[, month: "]]></xsl:text>
 <xsl:value-of select="@Month" />
 <xsl:text disable-output-escaping="yes"><![CDATA[", ProductX: ]]></xsl:text>
 <xsl:value-of select="@ProductX" />
 <xsl:text disable-output-escaping="yes"><![CDATA[, ProductY: ]]></xsl:text>
 <xsl:value-of select="@ProductY" />
 <xsl:text disable-output-escaping="yes"><![CDATA[, ProductZ: ]]></xsl:text>
 <xsl:value-of select="@ProductZ" />
 <xsl:choose>
   <xsl:when test="position() = last()">
     <xsl:text disable-output-escaping="yes"><![CDATA[}]]></xsl:text>
  </xsl:when>
   <xsl:otherwise>
     <xsl:text disable-output-escaping="yes"><![CDATA[},]]></xsl:text>
  </xsl:otherwise>
 </xsl:choose>
</xsl:for-each>
<xsl:text disable-output-escaping="yes"><![CDATA[]; ]]></xsl:text>

The code just loops through your SalesData list, and creates an array from of the data. You do the looping by using the <xsl:for-each select="/dsQueryResponse/Rows/Row"> statement. The only tricky thing is the comma after the last array item, that is why you need the <xsl:choose> element in combination with <xsl:when test="position() = last()">.

When all went well this XSL will generate exactly the JavaScript code from the example but then with the data from your SharePoint list, instead of hardcoded data.

This worked for me and resulted in some cool graphs. When you have any questions or you want the complete .XSL code just Contact me.

Related Posts

• How To Add Awesome Graphs To SharePoint with Silverlight