Applies to: Nevron Chart for SharePoint (WSS3.0, SharePoint 2007/2010/2013)
How to create interactive drill-down chart in SharePoint?
To have interactive actions in SharePoint charts is a common requirement for many users. Nevron Chart web part allows the development of interactive dashboards and drill down reports by using parameters. Actions can be associated with all essential chart and gauge elements, including: chart data points, chart walls, chart axes, gauge pointers, gauge ranges, gauge axes, titles etc. Parameters can be consumed from any expression, they can be passed as arguments to the JumpToReport action. Parameters can also be directly integrated into any SharePoint page.
In this article we will review a scenario for drilling-down with 4 levels, and you can use it as a getting started point. However, by using parameters with Nevron SharePoint web parts, you can create different, as well as very complex interactive dashboards and reports directly inside SharePoint.
The following example demonstrates how you can create a drill-down report in SharePoint by using parameters with Nevron Chart web part. Let’s say that we have the following sample data:
You can download the full sample data (.xls and .xlsx format) that was used from the following links:
SampleData.xlsx
SampleData.zip
1. Here are the steps to start with:
1.1. Add Nevron Chart Web Part to a page. Run the designer and connect to the sample data from the Data Source tab.
1.2. From the Chart tab, select a Bar Chart Type.
2. Configure Pivot Drill Down Action
2.1. From Pivot >> Data Groupings >> Values >> Action
We will be performing drill-down on the same page. For the Report field, use the following expression: =Params![PageUrl].STR
2.2. Add the following parameters named:
- Region with value: =FIRST(Fields!Region)
- Company with value: =FIRST(Fields!Company)
- City with value: =FIRST(Fields!City)
- DrillLevel with value: =IF(Params!DrillLevel.STR = "", "1", TONUM(Params!DrillLevel.STR) + 1)
The Action Type will be conditional using the following expression:
=IF(TONUM(Params!DrillLevel.STR) >= 3, "None", "JumpToReport")
2.3. The Tooltip will use the following:
=IF(Params!DrillLevel.STR="", "Showing sales for " + FIRST(Fields!Region) + "
Sales: " + FORMAT(SUM(Fields!Sales.Value), "C", "en-us") + "
Profit: " + FORMAT(SUM(Fields!Profit.Value), "C", "en-us") + "
Click to see sales per city for the " + FIRST(Fields!Region) + " region.", IF(Params!DrillLevel.STR="1", "Showing sales for " + FIRST(Fields!City) + " city
Region: " + FIRST(Fields!Region) + "
Sales: " + FORMAT(SUM(Fields!Sales.Value), "C", "en-us") + "
Profit: " + FORMAT(SUM(Fields!Profit.Value), "C", "en-us") + "
Click to see sales per company for the " + FIRST(Fields!City) + " city.", IF(Params!DrillLevel.STR="2", "Showing sales for " + FIRST(Fields!Company) + " company" + "
City: " + FIRST(Fields!City) + "
Region: " + FIRST(Fields!Region) + "
Sales: " + FORMAT(SUM(Fields!Sales.Value), "C", "en-us") + "
Profit: " + FORMAT(SUM(Fields!Profit.Value), "C", "en-us") + "
Click to see sales per branch for the " + FIRST(Fields!Company) + " company.", "Showing sales for " + FIRST(Fields!Branch) + "
Company: " + FIRST(Fields!Company) + "
City: " + FIRST(Fields!City) + "
Region: " + FIRST(Fields!Region) + "
Sales: " + FORMAT(SUM(Fields!Sales.Value), "C", "en-us") + "
Profit: " + FORMAT(SUM(Fields!Profit.Value), "C", "en-us"))))
3. Configure Pivot Values and Category Groupings
3.1. From Pivot >> Data Groupings >> Values >> General, add a value and use the following expressions for the Label and Data Value fields:
Data >> Value: =SUM(Fields!Sales)
Label: =IF(Params!DrillLevel.STR="", "Region Sales", IF(Params!DrillLevel.STR="1", "City Sales for " + FIRST(Fields!Region), IF(Params!DrillLevel.STR="2", "Company Sales for city " + FIRST(Fields!City) + ", region " + FIRST(Fields!Region), "Branch Sales for the " + FIRST(Fields!Company) + " company, city " + FIRST(Fields!City) + ", region " + FIRST(Fields!Region))))
3.2. From Pivot >> Data Groupings >> Categories, add a category and use the following expression for the Label and Group By fields:
=IF(Params!DrillLevel.STR="", Fields!Region, IF(Params!DrillLevel.STR="1", Fields!City, IF(Params!DrillLevel.STR="2", Fields!Company, Fields!Branch)))
4. Configure Pivot Filtering
4.1. From the Pivot >> Filters, add 3 filters which use the following expressions:
Filter 1: =IF(Params!DrillLevel.STR <> "", Fields!Region = Params!Region.STR, true)
Filter 2: =IF(Params!DrillLevel.STR = "2", Fields!City= Params!City.STR, true)
Filter 3: =IF(Params!DrillLevel.STR = "3", AND(Fields!City = Params!City.STR, Fields!Company = Params!Company.STR), true)
5. Configure Chart X axis Title
5.1. For the Chart >> Chart Areas >> Axes >> Primary X >> Title, we have used the following expression:
=IF(Params!DrillLevel.STR="", "Regions", IF(Params!DrillLevel.STR="1", "Cities for the " + FIRST(Fields!Region) + " region", IF(Params!DrillLevel.STR="2", "Companies for the " + FIRST(Fields!City) + " city, region: " + FIRST(Fields!Region), "Branches for the " + FIRST(Fields!Company) + " company. City: " + FIRST(Fields!City) + " Region: " + FIRST(Fields!Region))))
6. Configure Chart Title and Drill Down Action for it
6.1. From the Chart >> Titles tab, we have created a new title. For the Title text, we are using the following expression:
=IF(Params!DrillLevel.STR="", "<b>Showing total sales per region.</b><br/><u>Click on a bar to drill-down and show sales per city for the region</u>",
IF(Params!DrillLevel.STR="1", "<b>Showing sales per city for the " + FIRST(Fields!Region) + " region.</b><br/><font color = 'blue'><u>Click me to go back to regions.</u></font>", IF(Params!DrillLevel.STR="2", "<b>Showing sales per company for the " + FIRST(Fields!City) + " city.</b><br/><font color = 'blue'><u>Click me to go back to cities.</u></font>", "<b>Showing sales per branch for the " + FIRST(Fields!Company) + " company.</b><br/>City: " + FIRST(Fields!City) + " // Region: " + FIRST(Fields!Region) + "<br/><font color = 'blue'><u>Click me to go back to companies.</u></font>")))
6.2. We are also using Action for the Title – under the Action tab, use the following expression for the Tooltip:
=IF(Params!DrillLevel.STR="", "Showing total sales per region.
Click on a bar to drill-down and show sales per city for the region", IF(Params!DrillLevel.STR="1", "Showing sales per city for the " + FIRST(Fields!Region) + " region.
Click me to go back to regions.", IF(Params!DrillLevel.STR="2", "Showing sales per company for the " + FIRST(Fields!City) + " city.
Click me to go back to cities.", "Showing " + FIRST(Fields!Company) + " company sales per branch.
Click me to go back to companies.")))
For the Report use: =Params![PageUrl].STR
Add a parameter:
- Name: DrillLevel
- Value: =IF(Params!DrillLevel.STR = "1", "", TONUM(Params!DrillLevel) - 1)
The Action Type will be conditional using the following expression: =IF(Params!DrillLevel.STR = "", "None", "JumpToReport")
This action will actually act as a "Drill-Up" taking us one level up when you click on the title.
Click OK to save your configurations and go back to the SharePoint page. Now when you click on one of the bars for the different regions, your will drill down and the chart will display the Sales per City for that specific region (you can continue drilling-down). Then, when you click on the Title you will do back one level up to the Sales per Region report.
The following online example demonstrates multilevel drill-down: Interactive Drill-Down
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.comArticle ID: 203, Created On: 11/4/2011, Modified: 1/29/2013