A useful interactive feature of dashboards is to be able to click on a data point to see more detailed figures that make up its value. For example, on a pie chart with a slice for each country you might be able to click to be shown a chart with values for different regions within that country. This article briefly describes the steps to accomplish this within SharePoint, including Windows SharePoint Services 3.0. For a more detailed walkthrough of using a Query (URL) Filter Web Part (MOSS 2007 only) for drilling down and for additional tips, see "Implementing Drilldown" in the chart web part's help.

Set up your top level

On a page, set up your Dundas web part to display your top level, which the user can click on to drill down. In this example, this is a pie chart that displays a pie slice for each country.

For Dundas Chart, go to the Customize Your Chart wizard. On the last tab in step 3: "Interactive Features" set the Series Hyperlink to direct users to whatever page you intend to display your more detailed chart in (the lower level). This can be the same page, or another page.

We will also pass a parameter in with the URL using keywords, e.g., DrilldownPage.aspx?Country=#AXISLABEL. In this example, the keyword #AXISLABEL is used so that the axis label is passed to the other page. If you use numeric or Date/Time X values, you can use the #VALX keyword.

(Click here for a full description of using keywords.)

Fig. 1 Setting the Series Hyperlink.

In Dundas Gauge, you can go to Customize Your Gauge wizard. In step 3, set the Hyperlink on any element to link to whatever page you like.

Set up your lower level

On the page you linked to in your Hyperlink setting above, we must do something with the parameter so that we display the appropriate data. For example, after clicking on a country using the hyperlink set up in the previous step, the page that you linked to should display data for regions within that country.

In your Dundas web part, go through the Data Connection wizard to set up your data. The next steps depend on whether you get data from another web part, or if you choose a different data source:

• If you connect to another web part such as a SharePoint List View Web Part on your page, you will need to filter the values in the list view web part itself.

  To be able to automatically get the parameter value in the URL and pass it to the list web part, this requires a filter web part such as Query (URL) Filter Web Part (only available in MOSS 2007 with business intelligence features).

  Click here for a SharePoint help article for List View Web Parts, which includes details on setting up the web part to get filter values from another web part.

• If you connect to any other type of data source, our web part should be used to filter your data.

  If you connect to an external database, spreadsheet, site definition list or Excel Services, you will be presented with the option to add parameters in step 3 of the wizard. Set up your parameter here to make your query change depending on the parameter value passed from the other page. Use the same parameter name that you used in the Hyperlink earlier. (If you use the business data catalog, parameters are set up in the application definition file uploaded by your server administrator.)

  Here is an example where our web part is connected to an external Excel spreadsheet file:

  

  Fig. 2 Setting up parameters to filter the data based on drill-down.

  Now the easiest way to set this parameter is to use this ready-made Visual Basic code. Access our web part's code editor from the down-arrow menu in the top-right corner, and in the PostInitialize event, paste in:

  ' This VB code automatically sets the values of parameters in the data 
  ' connection wizard that match the names of parameters in the URL. It 
  ' should be used in the PostInitialize event.
  
  Dim webPart As ChartWebPart = chartObj.Parent
  ' For Dundas Gauge, replace "chart" with "gauge" in the line above
  ' E.g., Dim webPart As GaugeWebPart = gaugeObj.Parent
  
  For Each parameter As Dundas.SharePoint.Data.DataParameter In webPart.DataBindings(0).DataSource.Parameters
      If Not chartObj.Page.Request(parameter.Name) Is Nothing Then
          parameter.Value = Convert.ChangeType(chartObj.Page.Request(parameter.Name), parameter.Type)
      End If
  Next

  Another option for setting the parameter values is to use another web part that supports sending parameter or filter values to other web parts. For example, the Query (URL) Filter Web Part is capable of pulling a parameter value from the URL and sending it to other web parts. You can send the value to the Dundas web part to set the parameter you set up in the Data Connection Wizard. The article "Implementing Drilldown" in the web part's help provides a thorough walkthrough. For more details on connecting to this web part see this SharePoint how-to article.