Chart Filtering
- by Tim Dexter
Interesting question from a colleague this week. Can you add a filter to a chart to just show a specific set of data?
In an RTF template, you need to do a little finagling in the chart definition. In an online template, a couple of clicks and you're done.
RTF
Build your chart as you would normally to include all the data to start with.
Now flip to the Advanced tab to see the code behind the chart. Its not very pretty but with a little effort you can get it looking a little more friendly. Here's my chart showing employees and their salaries.
<Graph depthAngle="50" depthRadius="8" seriesEffect="SE_AUTO_GRADIENT">
<LegendArea visible="true"/>
<Title text="Executive Department Only" visible="true" horizontalAlignment="CENTER"/>
<LocalGridData colCount="{count(.//G_2)}" rowCount="1">
<RowLabels>
<Label>SALARY</Label>
</RowLabels>
<ColLabels>
<xsl:for-each select=".//G_2">
<Label><xsl:value-of select="EMP_NAME"/></Label>
</xsl:for-each>
</ColLabels>
<DataValues>
<RowData>
<xsl:for-each select=".//G_2">
<Cell><xsl:value-of select="SALARY"/></Cell>
</xsl:for-each>
</RowData>
</DataValues>
</LocalGridData>
</Graph>
Note the emboldened text. Its currently grabbing all values in the G_2 level of the data. We can use an XPATH expression to filter the data to the set we want to see. In my case I want to only see the employees that are in the Executive department. My data is structured thus:
<DATA_DS>
<G_1>
<DEPARTMENT_NAME>Accounting</DEPARTMENT_NAME>
<G_2>
<MANAGER>Higgins</MANAGER>
<EMPLOYEE_ID>206</EMPLOYEE_ID>
<HIRE_DATE>2002-06-07T00:00:00.000-04:00</HIRE_DATE>
<SALARY>8300</SALARY>
<JOB_TITLE>Public Accountant</JOB_TITLE>
<PARAS>11000</PARAS>
<EMP_NAME>William Gietz</EMP_NAME>
</G_2>
So the XPATH expression Im going to use to limit the data to the Executive department would be .//G_2[../DEPARTMENT_NAME='Executive'] Note the ../ moves the parser up the XML tree to be able to test the DEPARTMENT_NAME value. I added this XPATH expression to the three instances that need it ColCount, ColLabels and RowData. Its simple enough to do. Testing your XPATH expression is easier to do using a table of data. Please note, as soon as you make changes to the chart code. Going back to the Builder tab, you'll find that everything is grayed out. I recommend you make all the changes you can via the chart dialog before updating the code.
Online Template
Implementing the filter is much simpler, there is a dialog box to help you out. Add you chart and fill out the various data points you want to show. then hit the Filter item in the ribbon above the chart. That will pop the filter dialog box where you can then add a filter to the chart.
You can add multiple filters if needed and of course you can use the Manage Filters button to re-open and edit the filters.
Pretty straightforward stuff!
