Applies to: Nevron Chart for SharePoint (WSS3.0, SharePoint 2007/2010/2013)
How to filter the Chart Web Part based on begin and end date?
It is a common requirement to display a chart for specified date time range (begin and end date). You can achieve this by filtering the data and use parameters. Let's say that we have the sample data below. It represents historical data for a specified time period. The "Date" column is of type Date and Time and we have some historical data for Company X and Company Y.
In this article we will review two options for filtering the date range – 1. using the SharePoint Date Filter web part and 2. using a custom HTML + Java Script in Form Web Part or Content Editor Web Part (for SharePoint 2007), or the HTML Form Web Part (for SharePoint 2010).
Filter the Chart by using the SharePoint Date Filter web part
1. Add Nevron Chart Web Part to a page. Run the designer and connect to the sample data from the Data Source tab.
2. Add two Date Filter web parts and provide Filter Names: "Start Date" and "End Date". You should also provide a default value.
3. Using the web part menu, connect both of the Date Filter web parts to Nevron Chart by sending filter values to Nevron Chart web part:
You can then check the connections from the Chart web part menu and see that it is getting SP Field Parameters from the "Filter: Start Date" and "Filter: End Date".
4. Run the Chart Designer, from the Chart tab select a Combo Chart Type, Sub Type: XY Scatter.
5. From the Pivot tab, go to Filters and add a filter which uses the following expression:
=AND(Fields!Date>=DATETIME(Params!SPF_Start_Date.STR, "en-us"),Fields!Date<=DATETIME(Params!SPF_End_Date.STR, "en-us"))
If you have provided default values for the Date Filter web part, you will be able to see the parameters SPF_Start_Date.STR and SPF_End_Date.STR from the Expression Editor, under the Variables – Parameters tab.
6. We also have to configure the Pivot:
6.1. Add a new Category from the Data Groupings – Categories tab and use the following expression:
=Fields!Date.Value
6.2. We can also sort with ascending direction:
6.3. We can now add the Values from the Data Groupings – Values tab. In this example we have two values for Company X and Company Y. Respectively, for the Value field they use =SUM(Fields!Value1.Value) and =SUM(Fields!Value2.Value), and =Fields!Date.Value for the X: value (because we are using the combo chart XY Scatter sub type). We will be using Line for the Series Type for both companies.
7. We will also need to configure the X-axis to use sub type Date Time. This can be done from Chart - Chart Areas – Axes – Primary X – Scale – Numeric – Sub Type:
Additionally, you can format the X-axis labels – take a look at this topic: Configure the Chart Web Part date time axis label formatting
8. We will now add a Chart Title to provide information for the specified date range. The Title will be formatted with the following expression:
="Showing data between " + FORMAT(DATETIME(Params!SPF_Start_Date.STR), "MMMM d, yyyy", "en-us") + " and " + FORMAT(DATETIME(Params!SPF_End_Date.STR), "MMMM d, yyyy", "en-us")
Now you have an interactive dashboard, which allows you to choose the Start and End Date for the date range you need to plot from the Date Filter web parts Calendar.
Filter the Chart by using custom HTML + Java Script in Form Web Part or Content Editor Web Part (for SharePoint 2007), or the HTML Form Web Part (for SharePoint 2010)
1. We will add an HTML Form Web Part and use the following HTML + Java Script code:
<table>
<tbody>
<tr>
<td>
Start Date:
</td>
<td>
<div id="NDate1Id">
</div>
</td>
<td>
End Date:
</td>
<td>
<div id="NDate2Id">
</div>
</td>
<td>
<input type="button" value="Update" onclick="NRefreshAllWebParts()" />
</td>
</tr>
</tbody>
</table>
<script type="text/javascript">
function NFormatDateENUS(date) {
var year = date.getFullYear();
var month = date.getMonth() + 1;
var day = date.getDate();
return month + "/" + day + "/" + year;
}
var today = new Date();
var yesterday = new Date();
yesterday.setTime(today.getTime() - (1000 * 60 * 60 * 24));
NInitSTRParam("NDate1Id", "StartDate", {
InitialValue: NFormatDateENUS(yesterday),
RefreshMode: "none"
});
NInitSTRParam("NDate2Id", "EndDate", {
InitialValue: NFormatDateENUS(today),
RefreshMode: "none"
});
</script>
2. From the previous example, we will only have to modify the Chart Pivot Filter expression to:
=AND(Fields!Date>=DATETIME(Params!StartDate),Fields!Date<=DATETIME(Params!EndDate))
and the Chart Title expression to:
="Showing data between " + FORMAT(DATETIME(Params!StartDate), "MMMM d, yyyy", "en-us") + " and " + FORMAT(DATETIME(Params!EndDate), "MMMM d, yyyy", "en-us")
If you have any questions regarding this example, contact us at: support@nevron.com
For more details, take a look at the following Help Documentation Topics:
Building Dashboards >> Getting Started
Building Dashboards >> Parameters in SharePoint pages
Building Dashboards >> Actions and Drill Down Reports
If you want to build a custom dashboard in SharePoint and do not want to bother doing that yourself, you can contact Nevron Consulting for a quote at: consulting@nevron.com
Article ID: 206, Created On: 12/30/2011, Modified: 1/29/2013